Take the 2-minute tour ×
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. It's 100% free, no registration required.

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 stackoverflow.com Jan 22 '12 at 13:04

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

3  
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
1  
Well you need to backup the transaction log then to allow the space to be reused. –  Martin Smith Aug 24 '11 at 10:36
2  
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

3 Answers 3

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

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

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

share|improve this answer

Your Answer

 
discard

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