March 18, 2013 at 10:04 am
All my differential backups are being blocked by a SPID which has a command of CHECKPOINT.
Is this something to be concerned about ? If so how would one go about resolving it?
Many Thanks
March 19, 2013 at 2:48 am
Anyone have any insight into this?
Blocked process:Microsoft SQL Server Management Studio
Process ID:88
Database:MyDB
Host:XXXX
User:XXXX
Start time:12:00 PM
SQL fragment:BACKUP DATABASE [MyDB] TO DISK = N'D:\MyDB_backup_2013_03_16_120003_1030995.dfz' WITH DIFFERENTIAL , NOFORMAT, NOINIT, NAME = N'MyDB_backup_2013_03_16_120003_1010993', SKIP, REWIND, NOUNLOAD, STATS = 10
Blocking process:Unknown ------This is the spid 13 with command = checkpoint
Process ID:13
Database:
Host:
User:Unknown
Start time:1:47 AM
SQL fragment:Unknown
March 19, 2013 at 3:29 am
Is there enough memory in your server?
March 19, 2013 at 3:38 am
The checkpoint process is what writes all the dirty pages to disk.This is usually a fairly quick event. If it's hanging and running for a really long time, I guess the next question is, is the session that's running it a user or a system session? If it's a user session, it might just be an open transaction that wasn't committed. Try committing it, rolling it back or killing it. If it's a system session, yeah, you might be having troubles.
"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
March 19, 2013 at 4:33 am
salliven (3/19/2013)
Is there enough memory in your server?
18 GB on server, I think its enough
March 19, 2013 at 4:44 am
Grant Fritchey (3/19/2013)
The checkpoint process is what writes all the dirty pages to disk.This is usually a fairly quick event. If it's hanging and running for a really long time, I guess the next question is, is the session that's running it a user or a system session? If it's a user session, it might just be an open transaction that wasn't committed. Try committing it, rolling it back or killing it. If it's a system session, yeah, you might be having troubles.
The session is a system session.
The diff backup does happen it just gets blocked for less then a second is this okay?
March 19, 2013 at 5:03 am
bugg (3/19/2013)
Grant Fritchey (3/19/2013)
The checkpoint process is what writes all the dirty pages to disk.This is usually a fairly quick event. If it's hanging and running for a really long time, I guess the next question is, is the session that's running it a user or a system session? If it's a user session, it might just be an open transaction that wasn't committed. Try committing it, rolling it back or killing it. If it's a system session, yeah, you might be having troubles.The session is a system session.
The diff backup does happen it just gets blocked for less then a second is this okay?
Oh, good gosh yeah. The way you were putting it I thought it was completely blocked. Yes, it's normal that backups would have to wait for the checkpoint process to complete. 1-2 seconds on a block is not that big a deal when talking about something like backups.
"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
March 19, 2013 at 5:22 am
Grant Fritchey (3/19/2013)
bugg (3/19/2013)
Grant Fritchey (3/19/2013)
The checkpoint process is what writes all the dirty pages to disk.This is usually a fairly quick event. If it's hanging and running for a really long time, I guess the next question is, is the session that's running it a user or a system session? If it's a user session, it might just be an open transaction that wasn't committed. Try committing it, rolling it back or killing it. If it's a system session, yeah, you might be having troubles.The session is a system session.
The diff backup does happen it just gets blocked for less then a second is this okay?
Oh, good gosh yeah. The way you were putting it I thought it was completely blocked. Yes, it's normal that backups would have to wait for the checkpoint process to complete. 1-2 seconds on a block is not that big a deal when talking about something like backups.
😀 cool, thought it might be okay 🙂
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply