Stopping a transaction rollback

[Zukkster]

I stupidly ran a big update query on a table over the weekend and when I got in on Monday it was still running and all the space had been used because it had created a huge transaction log and the tempdb was massive.

Even more stupidly I stopped the query thinking that it wasn't working due to space issues and I needed to make some changes. I now realise it's going to spend almost as much time rolling back as it did running the query.

Then I made my third fatal mistake, everything locked up and I rebooted.

The database is one that I set up on hardware dedicated to me to do an analysis project (I'm and analyst not an IT guy) so it's proccessing big chunks of data. I'm happy to drop and recreate it if neccesary, the data isn't lost it's in text files ... I just don't want to wait 80 hours for the database to fix itself.

Do I have any options to kill the database or work out how long is let until I can use it again?

The current status in the sysdatabases table is 4194304 = autoshrink.

Thanks

 

[JayKusch]

Since we are unsure as to "when" during the weekend the update hung due to lack of disc space, you will not be able to guess on how long the rollback occured. You "may" be able to see if there are any alerts in the SQL Server error logs in Enterprise Manager. They are found under Management..SQL Server Error Logs. If they do show up, you can figure it will take "at least" as long to recover as it did from the time you started the update and the first occurence of an error in the log. In fact, you could probably take another 20% longer based on restore I have done in the past.

As for the DB, you could try to detach it, rename the LDF and try to reattach it. It may bark about the missing LDF but it hopefully will want to create a new one for you. If it goes as hoped, you may be able to get that DB back up but be sure to check if it looks sound.

Worst case is that you delete it or detach it and delete the MDF and LDF files.

Good Luck!

Thanks

J. Kusch