Sql-server – Maintenance plan is backing up database transaction log but it is not selected

maintenance-planssql serversql-server-2008transaction-log

I have a strange problem, the transaction log for my database is being backed up however it is not selected.

I change the names of the server and databases in the screenshots to protect my information from the bad guys. 😉

Here is the issue that server "XYZ" is having, the screen shot below is showing the sub-plan for the "Transaction Log Backup" maintenance job. As you can see here "Database2" is showing up in the plan task.

However in the properties plan task "Database2" is not selected. In order for the transaction log to be backed up the database has to be selected, like "Database1", right?

Despite the fact that "Database2" is not selected the transaction log is being backed up. I verified the properties of the database when the last transaction log was done.

So, I am a little confused about this and would like to know if anyone else here came across this and how you resolved it.

Server specs: SQL Server 2008 SP2 running on Windows Server 2008 R2 Standard

Best Answer

Your radio button is on All user databases and NOT on These Databases

Related Solutions

Sql-server – Transaction log growing on a mirrored principal

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.

Sql-server – Transaction Log maintenance on Mirror database

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

Related Solutions

Sql-server – Transaction log growing on a mirrored principal

Sql-server – Transaction Log maintenance on Mirror database

Related Question