notify the DBAs when the size of the transaction log reaches certain limit

Question

Post reply

notify the DBAs when the size of the transaction log reaches certain limit

NewToSQLserver

Right there with Babe

Points: 763
More actions
June 2, 2010 at 11:07 am

#141265

Hi All,
Lately, the T-log continues to grow tremendously..sometimes goes to 20Gig. I already setup the severity 17 (insufficient resources). The only way that I will get this error notification is actually run out of disk space. I don't want to continue to manually monitor the T-log file. I setup the maintenance plan to backup the T-log every 5 hours.
My question is that is there a way that I can setup an automatically job to check if the size of the t-log reaches to certain limit, email to the DBA team.
Thank you in advance for your help in this.
NewtoSQLserver.
p.s. the person on this project does not know what cause to increase the t-log and I don't know how to check it to see what the cause of a big jump of increasing in the T-log file.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Is every 5 hours often enough for log backups? If the tran log is growing a lot, it may not be.

Please read through this - Managing Transaction Logs[/url]

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

NewToSQLserver Right there with Babe Points: 763 More actions · Answer 2

Normally, it grows only 500mb to 1gig every 5 hours. A few times it grows to 8gig within 5 hours. I just want to setup an alert to notify the team when it reaches to 5gig to look into it and not wait until receive a backup email notification that there is no disk space for backing up the T-log.

magasvs SSCertifiable Points: 7660 More actions · Answer 3

You can setup alert similar to the one you have:

- change alert Type to "SQL Server performance condition alert"

- select Object "SQLServer:Databases"

- select Counter "Log File(s) Size (KB)"

- specify database name under Instance

- set value in KB (rises above NNN KB)

NewToSQLserver Right there with Babe Points: 763 More actions · Answer 4

NewToSQLserver

Right there with Babe

Points: 763

June 3, 2010 at 8:01 am

#1175963

Thanks. I will setup and test out.

Marco Default port Points: 1408 More actions · Answer 5

You can also let this alert make an automatic T-Log backup when it reaches the treshold, so the log doesn't have to grow at all.

Regards,

Marco

NewToSQLserver Right there with Babe Points: 763 More actions · Answer 6

Thanks magasvs. I tested out and it worked great. I implemented in production evnironment and it did sent me an email notification a few hours later. :=)

NewToSQLserver Right there with Babe Points: 763 More actions · Answer 7

Hi Marco,

I thought about it last night to see if there is a way that I can setup the automatic t-log backup when it reaches to certain threshhold. Now, you said it. I'm going to search the command.

Thanks,

NewToSQLServer 🙂

steveb. SSC-Forever Points: 46733 More actions · Answer 8

Rather than set the backups to run when you are running out of space, you really should set them up to run at regular intervals and manage the transaction log pro-actively, read through the link Gail posted to understand how to do this.

Setting up alerts to let you know when the disk is full be for the last resort...

Marco Default port Points: 1408 More actions · Answer 9

it is indeed a good idea to backup the logs more frequently, but when the logs grows over 60% between two backups it is a good idea to make an extra backup through the alert. When you also let the alert notify you, you can manually increase the log file size, when the alert fires frequently. In this way yoou are in control over log file growths en it isn't scary anymore, to let autogrow enabled.

You can start jobs from the Response tab in the alert.

Regards

Marco

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

mchofman (6/4/2010)
it is indeed a good idea to backup the logs more frequently, but when the logs grows over 60% between two backups it is a good idea to make an extra backup through the alert.

When the log grows 60% between backups, it's a good idea to find out why and then make an educated response, not just knee-jerk 'make a backup and don't worry about the root cause' approach

One comment, ad-hoc log backups will make a restore fun, if you don't know how many backups there should be. I hope the naming of the files is such that you can tell what order they would need to be restored in, should there be a need for a restore.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

steveb. SSC-Forever Points: 46733 More actions · Answer 11

mchofman (6/4/2010)
it is indeed a good idea to backup the logs more frequently, but when the logs grows over 60% between two backups it is a good idea to make an extra backup through the alert. When you also let the alert notify you, you can manually increase the log file size, when the alert fires frequently. In this way yoou are in control over log file growths en it isn't scary anymore, to let autogrow enabled.
You can start jobs from the Response tab in the alert.
Regards
Marco

I would look at why your logs are growing 60% between backups - this is not normal

Marco Default port Points: 1408 More actions · Answer 12

Sorry, I didn't mean grow 60% but fill up to 60% (60% of the log is in use).

Regards,

Marco