SQL Transaction Log Backup & Purge

If the database model is set to FULL and not SIMPLE, this log file will continue to retain data until a transaction log backup is performed or the server runs out of space.

A large transaction log will affect database performance and can cause slowness or timeout issues.  Performing a log backup will truncate data that is no longer needed.

It is recommended to backup the transaction log daily.  This can be automated with SQL Agent for Standard and Enterprise editions of SQL Server.  If you running SQL Express, you can change the database model to SIMPLE to automate the truncation process.

How to perform a transaction log backup

1. Connect to the LobbyCentral database server using SQL Management Studio
2. Create a folder on the database server to store the backup.  i.e. c:\backup.
3. Right click the lobbycentral database and choose Tasks, Backup.
4. Take a full backup the database.
5. Go back to Tasks, Backup
6. Change the Backup Type to Transaction Log
7. Set the Backup Destination to Disk and click Remove to remove the first backup's filename.   For identification purposes, you should append the word log to the new filename as shown below.
   
   

   

8. Click OK.
9. After the backup finishes, you will need to reduce the physical size of the log file.
10. Right-click the lobbycentral database and choose Properties
11. Click the File page
12. Locate the row for the Log and change the Initial Size to 100 or other preferred value.
13. Optionally you may set the max size of the file in the column to the right of the Initial Size column.
14. Click OK