July 22, 2018 at 2:00 am
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.
July 22, 2018 at 7:35 am
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
July 23, 2018 at 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 🙂
July 23, 2018 at 8:46 pm
ReamerXXVI - Monday, July 23, 2018 8:00 PMThanks 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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply