August 5, 2009 at 9:55 am
I have a sql2000 database, about 80GB, simple recovery model. It basically consists of one table with millions of rows. Users needed a new column for reporting. I added the column and was updating it. I added a datetime column and was populating it from varchar date & time columns already in the table. I was running the update query in query analyzer from my client for almost 23 hours and my client crashed. Looked at the sql server and the query was still running?? I tried to kill it, thinking I should be smarter and update this thing in smaller chunks. Now the query is in killed/rollback state. I'd prefer not to wait for the whole thing to rollback. I can restore the DB and get everything back. I just want the query to die and go away so the table will free up and I and the users can continue. Is there any way I can force this process in killed/rollback to go away? Can I set the mode or status of the DB in sysdatabases? Any ideas besides waiting it out?
Thanks all.
August 5, 2009 at 11:52 am
Wait. Rollback has to complete. You can restart SQL, but then the rollback will still have to complete and the entire database will be unavailable while that happens.
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
August 5, 2009 at 4:09 pm
I agree with Gail, wait. It's going to rollback one way or another. If you restart SQL Server the rollback process starts under a system SPID and you can't use KILL [SPID] WITH STATUSONLY to monitor progress. You would have to use the application log on the server to determine how much time remains in the rollback process.
On a side note, I have see SPID's get orphaned when a client crashes and the SPID is killed. It shows rollback as 100% complete but the SPID remains in a KILLED/ROLLBACK state. The only way I have found to clear this is to restart SQL Server. However, it's only a nuisance and does not cause any system issues.
August 5, 2009 at 4:11 pm
I have had some cases where stopping the SQL server when this is occuring trashes the database.. So I'd just wait for it to finish, it sucks but it is the way it is..
CEWII
August 6, 2009 at 9:05 am
Thanks all. I wasn't about to stop the SQL Server. I knew that it would recover on the way up and then ALL the databases on this machine would be offline until its done. I was just hoping there was some way to mark the DB as suspect or something and then restore it. Moot point, the rollback finished last night. I'll have to update the column in small pieces. Then I still have to index the new column so it can be used!
December 3, 2009 at 6:32 am
Hi, everybody...
I've got the same problem, but it's a little worse...
I've used the KILL SPID command but when I try to use KILL SPID WITH STATUSONLY to see the progress it replies:
SPID 67: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
The problem is that it hasn't changed for 24 hours (and counting...).
Does it mean that somehow the rollback process have crashed? Is there something else I could do?
Besides that, I tried to check how much time will it take with the following SQL statement:
select session_id, start_time, status, command, wait_time
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) t
where session_id <> @@SPID
The output was this:
session_id start_time status command wait_time
---------- ----------------------- ------------------------------ ---------------- -----------
67 2009-09-26 12:00:48.447 running KILLED/ROLLBACK 1574522391
Is the wait_time column in miliseconds? ('cause if the answer is YES, it means that I have to wait nearly 18 days for the rollback to complete!!! is that correct?)
Please, any help will be appreciated...
Thanks. Rgds,
Cristiano
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply