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'

Sql Server MirroringRESTORE cannot operate on database because it is configured for database mirroring Use ALTER DATABASE to remove mirroring if you intend to restore the databaseDatabase mirroring is currently provided for evaluation purposes only and is not to be used in production environments To enable database mirroring for evaluation purposes use trace flag 1400 during startupMS SQL server shutdownbackup sql databaseMS SQL server 2005 attach databse without ldf fileConnection PoolingMS SQL server mirroring remove end points master keygodaddy hosting MS SQL server configurationsql server default portsclient server communication

Author

My name is Satalaj, but people call me Sat. Here is my homepage: . I live in Pune, PN and work as a Software Engineer. I'm former MVP in ASP.net year 2010.
Disclaimer: Views or opinion expressed here are my personal research and it has nothing to do with my employer. You are free to use the code, ideas/hints in your projects. However, you should not copy and paste my original content to other web sites. Feel free to copy or extend the code.
If you want to fight with me, this website is not for you.
 

I'm Satalaj.