Transaction Log File- Monitoring

fredp1fredp1 Member Posts: 86
edited 2009-05-09 in SQL Performance
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

Comments

  • DenSterDenSter Member Posts: 8,304
    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.
  • kinekine Member Posts: 12,562
    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".
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • krikikriki Member, Moderator Posts: 9,094
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • fredp1fredp1 Member Posts: 86
    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
  • DenSterDenSter Member Posts: 8,304
    Thank you for following up Fred, and good to hear you have your transaction log under control now :mrgreen:
Sign In or Register to comment.