April 30, 2009 at 2:34 pm
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]
April 30, 2009 at 2:51 pm
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
April 30, 2009 at 2:57 pm
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]
April 30, 2009 at 3:00 pm
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
April 30, 2009 at 3:01 pm
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]
April 30, 2009 at 3:05 pm
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
April 30, 2009 at 3:10 pm
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]
April 30, 2009 at 3:23 pm
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
April 30, 2009 at 3:24 pm
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
April 30, 2009 at 3:36 pm
...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
April 30, 2009 at 3:40 pm
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]
April 30, 2009 at 9:56 pm
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
Craig Outcalt
May 1, 2009 at 12:20 pm
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]
May 1, 2009 at 1:26 pm
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
May 1, 2009 at 2:13 pm
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