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.
Solution for restoring a published database
We faced a similar problem: A published database is stored on Server1. Every day this database will be backed up and restored on Server2.
- We frequently got error messages:
LOG full due to REPLICATION
log_reuse_wait_desc
was set to REPLICATION
.
- Replication could not be removed, because this database was not published on Server2.
Solution
After restoring the database enable publication and remove it:
USE MyDatabase
GO
-- 1.) enable publication for MyDatabase
EXEC sp_replicationdboption
@dbname = 'MyDatabase',
@optname = N'publish',
@value = N'true';
GO
-- 2.) remove publication from database. Use the PUBLICATION-name (not database name)
sp_removedbreplication 'Publ_MyDatabase','both'
-- 3.) disable publication for MyDatabase
EXEC sp_replicationdboption
@dbname = 'MyDatabase',
@optname = N'publish',
@value = N'false';
GO
-- Verify: log_reuse_wait_desc should have changed from REPLICATION to NOTHING
SELECT name, log_reuse_wait_desc, * FROM sys.databases WHERE name = 'MyDatabase'
Best Answer
Your
log_reuse_wait_desc
is database_mirroring and you mentioned it currently stuck in a Suspended stateYou should find a cause your mirroring was suspended. Try to search messages that include
Database mirroring has been suspended
in ERRORLOG file on the principal. It could be like one below:Using error number you are able to get its description from
sys.messages
:This case:
That means there is not enough physical space to expand a file on the mirror.
After solving any found errors you have to turn on your mirroring. Run on the principal:
In case you won't be able to find any related errors in
ERRORLOG
, just try to resume your mirroring using a command above and then look after the database and check yourERRORLOG
for errors once the mirrored database goes to the suspended state (if it does).