Mirror, Synchronized / Restoring...
is the typical state for the mirror instance.
You can determine why the log file is growing by issuing the following command in a SQL Server Management Studio query window:
SELECT name, LOG_REUSE_WAIT_DESC
FROM sys.databases
WHERE name='your_database_here';
This will show several possible choices, such as:
NOTHING
CHECKPOINT
LOG_BACKUP
ACTIVE_BACKUP_OR_RESTORE
ACTIVE_TRANSACTION
DATABASE_MIRRORING
REPLICATION
DATABASE_SNAPSHOT_CREATION
LOG_SCAN
OTHER_TRANSIENT
You may need to perform at least one transaction log backup, among other actions depending on the LOG_REUSE_WAIT_DESCRIPTION
shown in the query above. For instance, there may be a long-running transaction that is forcing the log files to not be re-used.
We are doing log backups on the principal database, and my question is what are the gotchas with doing a log backup on a mirror?
You can't do a log backup on the mirror database.
At least one full database backup from the mirror has to be completed before doing a log backup, in that case are there special options that need to be used because it is a mirror?
You can't do a full database backup on the mirror database either.
Take this for instance: I have Server1
which houses the principal database AdventureWorks2012
, and I have Server2
which contains the mirror. Here's what happens when I try to run backups on the mirror database (on Server2
):
use master;
go
backup database AdventureWorks2012
to disk = 'c:\sqlserver\AW_mirror.bak';
go
Msg 954, Level 14, State 1, Line 2
The database "AdventureWorks2012" cannot be opened. It is acting as a mirror database.
Msg 3013, Level 16, State 1, Line 2
BACKUP DATABASE is terminating abnormally.
backup log AdventureWorks2012
to disk = 'c:\sqlserver\AW_mirror.trn';
go
Msg 954, Level 14, State 1, Line 1
The database "AdventureWorks2012" cannot be opened. It is acting as a mirror database.
Msg 3013, Level 16, State 1, Line 1
BACKUP LOG is terminating abnormally.
Take a look at this Database Mirroring FAQ by Robert Davis. I will quote him regarding this operation and the mirror database transaction log maintenance:
When you backup the log on the principal, the virtual log files (individual units within the log file) are marked as re-writable. The same VLF’s are marked as re-writable in the mirror log file as well. The VLF status is mirrored on the database.
So there you have it. If you have transaction log backups on the principal then similar log reuse behavior will be mirrored to its partner database.
Best Answer
Transaction log is the most important element in SQL Server. It is like a sequential journal that logs all the changes made to the database and contains enough information to undo / redo the changes in the event of crash recovery to keep the database in a consistent state.
Highly recommend to read : Understanding Logging and Recovery in SQL Server
All your question are just because you dont have proper understanding of how transaction log works.
To keep transaction log in shape, you should take regular log backups (more frequently) on the principal server. Check - How do we handle transaction log maintenance for a mirrored database?
To answer your questions :
No this is not possible and not advisable to delete T-Log.
Mirroring uses T-log to replay everything from principal server to mirror server. So Full recovery is only possible. check : recovery models.
Not feasible. Please read the links that I have suggested.