Post reply

Very Confused about LDF File Size - Please Help

  • December 14, 2011 at 9:27 am

    #253473

    Hi all;

    I currently have a .ldf (transaction log file) that is larger than my .mdf file. We are a school district and run a student system on a Microsoft SQL Server 2005 SP3. We recently migrated to a different domain (long story for the reasoning). We paid the software company to change permissions on the databases etc... Unfortunately, it looks like they didn't get the permissions squared away on everything. When this happened our Maintenance Plans would not run (wrong owner). We found out the hard way they were not running and our volume for our LDF files filled up and the system went down. They were getting Transaction log full errors on the webpage. I freed up some space and shrunk some non-critical database ldf files. We have plenty of space on that volume now and all jobs are running again. My issue is, the ldf files are not fluctuating in size at all. The most critical database the ldf file is 16.9GB and is not changing. The date modified is 12/12/2011 at 3:30AM which is when the server was rebooted. The recovery mode is Full and we are doing transaction log backups (hourly from 2AM-11PM). I thought when you did transaction log backups (trn), that it truncated the ldf file and it would clear it out and make the ldf file much smaller. What should our ldf file size be if we have transaction log backups running hourly and the database for the is 14GB?

    Do I need to do something else to SAFELY prevent our ldf file from growing this much?

    Also, is there any harm in having a larger ldf file - is this going to cause performance issues having it at 16.9GB?

    Any help for this is much appreciated.

    Thanks,

    Tony

  • December 14, 2011 at 9:31 am

    #1421420

    You need to read those 2 articles.

    http://www.sqlservercentral.com/articles/Administration/64582/

    http://www.sqlservercentral.com/articles/Transaction+Log/72488/

  • December 14, 2011 at 9:51 am

    #1421432

    Thank you very much Ninja. That is very helpful. I could not find any forums or articles like that myself. Looks like I need to sharpen up my Google skills 😀

    Thanks!!!

  • December 14, 2011 at 9:54 am

    #1421434

    Helps to know who to trust and there use there names in the SE. 😛

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply