Post reply

Blocking

  • August 31, 2009 at 6:00 pm

    #141330

    Hello Guys

    Today in the backend server suddently found an SPID creating blocking.

    So had to kill the SPID.

    But got the below message..

    SPID 77: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.

    My questions are::

    (1) Does it not kill the SPID immediately or it will take much time?

    Ideally it should be done quickly.

    (2) It seems from the above message that the SPID was not killed properly.

    Is there any option available to force kill the process??

    Please let me know!

    Thanks.

  • August 31, 2009 at 6:22 pm

    #1047248

    Sourav,

    If the transaction was big then it will take some time because it has to rollback the transaction completely.

    It’s better to monitor if the IO and CPU is changing or not

    USE [master]

    GO

    SELECT spid,DB_NAME(dbid)DBNAME,cpu,physical_io,memusage FROM sysprocesses

    WHERE spid = 77

    It’s also better to check if it is blocked or blocking anybody else.

    If the server is PROD and if it’s not creating any blocking then better to leave it for some time alone. Or you can recycle your PROD box when you have proper downtime.

    The solution to clear it out is recycle SQL Server or Single User mode with rollback immediate; I don’t have any other solution in my mind as of now. 🙁

    Thanks,

    Raj

  • August 31, 2009 at 7:10 pm

    #1047260

    Thanks for your response Raj. But the mentioned situation happened on Production only!

    Therefore no question of "Recycling the SQL Service", as it will incur temp downtime during the business hrs.

    Any other solution?

    Regards

    Sourav

    Thanks.

  • August 31, 2009 at 7:23 pm

    #1047264

    Sourav (8/31/2009)

    ...Any other solution?

    No there is no other solution. In fact those previously mentioned are not "solutions" either.

    Killing a process is easy, but killing a DML transaction in progress is not, they must either be rolled-back or allowed to complete. The only other choice would be to corrupt your database and fortunately, SQL Server works real hard to keep you from doing that.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • August 31, 2009 at 7:26 pm

    #1047266

    It wouldn't change a thing by cycling the service - SQL Server has to rollback all of the transactions in the batch. If the job you killed is a large job - better go get some coffee, some donuts, fruit, candy, whatever else you like and prepare to wait.

    It is going to take however long it needs to take to rollback those transactions - and there really is nothing you can do except let it.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • August 31, 2009 at 7:27 pm

    #1047267

    Welcome, what is the output of the sysprcoesses for the SPID 77?

    IO, CPU changing? What’s the rollback percentage? What is the lastwaittype ?

    Thanks,

    Raj

  • August 31, 2009 at 7:27 pm

    #1047268

    rajdba (8/31/2009)

    ...The solution to clear it out is recycle SQL Server or Single User mode with rollback immediate;...

    No, these are no different the KILL-ing the process. ROLLBACK IMMEDIATE, simply starts the rollback immediately, just as the KILL does.

    Restarting your SQL Server will simply result in it starting the rollback after you restart.

    There is NO way around the rollback. And that's a GOOD thing.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • August 31, 2009 at 7:34 pm

    #1047269

    Sourav,

    As I stated before, if it’s a PROD box better to leave it like that still it doesn’t create a big issue.

    I agree with RBarry and Jeffrey .. u better have a break…

    Thanks,

    Raj

    RBarry,

    It happened some time before with me for dev box at that time after recycle the rollback was faster.

    At that time my dev box wasn’t responding for a huge rollback.

    Thanks,

    Raj

  • August 31, 2009 at 7:36 pm

    #1047271

    No yar, I can't do that even..as batch jobs are running on the server.. and running processes status are in "suspended".

    Thanks.

  • August 31, 2009 at 8:43 pm

    #1047288

    Sourav,

    No options as to my little knowledge 🙁

    Let me know the solution once done.

    Thanks,

    Raj

  • August 31, 2009 at 9:18 pm

    #1047292

    If you stop and start SQL I can almost guarantee your database will be corrupted. Been there done that. You will just have to wait or it to complete the rollback.

    CEWII

  • August 31, 2009 at 10:34 pm

    #1047303

    If you choose to kill a long running process, you can get an idea of how long it will take by running the KILL statement again. It will return a percentage completion message. (won't kill any faster though)

  • September 1, 2009 at 6:02 am

    #1047433

    Rollback is necessary for SQL Server to preserve the integrity of the database. You don't want it to just leave a bunch of half-saved records hanging out in a database do you? No. So, as Barry has already stated, by killing the spid, you've started the rollback process. It usually takes about as long as the initial process did, sometimes longer. You should try to determine what that process was doing. See if you can query sys.dm_exec_requests and combine that with sys.dm_exec_sql_text to see the query being run.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • September 1, 2009 at 6:08 am

    #1047440

    Elliott W (8/31/2009)

    If you stop and start SQL I can almost guarantee your database will be corrupted. Been there done that. You will just have to wait or it to complete the rollback.

    CEWII

    While I have seen this happen too, Eliott, I think that it is important for those following along at home to know that this is not the normal case. *Normally* SQL Server will recover this just fine.

    I've had to do this or worked with customers who had to do it literally thousands of times and I have only ever seen it fail a handful of those times (and yep, then it was corrupt, goto backup).

    So while I would not recommend it unless you absolutely have to do it that way, it should still be relatively safe. That's the miracle of the transaction log at work. 🙂

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • September 1, 2009 at 8:26 am

    #1047564

    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. I did it a couple times knowing it would happen so I could just go back to the backup and not wait.. I have never done it in 2005/2008. Either way, in the strongest terms possible, I recommend waiting for it to complete.

    CEWII

Viewing 15 posts - 1 through 15 (of 25 total)

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