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

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

The shrink operation is not duplicated on the mirror database when you use database mirroring in SQL Server 2005.
  If you are trying to shrink database which has mirrored copy on other server, shrink operation will not get duplicated on mirrored.
You can resolve this issue by creating following script on your principal server using master database.dbcc shrink database for Principal and mirrored scenario.

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

Now, you can shrink principal database using above stored procedure
e.g.
EXEC sp_shrink_mirrored_database my_db_name', 10
or

EXEC sp_shrink_mirrored_database 'my_db_name'

Currently rated 5.0 by 1 people

  • Currently 5/5 Stars.
  • 1
  • 2
  • 3
  • 4
  • 5

Tags: ,

Author

code tutorial