none
DB snapshot taking more time on Mirrored database. RRS feed

  • Question

  • Hi Team,

    SQL is 2008R2+sp4 and window 2008R2.

    We have A(Source) and B(Dest) servers which is configured for the DB mirroring(say x db in A) everyday the batches(configured from autosys) first will perform some operations like : Dropping indexes,constraints and then recreate- post this the database snapshot will get creates on B but the problem is that  it takes 90sec(~) to generate the snapshot but on issue day(it is not specific day) it takes more time like 45 or 50Mins to generate the snapshot in this most of the time it took in Redo phase.

    We have enabled all the DB mirroring counters,windows counters- we dont see the issues that indicates of long & there batch is normal everyday(there is no load/unload (or)batch increase) nothing has increased.

    What else we have to check to troubleshoot this issue & how this can be fixed.




    Regards, S_NO "_"

    Monday, December 2, 2019 3:58 PM

All replies

  • That's really strange as the snapshot creation should not be doing anything.

    1) Do you  have open transactions, LONG running transactions?

    2) Perhaps  you drop older snapshots first?



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Tuesday, December 3, 2019 5:04 AM
  • Hi S_NO,

    As Uri mentioned, please check if you have some long running transactions. When creating  a database snapshot, the snapshot contains all open transactions at the time. As part of the initialization process, it calls the recovery process on the snapshot. Part of the recovery process is to roll back all active transactions in the snapshot (they do not roll back in the actual database). If there are long running transactions running at the time of the snapshot creation, the rollback can take a really long time. 

    I also found a similar thread, OP’s issue was related to tempdb.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, December 3, 2019 8:36 AM
  • That's really strange as the snapshot creation should not be doing anything.

    1) Do you  have open transactions, LONG running transactions?

    >>During that time nothing else running,only there batches(everyday it is normal w.r.t load/unload) starts post this snapshot begins.....

    2) Perhaps  you drop older snapshots first?

    Yes first it will drop then it will create.



    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence



    Regards, S_NO "_"

    Tuesday, December 3, 2019 8:37 AM