Post reply

LOG Files

  • December 22, 2015 at 9:27 am

    #303398

    My SQL DB Log Files keep on growing and never stops growing... what would you guys suggest before I run out of space? ShrinkFile?

    Thanks,

    Shany Grimard

  • December 22, 2015 at 9:47 am

    #1847493

    You need to figure out why the log is growing first. If the log is actively growing then you likely won't be able to shrink it anyway.

    Check the log_reuse_wait_desc in sys.databases for the database in question. Two very common causes of log growth are not backing up the log of databases in full recovery, and long-running open transactions.

    If the database is in full recovery and not getting log backups, you'll need either to set up regular log backups, or to decide if that database needs to be in full recovery (that will depend on your business requirements around acceptable levels of data loss and ability to restore to points in time).

    If you have a long-running open transaction, you'll need to see what the transaction in question is. From there you'll have to decide whether you just need to let the transaction finish and accommodate the growth as best you can, or whether it's reasonable to kill the offending transaction.

    There are other potential causes of log growth, of course, but these are the two I see the most.

    Cheers!

  • December 22, 2015 at 11:56 am

    #1847540

    I suggest you read this: http://qa.sqlservercentral.com/articles/Administration/64582/

    Then, once you've finished, read this: http://qa.sqlservercentral.com/articles/books/94938/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 22, 2015 at 11:57 am

    #1847541

    Are your databases in full recovery mode? If so, are you performing log backups? If you are not performing log backups, you need to. If you don't need to keep transaction history, then set the databases to simple recovery mode. Having a database in full recovery mode without performing backups will keep the transactions in the logfile, eventually filling up your drive.

  • December 22, 2015 at 9:30 pm

    #1847634

    Check out the following link to stop log file growth: http://blog.sqlauthority.com/2010/09/20/sql-server-how-to-stop-growing-log-file-too-big/

    and to shrink log file you can take the help of this link: http://www.sqlserverlogexplorer.com/shrink-transaction-file/

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

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