Sql-server – How does DBCC SHRINKFILE() affect backups

dbccsql serversql-server-2005

I have a pile of SQL 2005 databases that have been around for a very long time and, for varying reasons, experienced transaction log autogrowth. Regular backups now keep the transaction log usage under control, but the files are still large and fragmented.

I'd like to reclaim some of that space and boost performance by reducing the number of VLFs. However, I'd also prefer not to disrupt our scheduled backups. How does DBCC SHRINKFILE() affect the transaction log backup chain? Or does it? Does this change if I add TRUNCATEONLY to the command?

Note that this is a one-time fix. I'm well aware that regularly shrinking logfiles is a Bad Thing – had these databases been configured with rational initial sizes and autogrowth settings and had backups run perfectly every day of their history, this would not be an issue.

Best Answer

Per Microsoft Support http://support.microsoft.com/kb/272318, SQL Server shrinks the log file by removing as many VLFs as it can. It does so while trying to hit a target size without messing up your backup chain.

However, if you do use TRUNCATE_ONLY, the sequence gets messed up, so you'll need to either do that right before your scheduled backup or do full backup right after that.

Related Solutions

Sql-server – Save Results of DBCC ShrinkFile

The easiest way I see it is a batch file using sqlcmd:

sqlcmd -S .\SQL2008R2 -E -Q "dbcc loginfo" >> log.txt

The redirect operator appends data at the end of the file.

-Q parameter was used for closing the sqlcmd session immediately.

Sql-server – How to shrink the physical Transaction Log file when it’s the principal in a mirror

From what I can tell, I can't.

I believe much of the log file is waiting to be mirrored to the mirror server, but the mirror is down and can't be resumed because the mirrored transaction log has also grown to take up all the space on the disk.

This theory is further backed up by the fact once I remove the mirroring, the DBCC SHRINKFILE command correctly shrinks the physical log file, and the log_reuse_wait_desc is back to waiting on LOG_BACKUP

I can't shrink the log file on the mirrored server because it's acting as a mirror and can't be opened, so I think the mirror is unrecoverable.

So I am going to delete the mirror entirely, and set everything up again (very slow process with a 300GB database). And this time, I'll make sure the transaction log size is fairly small before starting up the mirror, and that the transaction log backups are running once the mirror is back up and running.

I'm also going to set a limit on how big the transaction log can grow on the production server, so the mirror doesn't ever try to grow its transaction log beyond the size available on the disk.

Best Answer

Related Solutions

Sql-server – Save Results of DBCC ShrinkFile

Sql-server – How to shrink the physical Transaction Log file when it’s the principal in a mirror

Related Question