SQL Server Performance

Transaction Log File Will not Shrink

Discussion in 'T-SQL Performance Tuning for Developers' started by wolffy, Oct 3, 2003.

  1. William Flather New Member

    I know this is a rather old thread, but we have had some chronic problems with this as well, so this time I documented how we fixed this problem. We are in no way power SQL users but host a variety of SQL databases on or SQL2k install. We are in the process of moving several DNN websites to new hosts. SQL server space needed is an issue in finding new external hosting, so evaluating size is important. We found that one DNN DB install that was 11.62GB. The Data file was 189 MB, the Transaction log 11.4GB (!). This was a problem and did not really reflect needed SQL resources.
    When we created this DB, we failed to set a limit on the transaction log, so over the years it simply grew. And grew... Our mistake.
    Shrink Database found in Tasks did nothing, and I was uncomfortable attempting to run any of the scripts or command line recommendations found here, so here is what we did. Any admins more knowledgeable are welcome to tell me why the following is a bad idea.

    How we reduced our transaction log size without shutting down the DB or running scripts:
    From Enterprise Manager, right click on the DB name and select Properties
    Click on Options tab and change Recovery Model from Full to Simple. (Note that this will eliminate the ability to restore from a fail point, but you can still do a full restore from backup, if you have one, so make sure you have a recent SQL backup)
    Save your change
    Right click on the DB name again and select All Tasks, Shrink Database
    Use Windows Explorer or My Computer to confirm that the physical transaction log has shrunk, probably to 1,024kb
    Now set a reasonable limit on transaction log file size:
    Right click on the DB name again and select Properties
    Click on the Transaction Log tab, change Maximum File size to Restrict Growth and set a resonable size in MB (your choice).
    Click on Options and re-set Recovery Model back to Full. Ability to restore from a fail point is now restored and will re-grow over time, limited by the transaction log size you set.
    Click on OK to save your changes.

    Done. Our databases are still working fine and the DNN sites are still fully functional.
  2. satya Moderator

    Good list of steps to reduce the issue, however I would always suggest to look into the factors that are contributing over size of transaction log in this case. Remember database size always include data and log file, when you take the backup it will only take data not entire database size what is shown in Enterprise manager.

Share This Page