alert to monitor log file size

Question

alert to monitor log file size

timscronin

SSCertifiable

Points: 6851
More actions
April 4, 2008 at 1:34 pm

#74871

I have a sql server which has some Biztalk databases which can grow like wildfire on a daily basis, where log files can grow to 20 gig in an hour. Is there a way to set an alert based upon a log file size growing to big that I can fire an sp on to shrink when needed. Or any other better approach?

Viewing 5 posts - 1 through 4 (of 4 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Setup a Job that runs every X minutes; have it check the Log File size and if over, then take corrective action.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

tosscrosby SSCoach Points: 18045 More actions · Answer 2

You can setup a job but have it run only when certain thresholds (alerts) are hit. Create a new job, have the step(s) be whatever action you are going to take. Next, select "New Alert" on the schedule tab and fill in whatever is appropriate. Enclosed is a print screen showing what I have setup. When the alert occurs in SQL, as long as the job is enabled, the job will execute. You can set it for a certain fixed size, percent full (that's what my screen shot is) or a bunch of other things.

This is on SQL2K, don't remember SQL7.

-- You can't be late until you show up.

Todd Engen SSCrazy Eights Points: 9869 More actions · Answer 3

Since using that much space sounds normal for your situation leaving it at the largest size might be better option. Or, increase the frequency of the transaction log backups.

Have a look at the section named What are the performance implications?: Considerations for the "autogrow" and "autoshrink" settings in SQL Server

Or, depending on your recovery strategy, you could change the database recovery model to simple. That would help keep the transaction log from growing. Just be aware that you can only recover to the last full backup.

timscronin SSCertifiable Points: 6851 More actions · Answer 4

Thanks, Duh missed this approach. Biztalk is it's own beast and come sometimes go crazy and with it the log file