Transaction Log File- Monitoring

fredp1

Hi,

How do I identify the transaction log file been extended in SQL 2005?

I know this is not a god thing to happen and can be prevented by making it big enough and backing it up etc, but I'l like to know how fast it grows and heaven forbid, when it gets extended.
How do I defrag the TL? Can I do it online, or do I need to detach the db and then defrag the disk or recreate the TL?

Thanks

DenSter

Open SSMS, expand your server, expand the Management node, and see if you can find the "SQL Server Logs". In there you should see a number of logs that you can open. The auto expand events for all data files should be in there.

Don't take this as RTFM, but I want to point out "Books Online" to you, which is the best SQL Server resource available, and it should already be installed on your server. Go to Start, All Programs, SQL Server, Documentation and Tutorials, and there you should see Books Online. It has tons of articles in the local help files, from MSDN online, the communities, and much more. You will find hundreds of articles about the transaction log, how to maintain it, and everything you want to do with it.

kine

You can use the Performance Monitor (part of the Windows admin. tools). You can see that there are counters for SQLServer:Database with name "Log File(s) Size" and "Log File(s) Used Size". You can monitor these counters to have more info. More info is mentioned in the MOC 80040A - manual for NAV 2009 with name "INSTALLATION AND CONFIGURATION IN
MICROSOFT DYNAMICS® NAV 2009".

kriki

Check also this : http://mibuso.com/blogs/kriki/2008/12/23/some-tips-tricks-for-the-transaction-log/

fredp1

Hi all,

Thank you for the pointers to resources and suggestions.
The simplest tip was to look in the SQL Server Logs. Simple suggestions like checking the logs is always a good start as it told me as I had a constant problem of authentication failure triggered from a process on a remote server. (now fixed)
The SQL Server logs are showing a quite system now.

Also the fragmentation on the TL about 500 VLF... I need to work on that as i've started a file defrag.

I've also seen the database is about 2G, while the TL grew to 30G... The backup has been on the database only, not the TL file.
I'm going to setup a hourly TL backup to another disk to prevent that from happening again.

I hope this thread can help others.

Thanks for the tips.

Kind Regards
Fred

DenSter

Thank you for following up Fred, and good to hear you have your transaction log under control now