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.
- 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