Checkpoint Blocking Log Backups

Question

Checkpoint Blocking Log Backups

rajankjohn

SSCertifiable

Points: 5061
More actions
April 22, 2010 at 10:29 am

#156896

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. Is there a limit of tps that SQL Server 2005 can support (is 1.5k too high?) Do we know whether 2k8 could be better on high transaction support? Is there a better way with which transaction log backup can be done without going through SQL job (kind of snashot)?

Viewing 8 posts - 1 through 7 (of 7 total)

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

Steve Jones - SSC Editor SSC Guru Points: 735658 More actions · Answer 1

Likely if you have a limitation, it's not SQL Server. It could be your hardware being unable to keep up with the load. SQL Server is *always* limited by the hardware in some way. There are definitely people using more than 1500tps sustained, but you need to be sure that hardware supports that.

rajankjohn SSCertifiable Points: 5061 More actions · Answer 2

Thanks Steve! But, the storage vendor (they are the #1 in the world to say) says they didnt see any queing or blocking on the disk side; neither did we see anything in the perfmon logs. Can we have something else internally in SQL Server that can cause Checkpoint blocking log backups? Also going back to another question I had in the original post, is there a way better to take the log backup that through the SQLAgent job?

Steve Jones - SSC Editor SSC Guru Points: 735658 More actions · Answer 3

I don't think so, but I'll ask around and see. There has to be something that's sticking here. If it's not storage, I'm at a loss.

Chirag Roy SSChasing Mays Points: 622 More actions · Answer 4

Hi Rajan

A manual Checkpoint takes precedence over a queued system checkpoint. Can you run a manual checkpoint in the context of the affected database and see if you continue to see the locking behaviour?

Regards

Chirag

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 5

You can see checkpoint info. Run this under the affected db.

SELECT

DB_NAME() AS DatabaseName

, [Current LSN]

, [Previous LSN]

, Operation

, [Checkpoint Begin]

, [Checkpoint End]

, [Dirty Pages]

FROM fn_dblog(NULL, NULL)

WHERE operation IN ( 'LOP_BEGIN_CKPT', 'LOP_END_CKPT')

HTH!

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 6

Chirag Roy (4/22/2010)
Can you run a manual checkpoint in the context of the affected database and see if you continue to see the locking behaviour?

just to see behaviour, i dont think manual call would be good though i am not good in check point area but still before going for manula move , we can investigate in other ways also. see the link http://blogs.msdn.com/joaol/archive/2008/11/20/sql-server-checkpoint-problems.aspx

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Chirag Roy SSChasing Mays Points: 622 More actions · Answer 7

Hi Bhuvnesh

Thanks for posting the link, exactly the behaviour that could be affecting in this case. Since the manual checkpoint takes precedence over a system one, if issuing a manual checkpoint does clear the log file that would prove there is an issue with checkpoint throttling, hence the suggestion!

Regards

Chirag