April 1, 2014 at 2:28 am
Got a bit of an odd one here. A client has SQL 2008 R2 Express installed to support our product--since this doesn't have the SQL Server Agent, we use a simple SQL script that runs via SQLCMD to back up the databases. However, every now and again the backup operation starts failing, saying that the operation is serialized and can't run while a previous one is still going. If I use Activity Monitor I can see that there are BACKUP DATABASE operations for the databases being backed up which are in state SUSPENDED and mostly in wait type WRITELOG (there's one that's in state LOGBUFFER).
Once it gets into this state I haven't found a way to fix it short of rebooting the entire server, which is obviously not ideal. Anyone seen this before and got any suggestions?
April 1, 2014 at 4:34 am
Those are waits related primarily to disk. Are you backing up across the network? Otherwise, I'd focus right on the disk to see what issues there might be there.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 1, 2014 at 4:38 am
I haven't seen it, but:
- How long does a backup take?
- How often is the job being sent to start a backup?
- Where is the backup going to?
I feel like those 3 things will sort it out.
April 1, 2014 at 5:09 am
It's backing up to a local disk on the machine, and there never seem to be any other I/O errors--it's just while backing up. I've just learned it's probably a moot point because the client is getting a new server at the end of the month anyway, though! 🙂
April 1, 2014 at 10:05 am
Are you running the backups in parallel ?
--
SQLBuddy
April 2, 2014 at 3:04 am
sqlbuddy123 (4/1/2014)
Are you running the backups in parallel ?--
SQLBuddy
No, why would I be doing that? It's a strictly sequential operation.
April 3, 2014 at 8:22 am
paul.knibbs (4/2/2014)
sqlbuddy123 (4/1/2014)
Are you running the backups in parallel ?--
SQLBuddy
No, why would I be doing that? It's a strictly sequential operation.
Because of this ..
.. saying that the operation is serialized and can't run while a previous one is still going.
Also check if there were any backups kicked off by other source ..
--
SQLBuddy
April 3, 2014 at 8:34 am
No, no backups from another source. What seems to be happening is that a previous backup simply never finishes for some reason (and there are 24 hours between backups, so we're talking a heck of a long time here), breaking all subsequent backups and causing the issue.
Anyway, as I said, it's a moot point because they're moving to a new server before too long.
April 3, 2014 at 9:06 am
paul.knibbs (4/3/2014)
No, no backups from another source. What seems to be happening is that a previous backup simply never finishes for some reason (and there are 24 hours between backups, so we're talking a heck of a long time here), breaking all subsequent backups and causing the issue.Anyway, as I said, it's a moot point because they're moving to a new server before too long.
Sounds good, Paul. Yeah, that long running backup could be the culprit.
--
SQLBuddy
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply