locked
Database Mirroring Oldest Unsent Transaction RRS feed

  • Question

  • Hello

    We have a mirrored database that is setup to alert when the Oldest Unsent Transactions exceeds 15 minutes.  We seem to be receiving spurious alerts for Oldest Unsent Transactions, when these alerts are occurring Unsent Log is 0 KB.

     

    Responses between Oldest Unsent Transactions have been set to 15 minutes and alerts have been setup to send emails.

     

    When these alerts are raised I have used DBCC OPENTRAN on the mirrored database and found that no long running transactions were present.  Using sp_lock I can see that no locks are held against the mirrored database that could cause a problem and there is no blocked SPIDs.

     

    These alerts start around the turn of the hour or half hour.  We have a number of jobs that run on the hour and half hour.  These jobs run transaction log backups and Full Text Index populations.  Oldest Unsent Transactions alerts continue to be raised even after transaction log backups and full text index population jobs have completed.

     

    Has anyone else had this problem?

     

    Many thanks.

     


    favorite country, Cyprus
    Wednesday, June 15, 2011 3:25 PM

Answers

  • Oldest unsent transaction:-Specifies the number of minutes worth of transactions that can accumulate in the send queue before a warning is generated on the principal server instance. This warning helps measure the potential for data loss in terms of time and is especially relevant for high-performance mode. However, the warning is also relevant for high-safety mode when mirroring is paused or suspended because the partners become disconnected.

    It always

    Warn if the age of the oldest unsent transaction exceeds the threshold. this is informational message only no worries. set the threshold correctly will sort this out because some tilme other transaction happened on Database server and due to this it  increases the threhold time to send log to mirror server and alrt get generated .

    • Proposed as answer by Kuldeep Kushwaha Wednesday, July 6, 2011 11:10 AM
    • Marked as answer by Peja Tao Monday, July 11, 2011 1:19 AM
    Friday, July 1, 2011 9:44 AM

