SQL Server 2012 – Handling Non-Active Transactions in Log File and Shrinking

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.

Here is the answer to my own question.

Run the below query to get information about the log file's reuse wait:

SELECT log_reuse_wait_desc
FROM sys.databases
WHERE name = 'DBName'

I got the following output:

log_reuse_wait_desc
-------------------
REPLICATION 

There were some replication-related objects remaining in the database, even after removing the replication.

To remove the replication from the database, sp_removedbreplication can be used. But it didn't work for us as replication was not active at the time and actually replication had been removed long before.

The solution was to import the database contents to another database using the import option of SQL Server.