Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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 ?

share|improve this question

migrated from Jan 22 '12 at 13:04

This question came from our site for professional and enthusiast programmers.

Don't shrink the log or database as a daily scheduled task. Shrinking the DB can cause fragmentation. Shrinking the log is a complete waste of resources as it will just spend time auto growing again (for which it needs to zero out the file) the next day. – Martin Smith Aug 24 '11 at 10:32
Need to shrink it as I am getting server out of space issues – StevieB Aug 24 '11 at 10:35
Well you need to backup the transaction log then to allow the space to be reused. – Martin Smith Aug 24 '11 at 10:36
If you have space issues from a 1 GB log file then you have larger space issues you need to address. Some of my DBs have logs close to 1TB at times. Martin is right, shrinking is a waste of time (unless you are deploying). – JNK Aug 24 '11 at 10:38
Does backing it up reduce the size of the original ? – StevieB Aug 24 '11 at 10:51

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.

share|improve this answer

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:


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

share|improve this answer

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.

share|improve this answer

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
alter database sharepoint_config set recovery simple
dbcc shrinkfile('SharePoint_Config_log',100)
alter database sharepoint_config set recovery FUll

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 useful guide to Shrink a transaction log file Maintenance Plan in SQL Server

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.