Solved

Alert - Monitoring Transaction Log Space

Posted on 2006-11-20
7
731 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 16

Accepted Solution

by:
Hillwaaa earned 350 total points
ID: 17984852
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
ID: 17985249
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 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 17985279
>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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 16

Assisted Solution

by:Hillwaaa
Hillwaaa earned 350 total points
ID: 17985286
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
ID: 17985428
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
ID: 17985497
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
ID: 17986011
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

617 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