• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 734
  • Last Modified:

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
0
novknow
Asked:
novknow
4 Solutions
 
HillwaaaCommented:
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]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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
HillwaaaCommented:
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
 
roshkmCommented:
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now