February 24, 2010 at 4:12 pm
We are noticing on one customer's database the log backup occasionally blocks spid 10 which is their checkpoint process. They are concerned that this might be an issue. Can anyone tell me why this might be happening and if it is, in fact, an issue?
They are on SQL Server 2005 SP2.
Thanks,
John
February 25, 2010 at 12:20 am
That's normal, the checkpoint and the backup log both have to write to the transaction log, hence one will block the other.
If it's happening often, check the duration of checkpoint, check that it isn't hitting IO bottlenecks. Check the duration and frequency of the log backups, check that they aren't hitting IO bottlenecks. Unless the system is hectically busy the blocking shouldn't be often or excessive.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 25, 2010 at 9:36 am
Thank you
April 19, 2010 at 1:13 pm
I just happened to face this issue. I have been facing cpu increase, blocks etc and nailed down to Checkpoint blocking Log backups. MS supplied a job that collects blocking information, and I see Checkpoint is the blocker and log backup is blocked and that creates a series of locks and blocks. MS support doesnt say an accurate reason to this issue. The issue seems to start happening when I have more than 1500 TPS on the database. I run log backup every 5 minutes. This has turned out to be a very big issue, and the senior management sees this as a limitation to SQL Server.:crying:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply