Is Database Mirroring Causing The Transaction Log To Fill Up

Database Mirroring is a feature to increase database availability by setting up a principal database and a mirrored database.  When the principal database goes down, SQL Server will switch to the mirrored database to minimize down time for critical databases. 

In this article, I will describe some steps a DBA can take to check for mirroring on SQL Server, give a status of the mirroring state and some options for resolving any problems with the transaction log that might be caused by Database Mirroring.

First, a high level introduction to Database Mirroring in SQL Server.  Basically, there is a Principal database where your connections and applications are pointed. Then there is a Mirrored database that is a copy of the principal database, which is ready to take over just in case the principal database goes down.  Optionally, there is a third server that acts as a Witness to the communications between the principal and mirror databases when enabling the Automatic Failover option.

Every insert, update, and delete operation that occurs on the principal database is sent to the mirror database through active transaction log records.  The mirror server applies these log records in sequence as quickly as possible.  There are two operating modes that dictate how SQL Server manages the way the principal and mirrored databases handle the transactions. 

The first operating mode is high-performance (asynchronous) mode, where there is just a principal server and a mirror server.  In this mode, performance is priority at the risk of a potential loss of data and high availability.   What’s important to note here is that in this mode once the principal server sends the log for a transaction to the mirror, it does not wait for a confirmation from the mirror acknowledging the transacation.  In this case, the principal will just keep sending log data to the mirror regardless of the work load. 

The second operating mode is High Safety failover (synchronous) mode. In this mode, all committed transactions are guaranteed to be written to disk on the mirror server.  The principal database will send the transactions over to the mirror and wait to commit those transactions until it gets a verification from the mirror server.  If for some reason, the link between the two databases is affected, the transaction log will continue to grow on the principal database until it receives acknowledgement from the mirrored database. As a result the transaction log will grow and not re-use space. 

For the following examples, I setup a simple database called DBA, and configured it for High-Safety failover mirroring using a witness server. For information on establishing Database Mirroring please refer to this link: http://msdn.microsoft.com/en-us/library/ms190941(v=sql.110).aspx.   In addition, I created a small script that inserts data into a table to fill up the transaction log. 

First, I want to make sure that the database is indeed being mirrored.  For this I can query the sys.database_mirroring catalog view on the principal server.  The view will return rows for all of the databases on the server, however only the mirrored databases will return data, the non-mirrored databases will contain Null values.  

Use master
Go

SELECT
     d.name
    ,m.mirroring_state_desc
    ,m.mirroring_role_desc
    ,m.mirroring_safety_level_desc
    ,m.mirroring_partner_instance AS 'Mirror Server'
    ,m.mirroring_witness_name AS 'Witness Server'
    FROM
    sys.database_mirroring m
    INNER JOIN 
    sys.databases d
    ON 
    m.database_id = d.database_id

The results below tell me that my DBA database is setup for mirroring along with the name of the Mirrored Server and the name of the Witness server. The results also tell me that the database is currently synchronized with the mirror and everything is looking good.

Zoom in  |  Open in new window

Once I know the database is being mirrored I can also execute a system stored procedure to view the mirroring stare and status.  

EXEC sp_dbmmonitorresults 'DBA',2,1

When I execute this, it will return the following information, showing me the mirroring state of 4 (synchronized).  The mirroring_state column returns int values from 0-4 for the mirrored database, where 0 is suspended, 1 is disconnected, 2 is synchronizing, 3 is pending failover and 4 is synchronized.  You can refer to the MSDN article for a detailed explanation at http://msdn.microsoft.com/en-us/library/ms366320(v=sql.110).aspx .

So far, everything is looking good and we have no problems. 

What if the mirroring is paused or the mirror server is down and unable to process the logs sent from the principal.  At this point the principal is holding on to the transactions, waiting to hear from the mirror.  As a result the log is growing and unable to reuse any space. 

The first thing I can do is check the log_reuse_wait_desc column in sys.databases catalog view in the master database using the following query.

  SELECT
        name
    ,   recovery_model_desc
    ,   log_reuse_wait_desc
    FROM
        sys.databases

The results for my DBA database show that the database is waiting for a log backup.  

We run a log backup, and run the query again. Now we see the wait is DATABASE_MIRRORING

Ok, so now I know that the principal database is waiting to send transactions to the mirrored database. What is the state of mirroring on my server?

Going to the database properties mirroring page, I can see that mirroring is paused or suspended, as shown below.

I can also execute the sp_dbmmonitorresults system stored procedure again to see that the mirroring_state is at 0 or Suspended. It also shows the size of unsent_log in kb.

EXEC sp_dbmmonitorresults 'DBA',2,1

Now I can see that the log is waiting for database mirroring.  At this point, I  can change the status of mirroring or remove mirroring a couple of different ways.

First, under the Database properties Mirroring page there are options to Resume mirroring or to remove it .

It will switch the status to Synchronizing and will begin to transfer the data.

Click on Refresh and it will update to Synchronized 

Or, I can use the Alter Database command. This will resume the mirroring session.

ALTER DATABASE DBA SET PARTNER RESUME;

On the other hand, I can turn off mirroring by running the following command

ALTER DATABASE DBA SET PARTNER OFF;

Querying sys.databases again, I can see that the log_reuse_wait is back at NOTHING

There are a number of reasons why a transaction log continues to grow, in this article my focus was to provide some solutions to clear the log when database mirroring may be the cause. 

Proactive alerts and warnings can be set for mirroring to avoid these potential problems.  There is a white paper on Alerting on Database Mirroring Events (http://technet.microsoft.com/library/Cc966392 ) that goes into detail on configuring and setting up alerts. 

References

• http://msdn.microsoft.com/en-us/library/ms190202(v=sql.110).aspx
• http://msdn.microsoft.com/en-us/library/dd207006(v=sql.110).aspx#Sync
• http://msdn.microsoft.com/en-us/library/ms190941(v=sql.110).aspx
• http://msdn.microsoft.com/en-us/library/ms366320(v=sql.110).aspx
• http://msdn.microsoft.com/en-us/library/bb522476(v=sql.110).aspx
• http://www.sqlservercentral.com/articles/Database+Mirroring/117489/