Solved

Alert - Monitoring Transaction Log Space

Posted on 2006-11-20
7
719 Views
Last Modified: 2008-03-10
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
Comment
Question by:novknow
7 Comments
 
LVL 16

Accepted Solution

by:
Hillwaaa earned 350 total points
Comment Utility
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
 

Author Comment

by:novknow
Comment Utility
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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
Comment Utility
>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 Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 16

Assisted Solution

by:Hillwaaa
Hillwaaa earned 350 total points
Comment Utility
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
 

Author Comment

by:novknow
Comment Utility
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
 
LVL 16

Expert Comment

by:Hillwaaa
Comment Utility
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
 
LVL 4

Assisted Solution

by:roshkm
roshkm earned 50 total points
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Backup 24 69
Data to display differently-SQL Server 4 19
sql calculate averages 18 21
SQL Date Retrival 7 21
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now