All replies

  • Is this a sync mirror or async? Normally something like that happens if you have long running transactions or very heavily pounded DBs.

    Thursday, June 16, 2011 6:14 AM
  • Hello

    We are using asynchronous database mirroring.  This issue happens on an irregular basis when I have been able to investigate while Oldest Unsent Transaction alerts are being raised I could not find any long running transactions.


    favorite country, Cyprus
    Thursday, June 16, 2011 10:56 AM
  • This warning helps measure the potential for data loss in terms of time, and is particularly relevant for high-performance mode. However, the warning is also relevant for high-safety mode when mirroring is paused or suspended because the partners become disconnected.

    Please check SQL Service account does it have enough permissions or any changes done recently

    Worth checking with network team for any issues

    Check for account under endpoints configured does it have enough permission or any permission changes.

    Please check windows event log for any errors on principal, mirror and witness?


    http://uk.linkedin.com/in/ramjaddu
    Thursday, June 16, 2011 11:04 AM
  •  

    Hello Ram

    Thank you for your reply.

    I have used perfmon to trace packets/sec and found that at the time that Oldest Unsent Transaction are raised rate of packet/sec is about the same as the previous 10 hours.  This would seem to suggest that network load is not to blame for Oldest Unsent Transaction alerts being raised. 

    I am going to use SQL Profiler to trace activity against the database at the times that these alerts are likely to occur.  The alerts have been occuring at approximately the same time past the hour but I cannot find any SQL jobs that run at the same interval.


    favorite country, Cyprus
    Thursday, June 16, 2011 4:33 PM
  • Can you able to telnet endpoint port on bi-direction between principal and mirroring, mirroring and witness? what is your db size?

     


    http://uk.linkedin.com/in/ramjaddu
    Thursday, June 16, 2011 9:34 PM
  • If most of the time you are close to synch I don't think that you have a problem with the connectivity, although it would be interesting to follow mirroring monitor to see if your system gets disconnected when you encounter this problem.

    I have two more questions to ask: Is this a Standard Edition or Enterprise Edition installation? And how many databases are mirrored on this server?

    Lucifer

    Friday, June 17, 2011 6:06 AM
  • Hello

    Thank you for replying.

    We are using Enterprise Edition.  We are mirroring approximately 10 databases. 

    I am starting to get to the point of view that Oldest Unsent Transactions are being raised when Unsent Log is 0 KB (as is the case here) due to the way that Oldest Unsent Transaction is calcuated.  It looks like the Oldest Unsent Transaction is an average of all transactions but I am not too sure how it is actually worked out. 

     

    When I run the following on the server I do not see any delayed transactions.

     SELECT * FROM dbm_monitor_data

     

    WHERE transaction_delay > 0

    Also, I run the following against each mirrored database.

    USE

     

    msdb

     

     

    EXEC

     

     sp_dbmmonitorresults mirrored_database_name, 4, 0;

     

     

    Friday, June 17, 2011 10:57 AM
  • What is your db size? after reading all your information it would be best choice to break mirroring and re-configure it again.....
    http://uk.linkedin.com/in/ramjaddu
    Friday, June 17, 2011 11:45 PM
  • Did you checked whether these open transaction alert is raised right after any database maintenance job is executed?

    See this http://sqlserverpedia.com/blog/sql-server-bloggers/embracing-your-inner-robot/ for information.


    Satya SKJ, SQL Server MVP [Knowledge Sharing Network - www.sqlserver-qa.net]
    Author of SQL Server 2008 R2 Administration cookbook.
    Follow me @sqlmaster.
    Tuesday, June 21, 2011 9:11 AM
  • Hello

    Thank you for the replies.

    It is not possible to break and re-configure mirroring as it working as expected and we have a 24x7 environment.

    The alerts are raised after database maintenance jobs but spurious alerts are raised when database maintenance jobs are not running.

     


    favorite country, Cyprus
    Tuesday, June 21, 2011 1:59 PM
  • Can you please run one day server side SQL trace and see what happening during that time when server sendind alert?

    http://uk.linkedin.com/in/ramjaddu
    Tuesday, June 21, 2011 3:20 PM
  •  

    Thank you for your replies.

     

    We have turned off threshold alerting for oldest unsent transactions.  I was unable to find any activity against the mirrored database that was raising these alerts.

     

    Does anyone know the algorithm that is used to calculate oldest unsent transactions?


    favorite country, Cyprus
    Tuesday, June 28, 2011 2:29 PM
  • This can helps you

    USE [msdb]
    GO
    create table #Mirror

    (database_name sysname,
    [role] int,
    mirroring_state int,
    witness_status int,
    log_generation_rate int,
    unsent_log int,
    send_rate int,
    unrestored_log int,
    recovery_rate int,
    transaction_delay int,
    transactions_per_sec int,
    average_delay int,
    time_recorded datetime,
    time_behind datetime,
    local_time datetime);
    Go
    insert into #Mirror EXEC sp_dbmmonitorresults @MirrorDB, 8, 0; --- Change db name here
    Select datediff(mi,time_behind,time_recorded) as MinutesBehind,* from #Mirror where abs(datediff(mi,time_behind,time_recorded)) > 10


    http://uk.linkedin.com/in/ramjaddu
    Tuesday, June 28, 2011 3:48 PM
  • Oldest unsent transaction:-Specifies the number of minutes worth of transactions that can accumulate in the send queue before a warning is generated on the principal server instance. This warning helps measure the potential for data loss in terms of time and is especially relevant for high-performance mode. However, the warning is also relevant for high-safety mode when mirroring is paused or suspended because the partners become disconnected.

    It always

    Warn if the age of the oldest unsent transaction exceeds the threshold. this is informational message only no worries. set the threshold correctly will sort this out because some tilme other transaction happened on Database server and due to this it  increases the threhold time to send log to mirror server and alrt get generated .

    • Proposed as answer by Kuldeep Kushwaha Wednesday, July 6, 2011 11:10 AM
    • Marked as answer by Peja Tao Monday, July 11, 2011 1:19 AM
    Friday, July 1, 2011 9:44 AM