Alert - Monitoring Transaction Log Space

Hi experts,

In SQL Server 2005, what counter should I use to monitor the above?
It should trigger when the transaction log space is 50% full.

regards
novknowAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
HillwaaaConnect With a Mentor Commented:
Hi novknow,

I'd suggest:

Type: SQL Server performance condition alert
Object: MSSQL$<instanceName>:Databases
Counter: Percent Log Used
Alert if counter: rises above
Value: 49

Cheers,
Hillwaaa
0
 
novknowAuthor Commented:
Hi Hillwaaa

Thanks for the response.
I would like to ask further question (I have increased the number of points):
Will alert works if the transaction log file is allowed to auto-grow?
What is the next best action to take when the the transaction log is full (assuming no one is able to attend to the server in time when it was triggered?

regards
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>Will alert works if the transaction log file is allowed to auto-grow?
yes. the auto-grow will only run if the file is "full".

>What is the next best action to take when the the transaction log is full (assuming no one is able to attend to the server in time when it was triggered?
the best action is to run a transaction log backup regulary, depending on the load on the database it might be very frequent (every 15 minutes or even faster) or less frequent (every hour).
the longer the interval, the higher the possibility of data loss in case of crash. the shorter the interval, the higher the additional load to perform the log backup.


0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
HillwaaaConnect With a Mentor Commented:
The Percent Log Used is a percentage, so it will continue to work when the log file grows.

Next best action would either be to shrink it probably with backup first as described here:

How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
http://support.microsoft.com/kb/907511

Also see shrinking the transaction log
http://msdn2.microsoft.com/en-us/library/ms178037.aspx

Cheers,
Hillwaaa
0
 
novknowAuthor Commented:
Hi angelIII,

I have not encounter transaction log file is full problem/situation before.
May I know how to recover the server when it happens?

regards
0
 
HillwaaaCommented:
novknow - I think backing up and shrinking the transaction log is still appropriate when it becomes full (see the above links).

angelIII - agree/disagree or more to add?
0
 
roshkmConnect With a Mentor Commented:
Usually Transaction Log will be 'emptied' once a successfull back up is completed.

Other wise u will have to check Auto Shrink option in Transaction Properties. But this will slow down the entire transaction a little bit since it will try to shrink the Transaction log after every transaction.

Or u can chage the Transaction Model from 'Full' to 'Simple' if u dont take a back up of the transaction log. This is recommended only if u dont worry about transactional data and u can recover the database from DB file.

If u let the size limit as auto grow, u need not worry, unless ur drive dont have enough space.  Once i have seen my log file reach 16GB.

RKM.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.