ShrinkDB vs. Backup for MSSQL

Shrinking your log files is usually a bad idea. Normally the log file will assigned a fixed size during the database creation. The log files contain both all the uncommitted transactions and the committed transactions. During the backup, the committed transactions are removed from the log file. Still there are times when you need to shrink the log files. Microsoft offer 2 ways of shrinking the log files: dbcc shrinkdb and backup log

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

DBCC SHRINKDB

You must enable the full recovery for the shrinking of the logs.

  1. ALTER DATABASE sql911
  2. SET recovery FULL;
  3. go
[Get Code]
Command(s) completed successfully.

Files are always shrunk from the end and only for the existing free space at the end of the file.

You should use the dbcc shrinkdb only for shipping logs or for backing up to a very small tape/hard drive.

Backups of transaction logs

When SQL Server is finished with the backup of the transaction log, it removes all the completed transactions. You should not usually do a manually log truncation unless if it's for doing a log shipping and you don't have enough space.

The transaction log cannot be backed up during a full database backup or a differential database backup.

Do not back up the transaction log while the database or the file backup is completed because the transaction log contains all the changes made to the database after the last backup was created.

  • dbcc shrinkdb: removes the free space from the end of the log file. It doesn't remove committed transactions.
  • backup log: removes the committed transactions from the log file.

If you do not want to deal with log backups, use the simple recovery model.