Joseph Michael Pesch
VP Programming

SQL DBCC ShrinkFile Not Working (i.e. Executes Successfully But File Does Not Shrink)

by 22. August 2013 12:41

When attempting to run "DBCC ShrinkFile" SQL command to shrink a transaction log file on a database that has replication enabled it may appear to have run successfully; however, the log file may not actually shrink.  To resolve this issue you can add a call to "EXEC sp_repldone" to allow the server to take action on the log file.  Below is a sample process that I have added to a nightly job to take place after the full DB backup executes.

use DBName;
go
alter database DBName set recovery simple;
go
exec sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
go
dbcc SHRINKFILE (DBName_log, 1);
go
alter database DBName set recovery full;
go

Tags:

SQL Server

Comments are closed