killed/rollback database backup

Last Post 31 Aug 2010 10:28 AM by gunneyk. 7 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Markus_SQL
New Member
New Member

--
30 Aug 2010 08:38 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.
russellb
New Member
New Member

--
30 Aug 2010 09:33 AM
you're best off waiting it out. if you bounce the server, it still has to rollback.

i'm more interested in why it was taking so long to backup. did you see any blocking? what did disk activity look like? were you backing up locally or over the network? if over the network, you would've been better served to sever the connection. then the backup would just fail and you don't need to wait around for the rollback.

what recover model is your db? how often do you backup?

how many processors?

how is server performance now while rollback is occurring? long disk waits?
Markus_SQL
New Member
New Member

--
30 Aug 2010 10:01 AM
This is a quad 2.6 64 bit WIn2008 R2 Cluster. So far this is the only database on it.  CPU useage was about 10%. 12 gig of memory in the server and SQL Server is using 4 gig is all.  I don't understand why the backup was hung either.  I am afraid this rollback will take days since the backup was going for 4 days.  The database is in SIMPLE mode and I do a daily full backup via data-domain to another server nightly.  It typically runs 45 minutes like clockwork which is even more puzzling as the day before backup was 135 gig.  The application folks have stopped their connects so no new data is going into the db and there is nothing else running in SQL Server but they cannot wait forever.  I am caught between a rock and a hard place.
rm
New Member
New Member

--
30 Aug 2010 10:39 AM
How did you do backup? As maintenance job? Canceling backup statement shouldn't cause long rollback, and you can backup log while backup db is running on sql2k8 by the way.
russellb
New Member
New Member

--
30 Aug 2010 10:46 AM
what does it show if u KILL @spid WITH STATUSONLY
Markus_SQL
New Member
New Member

--
30 Aug 2010 12:00 PM
It says the db is 3% recovered and like 4.8 mill seconds remaining.  The seconds remaining keeps going up!

The db backup is just a script in a job that works for hundreds of dbs here.   I have a call into MSFT support right now as to what our options are.
Markus_SQL
New Member
New Member

--
31 Aug 2010 05:39 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!
gunneyk
New Member
New Member

--
31 Aug 2010 10:28 AM
With that kind of action the table is a very good candidate for partitioning and you need a well configured array for the tran logs to keep up with it. Bakcing up every 30 minutes is still probably way too infrequent and you might want to consider ever 5 or so.
You are not authorized to post a reply.

Acceptable Use Policy