August 30, 2010 at 9:30 am
We have a 180gig db db backup that started on Thursday (4 days now) and was still running today.. the transaction log was up to 230gig so I killed the db backup. It is in Killed/Rollback status and has been for 1 hour. Not knowing how long it will take I was thinking of bouncing SQL Server but am unsure if that will put the db into recovery or suspect mode. Any ideas?
Win2008 Cluster running SQL 2008 Std SP1 CU6.
August 30, 2010 at 10:32 am
My 2 cents. It will not put database into SUSPECT mode.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 30, 2010 at 10:35 am
OK, But since it is currently in a rollback state if I restart SQL Server I have a feeling the DB will still be in some type of recovery state and not empty the transaction log that is 230 gig in size right now until it rolls back the backup..... very odd situation.
August 31, 2010 at 7:33 am
The DB will not be in recovery state even though if you restart the services.
August 31, 2010 at 7:38 am
In talking with Microsoft if you run a kill spid with statusonly and the io and cpu are not progressing the process is dead so it is OK to restart. We restarted SQL Server and after about 5 minutes the db was available.
In looking more into this problem they are pumping 200,000 rows a minute into one table, running updates and deletes against it. In four days that table went from 160,000,000 rows to 2.8 billion rows. The backup cannot seem to keep up with it and there was alot of blocking/locking in the app. We shutdown the app, truncated the table and the app guy is talking with the software vendor as to why they are inserting so many rows into this one table.
We also put the db into FULL mode and are doing trans backups every 30 min now and the backups are working. I think we have a handle on what is wrong.
Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply