SQL Server Performance Forum – Threads Archive
database recoverWe have a fairly large database with a couple of big tables in them (80M rows). We made the mistake of using a guid to join these and decided to swap to an integer. The process to do this was running for about 6 day when the trans log filled and the server sell over and windows rebooted itself. Now we have a database that is in recovery mode. Is there any way to speed this up or cancell it and with what effect? What would happen if I restore the backup from last night and restart the db. Would it roll back for a few days or continue to try and update the table? Thanks, Tim..
Do you want the bad news, or the worse news? Any database you restore that was started after you issued the update to the table structure will continue to try to finish it, or will roll the whole thing back. Recovery is trying to do the same thing and will likely have the same issue of a log filling up. You may be able to check the sql ‘error’ log to see if its marking any rollforward or rollbacks. Is your data fairly static? How much would be lost if you restored a database that went back to just before the change was initiated? If its not too much data then you may want to consider it. As to your column datatype change. There are a couple of steps you can do.
1) Create a new table with the column structure you want
2) in batches, insert rows into the new table (by doing it in batches, you can issue commit statements in between and manage your log size)
3) drop the old table
4) rename your new table to the old table name, and for good measure, recompile your sprocs etc that have it. chris
Also as a tip, always try to execute such huge operations in batches. Gaurav
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard