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'