March 1, 2005 at 11:17 am
We tried to delete a HUGE table, but decided to stop it before it was done. Now SQL Server cannot be shut off. We can't kill the process and rebooting or shutting down the server doesn't help. SQL Server just starts up again and we can't access it. Suggestions? Thanks.
March 1, 2005 at 12:32 pm
How long did you let the delete run before stopping it? When you stop an action like that, SQL has to roll back the changes, and that will take about as long as it ran to begin with. When you say "we can't access it", what exactly are you referring to? SQL? the database? the table? If you can get to SQL, you should be able to run sp_who2 to identify the connection that is rolling back, and then kill the connection (assuming that you don't care about the data in that table/database). Another possibility would be to stop SQL (go to services and stop the MSSQLServer service), delete the database files for that database, then restart SQL. Then you can restore the database from backup (assuming some data loss is acceptable depending on your backup strategy). Otherwise, just wait for the rollback to complete.
Steve
March 1, 2005 at 2:40 pm
Look into the event log on the server, there will be markers "DB blah Recovery ?% ..."
That will give you the progress of the backout that Hoo-t was talking about.
The DB is unusable until that completes, you cannot even delete it.
KlK
March 1, 2005 at 4:00 pm
Great answers! Problem solved. Thanks!
March 2, 2005 at 10:58 am
Moral of the story - If you need to delete a large table, scary as is it, truncate the table first and then drop it. Truncate is non-logged.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply