Solved

Alert - Monitoring Transaction Log Space

Posted on 2006-11-20
7
724 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
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 142

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
2016 SQL Licensing 7 41
What's wrong with this T-SQL Foreign Key? 7 46
Show Results for Latest DateTime in a View 27 25
Present Absent from working date rage 11 21
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

831 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