Microsoft KB Archive/937531

= The shrink operation is not duplicated on the mirror database when you use database mirroring in SQL Server 2005 =

Article ID: 937531

Article Last Modified on 11/20/2007

-

APPLIES TO


 * 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

-



SYMPTOMS
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.



WORKAROUND
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'



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the &quot;Applies to&quot; section.

Keywords: kbtshoot kbprb kbexpertiseadvanced kbsql2005engine KB937531

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.