The DBCC SHRINKFILE command will be mirrored from the principal to the mirrored database. Here's some proof.
Create a sample database on the principal:
create database MirroredDb;
go
Create the same database from a backup with NORECOVERY:
restore database MirroredDb
from disk = '\\backupdir\MirroredDb.bak'
with norecovery;
go
Setup your mirroring session however which way you choose.
On the principal database look at the database file sizes:
use MirroredDb;
go
select
name,
size
from sys.database_files;
My result set looks like this following:
name size
MirroredDb 392
MirroredDb_log 104
On the mirror database, create a snapshot and look at the same information:
create database MirroredDbss
on
(
name = 'MirroredDb',
filename = 'c:\sqlserver\MirroedDb.ss'
)as snapshot of MirroredDb;
use MirroredDbss;
go
select
name,
size
from sys.database_files;
My result set looks like the following:
name size
MirroredDb 392
MirroredDb_log 104
Now grow the transaction log file on the principal database (I brought it to 1 GB):
alter database MirroredDb
modify file
(
name = MirroredDb_log,
size = 1GB
);
go
Looking at the principal database's transaction log size, we now see the adjusted size:
use MirroredDb;
go
select
name,
size
from sys.database_files;
My result set looks like the following:
name size
MirroredDb 392
MirroredDb_log 131072
Create another snapshot on the mirrored database, and look at the transaction log file size there:
create database MirroredDbss2
on
(
name = 'MirroredDb',
filename = 'c:\sqlserver\MirroedDb2.ss'
)as snapshot of MirroredDb;
use MirroredDbss2;
go
select
name,
size
from sys.database_files;
My result set looks like the following:
name size
MirroredDb 392
MirroredDb_log 131072
Now do the DBCC SHRINKFILE on the principal:
use MirroredDb;
go
dbcc shrinkfile('MirroredDb_log', 0);
go
select
name,
size
from sys.database_files;
My result set is the following:
name size
MirroredDb 392
MirroredDb_log 104
Create a third and final snapshot on the mirrored database, and look at the size:
create database MirroredDbss3
on
(
name = 'MirroredDb',
filename = 'c:\sqlserver\MirroedDb3.ss'
)as snapshot of MirroredDb;
use MirroredDbss3;
go
select
name,
size
from sys.database_files;
And I get the following result set:
name size
MirroredDb 392
MirroredDb_log 104
So as you can see here, the DBCC SHRINKFILE command is in fact mirrored to the mirror database.
Best Answer
The error is caused by how long it's taking SQL Server to grow your transaction log.
Assuming the error message you've quoted is for the last grow, SQL Server is claiming that it's taking about 17 minutes for SQL Server to add just under 5GB to your transaction log file.
This can be caused by a number of issues:
Slow disk: If your disks are particularly old and slow it will take SQL Server longer to extend the files
Disk Contention: If SQL Server is having to compete for access to the disks, then it will be slow to extend them. Common causes of this could be having only 1 disk for OS, Data and Logs, or another application on the machine accessing the same disk as SQL Server
There are a number of things you can try to alleviate the problem:
Move your files onto seperate physical disks if possible. Typically you'd like to have 3 disks for OS, Data and Logs.
Use faster storage. If you can upgrade your disks then this will alleviate the wait for autogrowth to complete
Modify your autogrowth setting to be a fixed size. That way you'll get consistent growth rather than exponential as you are with your current percentage setting. Pick a value that is an acceptable compromise between lots of file growth events and waiting for the growth to happen.
Recreate your database, and preallocate the log file required to accommodate the queries you plan to run. This will remove the autogrowth events (unless you exceed that size).
Hope that helps.