From what I can tell, I can't.
I believe much of the log file is waiting to be mirrored to the mirror server, but the mirror is down and can't be resumed because the mirrored transaction log has also grown to take up all the space on the disk.
This theory is further backed up by the fact once I remove the mirroring, the DBCC SHRINKFILE
command correctly shrinks the physical log file, and the log_reuse_wait_desc
is back to waiting on LOG_BACKUP
I can't shrink the log file on the mirrored server because it's acting as a mirror and can't be opened, so I think the mirror is unrecoverable.
So I am going to delete the mirror entirely, and set everything up again (very slow process with a 300GB database). And this time, I'll make sure the transaction log size is fairly small before starting up the mirror, and that the transaction log backups are running once the mirror is back up and running.
I'm also going to set a limit on how big the transaction log can grow on the production server, so the mirror doesn't ever try to grow its transaction log beyond the size available on the disk.
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 transaction log file (LDF) is made up of lots of virtual log files (VLFs) inside. Think of it like a cabinet with several pull-out drawers. You could choose a large cabinet, or a small one - but it's still going to be a fixed size with just different numbers of drawers.
As SQL Server works, it puts your transactions into drawers (VLFs.) It starts at one end of your cabinet, fills up the first drawer, then when space runs out in that drawer, it moves on to the next drawer.
When you back up the transaction log, what you're really doing is:
Backups don't change the size of your cabinet (log file).