September 4, 2007 at 4:41 pm
I have several maintenance plans running in limbo and threatening my prod db's integrity. And they are the backups.
Came in today to an incredibly slow, constantly timing out server. Here's a little background: We back up (full) production (60gb) nightly at 0130, transaction logs at noon and run optimizations (reorg and integrity check on sys db's and production db) Sundays (no full backup Monday AM). Let's not talk about memory. It's SS2k 8.00.1007 (SP3) Std Ed running on Windows Server 2003. We can't upgrade to SP4 due to application issues. Finally, the db server is extremely low-powered for what it has to do. The Average Disk Queues would make any dba run the other way. Oh, and we're not a 24hr shop.
Based on what's running now, here's what happened:
9/1 0130: Backup failed, errorlog written
9/1 1200: Tranlog backup succeeded
9/2 0130: Backup... well, it's still in limbo as I write this. Process is runnable, but hasn't had activity since 1 second after it started. Errorlog exists, 0kb. No PhysicalIO is attributed to this process.
9/2 0200: Optimization job runs, and runs... is now in a KILLED/ROLLBACK state (don't ask) and appears to be very active.
9/3 1200: Tranlog backup also went into limbo, same as above.
9/4 1200: Tranlog does not kick off, presumably b/c it's still running..
9/4 1300: Everything runs extremely slow.
At this point, I'm unsure what to do. With anything I do, it seems I could end up with a corrupt database. Since the backups are at the heart of the problem, that's quite the risk. And, of course, things are expected to be back to normal at 8am.
September 4, 2007 at 6:08 pm
Well, for now I'm gonna let it sit, see if the rollback runs its course. Based on history, I figure it'll finish before 2am. Then I'll kill the backup processes and manually run a full backup..
..jeremy.
September 4, 2007 at 8:57 pm
Ran kill 63 with statusonly and discovered the rollback was 100% complete, meaning the process was orphaned. So, I killed the two backup processes, checked their rollback statuses, and restarted the SQL Server service. Everything is cleared out now and back to normal. The database has a normal status and I'm backing it up now.
Guess I'll be getting some sleep tonight after all! 😀
..jeremy.
September 5, 2007 at 8:41 am
Hi there, based on what you said had happened, I believe this is the reason:
Database locking and blocking contention.
The optimization jobs lock lots of data and block other transactions from happening, including simple selects, updates, inserts, deletes, database backups, etc...
SQL Server does appear to be smart enough on backups, to delay the backup if either another backup is running or pending, or if something like optimizations are running, which is why they went into "limbo."
I will warn you that the optimization task can block your user applications depending on table size and activity. Generally I find that if a table is less than 1,000,000 rows, you can get away with optimization/index defragmentation most of the time. After that it can take 1 minute or more to optimize on a high powered machine. In our office, we avoid the optimization job and use Idera's diagnostic manager to determine when we need to do index defragmentation. On the larger tables 2,000,000+ rows we will do it during a scheduled downtime, where it is much faster and does not impact customers.
From the point of you under-powered machine, my guess is that your database server may be acting as a bottleneck for whatever your company does. The only thing I will point out is: slow application = no customers!
Good Luck,
Brian
September 5, 2007 at 4:11 pm
Thanks Brian. I'll be reviewing the backup and optimization job histories to see if I need to make any adjustments. Sundays have always been a zero-activity day for us, but recently we added a small batch update coming from another db server that runs 24/7. I need to review the impact it is having.
I'll also be looking for any other jobs/processes that might have caused this.
Oh, in other news, my budget for two new high powered db servers was approved today. 😀
..jeremy.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply