2

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?

Improve this question
2
  • 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. Commented Mar 1, 2014 at 16:02
  • Why you can't take log backups ? Commented Mar 1, 2014 at 23:58

1 Answer 1

Reset to default
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.

Improve this answer