Post reply

Blocking

  • September 1, 2009 at 9:49 am

    #1047627

    Everyone is correct. I would like you to take a rest but you can really do something here to make the rollback faster. Lessen the load on your server. Try to see if resource deficiency is there. Rest is ur luck.

    -LK

  • September 1, 2009 at 10:16 am

    #1047651

    Elliott W (8/31/2009)

    If you stop and start SQL I can almost guarantee your database will be corrupted.

    Not at all. If you stop SQL and restart it, it will still roll the transaction back during the restart-recovery.

    Providing the transaction log is intact, SQL can handle unexpected shutdowns and restarts without causing any damage to the data file. That, in fact, is the primary reason for the transaction log.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 1, 2009 at 10:19 am

    #1047656

    Elliott W (9/1/2009)

    This was especially true in 2000, almost without fail if I killed a process and it was in the middle of a rollback and then I killed the server it would trash the DB.

    If that happens, I would take a long, hard look at the IO subsystem, especially at the disk write cache and make sure that it's properly battery-backed and that the batteries are working.

    A non-battery backed write cache and a hard server shutdown can indeed cause corruption, but that's not SQL Server's fault.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 1, 2009 at 10:28 am

    #1047662

    GilaMonster (9/1/2009)

    If that happens, I would take a long, hard look at the IO subsystem, especially at the disk write cache and make sure that it's properly battery-backed and that the batteries are working.

    A non-battery backed write cache and a hard server shutdown can indeed cause corruption, but that's not SQL Server's fault.

    It happened on several different servers real and virtual and rarely involved a hard server down, sometimes a hard SQL down but rarely a server. Also, I'm not trying to blame anything. I'm just saying that it is a REALLY bad idea to down a server during the rollback and that it is better to take your lumps and wait..

    CEWII

  • September 1, 2009 at 10:45 am

    #1047670

    Hi,

    Please consider this scenario, if you have a PRDO box and some ADHOC query which was not properly tested in DEV/UAT was resource expensive and blocking others and your last resort was to KILL that SPID after approval. You KILLED it. After that it was not ROLLING BACK for hours and holding lot of resources.

    What you think we should do?

    Thanks,

    Raj

  • September 1, 2009 at 11:02 am

    #1047679

    rajdba (9/1/2009)

    What you think we should do?

    Wait or restart SQL. Those are your only options. Note that if you restart SQL and do not have 2005 Enterprise edition, the entire database will be unavailable while the transaction rolls back.

    Usually waiting is the better option.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • September 1, 2009 at 11:14 am

    #1047684

    Gail Shaw

    You are right, waiting is better than recycling.

    I think we can control all these by restricting ADHOC and untested queries on PROD.

    I have faced the problem in DEV /UAT and at that time recycling the SQL Server is fast then that of waiting and never happened the Database got corrupted. It always had roll backed automatically.

    I wish and pray it never happens with me on PROD.

    I am anxious to know what happened with Sourav’s BOX.

    Thanks

    Raj

  • September 1, 2009 at 12:31 pm

    #1047741

    Hello Raj

    Our Sr. DBA team did SQL Cluster Failover to rectify the issue!!

    Thanks.

  • September 1, 2009 at 1:01 pm

    #1047760

    .... a cluster failover is not more than a shutdown on one side and restart on the other one, which involves the rollback anyway when the DB is started up.....

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • September 1, 2009 at 2:18 pm

    #1047813

    Sourav,

    Good to hear that!!!

    Any Data corruption or unwanted problem after failover?

    Thanks

    Raj

  • September 1, 2009 at 2:49 pm

    #1047840

    Sourav,

    You will need to let the rollback complete - it is going to happen one way or another. If there is a massive impact on the server due to the rollback, try to mitigate it otherwise.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 11 posts - 16 through 25 (of 25 total)

You must be logged in to reply to this topic. Login to reply