SQL Server How to Set up Maintenance plan to shrink log

Question

So I have a log file which grows very large after I run an SSIS job daily, sometimes up to 1220MB. In my database options I have set my recovery model to "FULL".

When it is to FULL and I perfrom shrink -> log -> it doesnt reduce much but If I set recovery model to simple it reduces it to 1 MB.

The problem is as I see it I need to leave it at full so my back ups have full version of the database.

Now I created a maintenance plan to shrink database, but im noticing it also almost doing no shrinking again because I guess recovery model is at full. How can I get a maintenance plan working which will actually reduce the log file ?

Answer 1

When you switch the recovery mode to shrink the log file you are invalidating prior backups because it breaks the log chain. What you need to do it switch it to simple, shrink it down to what you want, then switch it back and set up regular log backups (I would recommend hourly) in addition to full backups. When a log backup is performed the log is truncated (note, not shrunk. it will remain the same size on disk) and then the database will be free to re-use the space at the beginning of the file instead of appending to the end of the log file and causing file growth.

Answer 2

If yopu cannot shrink the log in FULL recovery model, try to do with your database the following steps:

1. set the database recovery model to FULL.

2. Make the full database backup:

   BACKUP DATABASE myDatabase TO disk='c:\backup\MyDB.back'

3. Make the log backup:

   BACKUP DATABASE myDatabase TO disk='c:\backup\MyDB.back'

4. Shrink the database:

   DBCC SHRINKDATABASE (myDatabase, TRUNCATEONLY)

Now, your log must be shrinked. If not, you can try to look to the field log_reuse_wait_desc filed of sys.databases DMV. This value describes why your log file is not shrinked. For example, once I recieved value 'REPLICATION' in this field. After removing the replication using sp_removedbreplication stored procedure, I sucessfully shrinked the log.

Now, if your DB can be shrinked in Full recovery mode, to shrink the backup on regular basis, you need to do the following: 1. Check the AUTOSHRINK option of the database is ON; 2. Setup the regular full backups (at least once per day). 3. Setup the regular log backups (at least once per 2 hours).

Answer 3

from msdn :

Use the Shrink Database Task dialog to create a task that attempts to reduce the size of the selected databases. Use the options below to determine the amount of unused space to remain in the database after the database is shrunk (the larger the percentage, the less the database can shrink). The value is based on the percentage of the actual data in the database. For example, a 100-MB database containing 60 MB of data and 40 MB of free space, with a free space percentage of 50 percent, would result in 60 MB of data and 30 MB of free space (because 50 percent of 60 MB is 30 MB). Only excess space in the database is eliminated. Valid values are from 0 through 100.

This task executes the DBCC SHRINKDATABASE statement.

Answer 4

Firstly, be aware of shrink operation via maintenance plan will effect on *.mdf file and *.ldf file. so you need to create a maintenance plan with SQL job task and write the following command to can only shrink *.ldf file to your appropriate target size.

use sharepoint_config
go
alter database sharepoint_config set recovery simple
go
dbcc shrinkfile('SharePoint_Config_log',100)
go
alter database sharepoint_config set recovery FUll
go

Note: 100 is called the target_size for the file in megabytes, expressed as an integer. If not specified, DBCC SHRINKFILE reduces the size to the default file size. The default size is the size specified when the file was created.

Secondly, It’s not recommended to perform the shrink operation periodically! Only in some circumstances that you need to reduce the physical size.

You can also check this guide to Shrink a transaction log file Maintenance Plan in SQL Server