SQL Server Database Mirroring – Performance Metrics and Warning Thresholds

Hi Friends,

This is an old writing of mine from my old blog – just wanted to re post this here.

Management by exception – don’t be too sure that things are running fine. Probably, that’s the time you need to worry when everything is running smoothly, especially your Database Mirroring session .

When your SQL Server Database Mirroring session is established and running smoothly, you can use the Mirroring Monitor to monitor the session.

1_SQL_Server_Database_Mirroring_Performance_Metrics_Warning_Thresholds

You can see many performance metrics including the sent rate, restore rate, unsent log, etc…

But more importantly, what do you when you want to be alerted when one these performance metrics cross their limits. So what do I mean?

Lets take an example: Imagine a scenario where the mirror server is down and there are continuous transactions occurring on the principal server. In such a case, there will be a back log on the principal server which is displayed as unsent log. But you certainly don’t want the unsent log to get huge and you should by notified, rightly so.

Therefore, DB mirroring in SQL Server offers you threshold warnings.  You can define threshold warnings in Unsent Log, Unrestored log, Oldest unsent transaction & Mirror commit overhead.

Unsent log – how many kilobytes KBs of unsent log will generate a warning?

Unrestored log – how many KBs of unrestored log will generate a warning?

Oldest unsent transaction – Do you want a warning when the oldest unsent transaction crosses 2 mins or 4 mins ??

Mirror commit overhead – This is relevant only in high-safety mode. Specifies the number of milliseconds of average delay per transaction that are tolerated before a warning is generated on the principal server.

You can choose to be alerted via mail, NET SEND msg or pager by configuring the relevant alerts for these warning thresholds.

How do you do it?

1. First specify the thresholds that are applicable to your environment.

2. You should know the error number / event ID for these warnings, which is as follows: Unsent log-32042, Unrestored log-32043, Oldest unsent transaction-32040, Mirror commit overhead-32044.

3. Next, configure Alerts under SQL Server Agent and specify the above error numbers in the configuration dialog box.

4. You need to choose, how you want to be notified. If you choose mail, make sure DB mail is configured appropriately.

 

 

Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

One Comment on “SQL Server Database Mirroring – Performance Metrics and Warning Thresholds”

  1. Hi,

    I am working in a small concern. In my concern we were configure database mirroring.That database mirroring monitor tool show mirror server status “NOT CONNETCED TO XXXXXX” Server.Am working sql server 2008 and OS windows server 2008 bothare enterprise edition. Please advise me what shall i do now . how to encounter it ..

Leave a Reply

Your email address will not be published. Required fields are marked *