Truncating the SQL Log File
If the SQL log file is not backed up at least weekly, it can grow unchecked to the point of running the server out of disk space. A bloated log file also creates performance issues and slows processing of SQL transactions.
This only applies to databases that are using the Full recovery model. Simple recovery automatically purges log transactions. You can check which recovery option is being used in the database properties->Options screen.
How to check for a large log file
- Using SQL Management Studio (SMS), connect to the database server.
- Right click on the lobbyCentral database and choose Properties.
- Select Files
- The fourth column called Initial Size actually display the current size of the files.
- The log file should be around 25% of the size of the database. If it is over 100MB in size, this generally is too large and should be truncated.
Truncating the SQL Log File
- Change the Initial Size to a smaller size in megabytes. For example, if the current size is 100MB, change it to 10MB. As long as Autogrowth is on (next column), the size does not need to be exact.
- Click OK
- Right click lobbyCentral and choose Backup
- Perform a Full backup
- Right-click lobbyCentral and choose Backup again.
- Change the Backup Type to Transaction Log
- Remove the current file name, which should be the file that was created with the first backup.
- Add a new filename indicating it is a log backup to identify the two backup files
- Click OK
You must perform the data backup first, then follow with a backup of the log or the truncate will not work.
Preventing Explosive Log Growth
Create a SQL Agent job to backup the database and log in separate steps using SQL Agent. We recommend that you schedule the job to run daily.
If you are using SQL Express, SQL Agent is not available and this must be performed manually. When performing manually, you can follow the steps above but skip steps 1 and 2. Changing the size of the database is only required to reduce the size of the file.