When you use database mirroring in Microsoft SQL Server 2005, SQL Server automatically propagates any changes on the principal database to the mirror database. However, if you run the DBCC SHRINKDATABASE statement or the DBCC SHRINKFILE statements to shrink the principal database, the shrink operation is not duplicated on the mirror database.
Database mirroring will change the physical file sizes only after a checkpoint.
To work around this problem, run the following statements to create a new stored procedure in the master database. Then, use this stored procedure to shrink the principal database instead of running the DBCC SHRINKDATABASE statement or the DBCC SHRINKFILE statement.
use master go if object_id ('sp_shrink_mirrored_database', 'P') is not null drop proc sp_shrink_mirrored_database go create procedure sp_shrink_mirrored_database @dbname sysname, @target_percent int = null as begin declare @filename sysname declare @filesize int declare @sql nvarchar(4000) if @target_percent is null dbcc shrinkdatabase (@dbname) else dbcc shrinkdatabase (@dbname, @target_percent) declare c cursor for select [name], [size] from sys.master_files where type=0 and database_id = db_id (@dbname) open c fetch next from c into @filename, @filesize while @@fetch_status=0 begin set @filesize=(@filesize+1)*8 set @sql='alter database [' + @dbname + '] modify file ( name=' + @filename + ', size=' + cast(@filesize as nvarchar) + 'kb )' execute sp_executesql @sql fetch next from c into @filename, @filesize end close c deallocate c end go
For example, if you want to shrink the mydb database, run the following statement.
EXEC sp_shrink_mirrored_database 'mydb'
Issue a manual checkpoint after shrinking the files on the principal.
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Standard X64 Edition, Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems, Microsoft SQL Server 2005 Enterprise X64 Edition, Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems