Runaway CHECKPOINT session on SQL-2008 db-engine instance

  • I have a CHECKPOINT SPID that has been running on a SQL-2008 instance for the last 16 days.

    Here are the details from sys.sysprocesses (the spid is not blocked):

    Waittype: 0x0081

    Waittime: 4143683

    Lastwaittype: CHECKPOINT_QUEUE

    dbid: 10 (this is my user db)

    cpu: 51218

    physical_io: 8924

    memusage: 0

    login_time: 2009-04-14 17:05:30.420

    last_batch: 2009-04-14 17:05:30.420

    ecid: 0

    open_tran: 0

    status: background

    cmd: CHECKPOINT

    I can't kill this SPID (it's a system SPID), and it is using up resources on the user database.

    Meanwhile, I'm not sure why it is constantly running. The cpu/phys. io values are not changing.

    The last batch was issued at the same time as the login_time!

    I know what CHECKPOINT is supposed to do, but it seems to be hung.

    Is something wrong with this instance/db?

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Is this a virtual server?

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • David Benoit (4/30/2009)


    Is this a virtual server?

    No, it's a physical server.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • I have seen this too but only on a virtual server. I moved the database to a similarly configured physical and didn't have the issue.

    I have a case with MS that I will be working tomorrow on an issue with the virtual server and I will be addressing this issue so, I will certainly share with you what I find.

    Until then though, I am clueless. 🙁 Haven't researched it much yet but probably should.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • David Benoit (4/30/2009)


    I have seen this too but only on a virtual server. I moved the database to a similarly configured physical and didn't have the issue.

    I have a case with MS that I will be working tomorrow on an issue with the virtual server and I will be addressing this issue so, I will certainly share with you what I find.

    Until then though, I am clueless. 🙁 Haven't researched it much yet but probably should.

    Thank you for the input, is yours a SQL 2008 instance as well?

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Yup. There are other issues with this being virtual and this is just one of several warning signs which concerns me.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • David Benoit (4/30/2009)


    Yup. There are other issues with this being virtual and this is just one of several warning signs which concerns me.

    Slightly off topic, but what other issues have you had with virtualization? We are also thinking of virtualizing our SQL Server environments, but are concerned about potential issues with performance.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • What are you seeing when you look at the SQLServer:BufferManager Checkpoint pages / sec counter? On the server that I am looking at it is almost continuously writing pages (very bad) whereas on other boxes that I looked at (all 2005) it will write massive amounts and then go silent (expected behavior).

    Curious what you are seeing. Thanks in advance.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • Marios Philippopoulos (4/30/2009)


    David Benoit (4/30/2009)


    Yup. There are other issues with this being virtual and this is just one of several warning signs which concerns me.

    Slightly off topic, but what other issues have you had with virtualization? We are also thinking of virtualizing our SQL Server environments, but are concerned about potential issues with performance.

    I'll work on summarizing this in a day or so and share on here then.

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • ...and an interesting post on this can be found HERE

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • David Benoit (4/30/2009)


    ...and an interesting post on this can be found HERE

    Thanks for all the info!

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Don't wait around too long.

    IF a checkpoint is stuck that means you can't get a backup. can't run a dbcc, etc.

    I've had this happen once, tried everything and eventually called MS support. Essentially all you can do is restart the instance. There's a DBCC command that SQL runs when you restart an instance dbcc recoverdb maybe... you could try running that right and not restart. It didn't work for me, but maybe it's not even the same issue.

    I had to restart and when the instance starts up, the DB where the checkpoint was stuck will go into recovery mode via dbcc recoverdb and roll forward the transactions and be just fine.

    ~BOT

  • SQLBOT (4/30/2009)


    Don't wait around too long.

    IF a checkpoint is stuck that means you can't get a backup. can't run a dbcc, etc.

    I've had this happen once, tried everything and eventually called MS support. Essentially all you can do is restart the instance. There's a DBCC command that SQL runs when you restart an instance dbcc recoverdb maybe... you could try running that right and not restart. It didn't work for me, but maybe it's not even the same issue.

    I had to restart and when the instance starts up, the DB where the checkpoint was stuck will go into recovery mode via dbcc recoverdb and roll forward the transactions and be just fine.

    ~BOT

    Fortunately, this is a dev server so it is no trouble to restart the instance.

    My concern is, what if this happens in production and is a re-curring issue. I also wonder whether this is caused by a problem in the disk subsystem.

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

  • Are you suffering from IO latency? Also curious what your CPU looks like. Can you provide some of that information?

    David

    @SQLTentmaker

    “He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

  • David Benoit (5/1/2009)


    Are you suffering from IO latency? Also curious what your CPU looks like. Can you provide some of that information?

    Not sure if it is an IO problem.

    CPU looks under-utilized: 5%.

    I will try to collect some of that info and post it.

    Thanks

    __________________________________________________________________________________
    SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
    Persisting SQL Server Index-Usage Statistics with MERGE[/url]
    Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 15 posts - 1 through 15 (of 27 total)

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