SQL Maintenance

From Galen Healthcare Solutions - Allscripts TouchWorks EHR Wiki
Revision as of 20:40, 13 October 2011 by Joe.Nyiri (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

SQL Server 2005 - Shrink Log Files

Certain situations 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 misconfigured re-index processes. This is 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 Shrink feature in SQL Server Management Studio. To do so, first 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.

Shrink step 1.JPG

In the File type select box, select Log.

Shrink step 2.JPG

Next make note of the two fields "Currently allocated space" & "Available Free Space".

Shrink space.JPG

The next step requires a little trial error:

  1. 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 the process completes reselect Tasks->Shrink and compare the values of the "Currently allocated space" & "Available Free Space" to their previous values.
    Shrink step 3.JPG
  2. 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. The result 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.
    Shrink step 4.JPG

External Links

Microsoft's Top 10 Best Practices for SQL Server Maintenance