SQL server transaction log monitoring
I want to monitor transaction log how close to the limit. For example, I set transaction log limit to 12 GB. If a transacting is 80% close to 12 GB, I want to send out a warning. If 90% I want to send out a critical warning. Do you know if you have this kind scripts I can borrow? That I will use to implement to all databases. But, not all database limit is the same. I want the script to be able to pick up the limit then do the calculation. So, the purpose of the script is to prevent transaction log full issue
ASKER
The database is full recovery mode, We backup log every 30 minutes. But, sometimes in 30 mins period, we have a big transaction which cause the transaction log size too big and hit the limit, and cause the transaction log full error. We want to avoid this issue. So that we want to monitor the transaction log, never hit the limit.
ASKER
Hi,
How to code this by using DBCC SQLPERF(LOGSPACE)
If the log size up to 75%, send out warning,
If the log size up to 90%, send out critical notice
How to code this by using DBCC SQLPERF(LOGSPACE)
If the log size up to 75%, send out warning,
If the log size up to 90%, send out critical notice
It sounds more like your transaction log is just too small for your work load.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Open in new window
You'd probably want to execute this command in a scheduled maintenance plan at predefined intervals. However, I'm wondering why you'd let a transaction log grow to such a size. If you're using the full recovery model you should be performing transaction log backups at regular intervals which will automatically shrink or truncate the log once the backup is complete. If you're not performing transaction log backups then you should switch your recover mode to simple.