SQL Maintenance
SQL Server 2005 - Shrink Log Files
there are sitautions that may cause your SQL 2005 database logs to grow far greater in file size than normal log files. This could be due to many factors including failing backups jobs and/or mis-configured reindex processes. This most commonly associated with the Works database as it most often the database with the largest amount of data and requests.
In order reduce large log files you can use the built in SQL 2005 Shirnk feature in SQL Server Management Studio. To do so, firwst log into SQL Management Studio and right-click on the database in which you would like to reduce the log file. From there, select Tasks->Shrink->File.
In the File type select box, select Log.
Next make note of the two fields "Currently allocated space" & "Available Free Space".
The next step requires a little trial error:
- Attempt to shrink the log files using the "Released unused space option". Hit Ok, and wait till complete. Often this technique for shrinking the logs will either work exactly as designed or will have no effect all. Once teh process completes reselect Tasks->Shrink and compare the values of the "Currently allocated space" & "Available Free Space" to their previous values.
- If the log files report that they were indeed shrunk then the process is complete. However, if the option had no effect then we need to try the second option, "Reorganize pages before releasing unused space". This option will require you to provide the target log file size. A recommended size for the Works DB would be 10% of the overall data size (mdf file). Once you have calculated your value hit Ok and let the shrink process attempt top reduce the file size. Thye resutlk of this process will also need to be verified as it will often only take the actual file size to about 50% of the desired files size. It is ok to run this process more than once to achieve the target goal.
