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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy