May 1, 2009 at 2:15 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
I can actually do backups of the db on which the CHECKPOINT is running and run DBCC commands.
It seems the CHECKPOINT session is just hung and is not doing anything.
__________________________________________________________________________________
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 2:21 pm
Marios Philippopoulos (5/1/2009)
I can actually do backups of the db on which the CHECKPOINT is running and run DBCC commands.
It seems the CHECKPOINT session is just hung and is not doing anything.
Have you looked at the performance counter SQLServer:Buffer Manager Checkpoint pages / sec? My guess is that you will see it writing pages.
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:22 pm
David Benoit (5/1/2009)
Marios Philippopoulos (5/1/2009)
I can actually do backups of the db on which the CHECKPOINT is running and run DBCC commands.
It seems the CHECKPOINT session is just hung and is not doing anything.
Have you looked at the performance counter SQLServer:Buffer Manager Checkpoint pages / sec? My guess is that you will see it writing pages.
Good idea, I will try that and send an update with the results.
__________________________________________________________________________________
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 2:50 pm
I'm getting 0 Checkpoint pages/sec
__________________________________________________________________________________
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 2:57 pm
Marios Philippopoulos (5/1/2009)
I'm getting 0 Checkpoint pages/sec
You should see that counter reading 0 for the most part but then periodically it should shoot way up as the checkpoint process actually runs. So, it sounds like it is working properly.
The process itself is not bad and when looking at the spid it will show the database it is either checkpointing or has completed checkpointing. Just make sure that it is not continually processing on that database. You should be able to use the database that it is showing and issue a checkpoint statement yourself and have it complete fairly quick. The counter referenced earlier should shoot up at that point as well.
Hope this is helpful.
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 3:04 pm
But is it normal to have this SPID continuously running for so many days?
I actually issued the CHECKPOINT command and did see a spike in the perfmon counter, after which it has returned to 0 values. I will keep the counter running for a while.
However, the session keeps running. I wonder if it will terminate at some point.
__________________________________________________________________________________
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 3:26 pm
I should also mention that there is no other session currently running on the user database except for CHECKPOINT, so there is no user activity, neither has there been any for at least the last 24 hrs.
__________________________________________________________________________________
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 3:27 pm
On the two 2008 instances that I have I show the checkpoint process there all the time on both. The one is working fine as I mentioned in my previous post. The other one on the VM server is actually running continually on one of our databases (very bad). So, the behavior that you are mentioning sounds pretty accurate. My guess is that the checkpoint process you see is either spid 16 or 17.
I may be wrong on this and haven't investigated as to whether the process should be there all the time or not so, it would still be worth investigating. However, the behavior you are seeing is good.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 4, 2009 at 12:22 pm
Sorry,
I fell asleep at the wheel on this thread.
The checkpoint process should always be running somewhere, or in the state 'checkpoint wait'
There are many activities that automatically initiate a checkpoint
http://msdn.microsoft.com/en-us/library/ms188748(SQL.90).aspx
The checkpoint process /typically/ sits in master, but if your recovery interval is set too a low number it will auto checkpoint all the time.
http://msdn.microsoft.com/en-us/library/ms191154(SQL.90).aspx
Craig Outcalt
May 5, 2009 at 9:57 am
SQLBOT (5/4/2009)
Sorry,I fell asleep at the wheel on this thread.
The checkpoint process should always be running somewhere, or in the state 'checkpoint wait'
There are many activities that automatically initiate a checkpoint
http://msdn.microsoft.com/en-us/library/ms188748(SQL.90).aspx
The checkpoint process /typically/ sits in master, but if your recovery interval is set too a low number it will auto checkpoint all the time.
http://msdn.microsoft.com/en-us/library/ms191154(SQL.90).aspx
I did an instance restart over the weekend and that SPID disappeared.
It has now reappeared, but is now running on another user database; last wait type is still CHECKPOINT_QUEUE.
Should I expect to see multiple CHECKPOINT SPIDs, one for each database on the instance?
__________________________________________________________________________________
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 5, 2009 at 10:07 am
Nope, should be just one unless you execute one specifically 🙂 . The checkpoint should occur pretty quickly as noted in the one perfmon counter earlier discussed. If you run a couple of sp_who2's you should see it on different db's and / or hovering on master.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 5, 2009 at 10:57 am
David Benoit (5/5/2009)
Nope, should be just one unless you execute one specifically 🙂 . The checkpoint should occur pretty quickly as noted in the one perfmon counter earlier discussed. If you run a couple of sp_who2's you should see it on different db's and / or hovering on master.
Hmm, now it is on msdb, earlier it was on a user db.
I see your point, 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]
June 12, 2009 at 9:04 am
We are running into this same problem. The problem happens when we run out of log space on a DB. Then the checkpoint is stuck on the DB. This is on our test system running SQL 2008 SP1 Standard. The code is from a vender and it is in an exe so w/o running profiler I can't see if they are issuing a checkpoint or what.
I was able to drop the DB and do a restore which would be better than a SQL restart in a production environment.
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply