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.

Running SQL2008 R2 SP2, database is in FULL Mode

Is it Possible to shrink the transaction log without putting it into SIMPLE mode? Temporarily cannot take log backus and don't want it to grow until I can take the log backups.

I put it in Simple mode but was curious if could shrink in FULL? Think it is; or at least was possible

thanks in advance

Thanks for comments as stated; it is in Simple.

My question is; Can a transaction log be shrunk while it is in FULL mode?

share|improve this question

migrated from stackoverflow.com Mar 10 at 9:19

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

    
If you cannot or don't want to take log backups why you have it on full ??? Change it to simple and revert to full when you can/want to take log backups. –  M.Ali Mar 1 at 16:02
    
Why you can't take log backups ? –  Bogdan Sahlean Mar 1 at 23:58

1 Answer 1

One can attempt to shrink a log file at any time, regardless of recovery model.

The amount it shrinks will only go back to just after the last in-use virtual log file (VLF). VLFs are logical regions of a physical log file. You can use the DBCC LOGINFO command to dump a list of all the VLFs for the database-in-context. In the Status column, 0 means unused, and 2 means in-use. A shrink operation will only reclaim contiguous VLFs at the end of the physical file which have status 0.

The difference between recovery models is the mechanism that maintains the status of each VLF. In FULL recovery, VLFs can't be overwritten at least until they've been backed up in a log backup.


Just a note that switching to SIMPLE and then back to FULL usually isn't a good idea, and should only be used as a very last resort in exceptional circumstances. Doing this will break the log chain (which can be restarted with a full or differential backup) and lose the ability to recover to a point-in-time within the log records that were dropped on the floor.

share|improve this answer

Your Answer

 
discard

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