Date: September 04, 2012
Transaction logs can become quite large and occupy disk space. When this happens, database operations may begin to fail. The size of the transaction log cannot simply be limited because the database operations may fail when the log becomes full. Below we describe the method for managing the transaction log using the simple recovery model. Please note that in Simple mode, you can only restore the most recent backup. If you would like to restore previous backups you will have to leave the setting at Full. Please see Microsoft documentation for maintaining the transaction log size using the Full recovery model.
Reduce Log File Size Only
Putting the database into Simple Mode and tell the database to auto shrink will mean that the SQL Server will maintain the database by itself (See Reduce Database and Log File Sizes below for more information)
- To manually shrink log file:
- Backup the Database (full)
- Backup the Transaction Log file (transaction log)
- Tell the database to shrink (Task -> Shrink -> Files and select 'file type' is log instead of data)
Reduce Database and Log File Sizes
Putting the database into Simple Mode and tell the database to auto shrink will mean that the SQL Server will maintain the database by itself.
First, set the Recovery Mode of the database to Simple. In Simple mode, when the transaction log reaches 80% of its maximum capacity, the log is automatically truncated. However, setting the recovery mode to Simple is not the full solution because, by default, SQL Server does not restrict the transaction log space. Therefore, in addition to setting the recovery mode to Simple, we have to restrict the size of the transaction log.
Change to Simple Recovery Mode
- In SQL Server Management Studio, right click the database and select Properties.
- Click the Options tab and change the recovery mode to Simple.
- Click OK.
Shrink the Database
It is recommended that you shrink the database before you set the max size of the transaction log.
- Right click the database, select All Tasks, then Shrink Database.
- The property page shows the amount of space allocated and the amount of space free. Please note the free space will not decrease until the transaction log truncation completes.
- Click OK to shrink the database.
Set Maximum Transaction Log Size
- In SQL Server Management Studio, right click the database and select Properties.
- Click the Files tab and click the button in the Autogrowth column for the Log File Type Do NOT restrict the growth, just set it to a small number and allow it to grow again. It should not grow to a large size again unless youare restoring a database.
