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
Why exactly are you choosing the shrink the log routinely? Is it because you need to reclaim volume space? Hard disk space is so cheap that shrinking the log really shouldn't be a go-to maintenance item. Especially since you are seeing one of the main reasons not to shrink the log: Because your logged operations actually do need that additional log space.
Stop shrinking your log. Find the extra space somewhere so that your transaction log can remain that larger size. You haven't given us any numbers (initial size, size the log file eventually grows to, etc.) but the growth operation is expensive and can be time consuming. Log files can't even benefit from a file allocation optimization like Instant File Initialization.
If you are worried about the index rebuild, it can be a minimally logged operation and you could utilize the bulk logged recovery model. In that case, you could take a log backup prior to, then switch to the bulk logged recovery model, do your index rebuilds, then set your recovery model back to full. This could potentially prevent you from doing a point-in-time recovery during that time span that you are in the bulk logged recovery model, so that may be a DR consideration.
There are strategies here. But shrinking your log every night should be on the bottom of that list.
As for the rest of your question, rebuilding indexes (depending on their fragmentation level, your maintenance window, etc.) is a prudent and common maintenance task.