KILL Spid in ROLLBACK 1

[hmckillop]

I have a process which is rolling back a transaction, (I have already killed it due to a latch timeout), but now when I try to kill it again it says

Transaction Rollback in process; Estimated rollback completion 5%; Estimated time for completion 138464 seconds.

This was 11 hours ago.
I have a database backup of before I tried the operation, I really need to stop this. (I am not able to restart the box due to other complications).
Anyone any ideas (I have already tried):
KILL SPID in SQL
KILL Process at NT
Stop SQL Service
Kill SQL Service
Kill SQL Service with 2 3rd Party Tools.

Any other suggestions are appreciated.

Thanks



"I'm living so far beyond my income that we may almost be said to be living apart

 

[JayKusch]

let see if we can shake it loose ...

Go ahead and stop the SQL Server Service as well as the SQL Server Agent service either thru the SQL Server Manager in the system tray, or by stopping the service in the Service application or by issuing a NET STOP at the command line.

Once the services have been stopped, go the the folder(s)where the MDF and LDF reside. Rename the MDF/LDF to something like MDF2/LDF2 respectively.

Now restart the SQL Service, get into Enterprise Manager(EM).

We should see that the rouge DB is in some state of disarray.

Report back at what we currnetly have and we will hammer on it some more!

Thanks

J. Kusch

 

[hmckillop]

It wont let me stop the service, either through enterprise manager, or through command line.
Havent been able to continue with anything else due to above.

Thanks for prompt response.


"I'm living so far beyond my income that we may almost be said to be living apart

 

[JayKusch]

OK ... lets kick it some more!

Open Query Analyzer and enter the following commands:

USE master
GO

EXEC sp_configure 'allow updates', 1
GO

RECONFIGURE WITH OVERRIDE
GO

NOW ...

EXEC sp_resetstatus 'MyDatabase' -- Put your DB name in place
GO

Ater the procedure is run, immediately disable updates to the system tables:

EXEC sp_configure 'allow updates', 0
GO

RECONFIGURE WITH OVERRIDE
GO

Stop and Restart ALL SQL Server Services ... If you can.

Let see if that shakes it loose ... Still a couple more tricks I will send if that does not do it.


Thanks

J. Kusch

 

[JayKusch]

As a last resort ... we will "try to put it in DEATH MODE" - lol. This mode is called "Emergency Mode" and you can try to export as much data as you can but all-in-all you are going to loose this DB. Thank goodness you HAVE a backup to restore from!!!

OK here we go ... In QA we enter the following code ...

USE master
GO
sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO 
UPDATE master..sysdatabases
SET status = 32768
WHERE name = 'MyDatabase' -- Enter your DB name here again

NOW ... lets "try" to Stop and Restart ALL SQL Server Services.

If we are able to get the DB in Emergency Mode, I believe you can the try to DELETE the DB or try to detach it.

Let me know what happens!

Good Luck!


Thanks

J. Kusch

 

[JayKusch]

Now if that fails ... you will have to wait the "38 HOURS" that it will take to roll back based on the number of seconds the error message returned ...

138464 seconds / 60 = 2307.73 MINUTES ... then ...

2307.37 minutes / 60 = 38.46 HOURS!!! ... then ...

38.46 = 1 Day - 14 hours - 46 minutes.

In otherwords ... sometime tomorrow it will roll back.

You can check the status of how far the rollback has progressed by drilling down into Enterprise Manager(EM) and go to Management..SQL Server Logs and then right-click on the "Current" log and do a refresh.

The percentage of completion for the rollback for that DB will be shown.



Thanks

J. Kusch

 

[hmckillop]

Thanks a million for your help, I have left work for the night, but will be back in tomorrow.
Extremely useful and I am sure one of them will work.

Thanks again

"I'm living so far beyond my income that we may almost be said to be living apart