Backup blocked by system SPID

Question

Backup blocked by system SPID

ReamerXXVI

Ten Centuries

Points: 1379
More actions
July 22, 2018 at 2:00 am

#373789

Hello,
Has anyone ever seen a full backup blocked by a system spid?
I have a nightly job that runs and backups all of the databases on a 2008 R2 instance, all backups succeed except one and when I check the job it is blocked by a system spid.
I did some digging and determined the lock info for the backup job, and it seems to be waiting on a file lock??
<Database name="XXX">
<Locks>
<Lock request_mode="S" request_status="GRANT" request_count="1" />
</Locks>
<Objects>
<Object name="(null)">
<Locks>
<Lock resource_type="DATABASE.BULKOP_BACKUP_DB" request_mode="U" request_status="GRANT" request_count="1" />
<Lock resource_type="DATABASE.BULKOP_BACKUP_LOG" request_mode="NULL" request_status="GRANT" request_count="1" />
<Lock resource_type="FILE" request_mode="X" request_status="WAIT" request_count="1" />
</Locks>
</Object>
</Objects>
</Database>
The only information I could find on the system spid that was holding the lock was that is was a checkpoint operation:
<command_type>CHECKPOINT</command_type>
If any one has ever seen this and has any advice or could just educate me on whats going here that would be greatly appreciated.
Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

I have to admit that I've never seen such a thing before. I'm certainly not an expert here but the only way that I could see a CHECKPOINT causing this is if it had a whole lot to do because of some monster code that had made a whole lot of changes OR something else was in the process of doing a backup or there were some type of system maintenance going on (dunno if something like a DBCC command would cause something like this)..

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ReamerXXVI Ten Centuries Points: 1379 More actions · Answer 2

Thanks for your reply Jeff. I'm no closer to getting to the bottom of this one but I've since discovered this particular database is configured for log shipping and transnational replication. I wonder of there is a conflict here some how, maybe the file lock wait is referring to the transaction log file?

There are no other backups running at the time and no other maintenance tasks so it's still a mystery, I appreciate your reply though, thanks.

When I get to the bottom of it I'll let you know 🙂

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

ReamerXXVI - Monday, July 23, 2018 8:00 PM
Thanks for your reply Jeff. I'm no closer to getting to the bottom of this one but I've since discovered this particular database is configured for log shipping and transnational replication. I wonder of there is a conflict here some how, maybe the file lock wait is referring to the transaction log file?
There are no other backups running at the time and no other maintenance tasks so it's still a mystery, I appreciate your reply though, thanks.
When I get to the bottom of it I'll let you know 🙂

I've not used log shipping but I'm thinking that's probably the cause of your backups exhibiting problems. Even full backups have a "touch" on the log file. I don't know how to prevent the problem because, like I said, I don't use log shipping nor any form of replication that uses the log file.

Hopefully, someone with more knowledge of the subject will confirm or deny that supposition on my part.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)