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

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.

Cause

Database mirroring will change the physical file sizes only after a checkpoint.

Workaround

Method 1

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'

Method 2

Issue a manual checkpoint after shrinking the files on the principal.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Свойства

Номер статьи: 937531 — последний просмотр: 12 мая 2010 г. — редакция: 1

Отзывы и предложения