November 24, 2009 at 5:53 am
I have a server that has its databases refreshed daily. its a sandbox server so it doesnt matter what happens to the data on a dailybasis.
I couldnt restore todays backups as a system spid could not be killed. It was Spid 10, the checkpoint spid.
I performed a SHUTDOWN, followed by a restart of the sql server service and my restore could be kicked off.
Im curious though:
1. what can cause a checkpoint spid to hang
and
2. if i done this in production, would it corrupt data ( i.e to shutdown the server during a checkpoint)?
November 30, 2009 at 2:15 am
I can't answer question 1, but regarding question 2, a checkpoint will automatically be called during a server shutdown. If you don't run a checkpoint, all the pages that have changed won't be written to disk, and you may end up having to replay transactions from the log when you start up again. Worst case scenario: this takes a long time.
November 30, 2009 at 3:03 am
winston Smith (11/24/2009)
1. what can cause a checkpoint spid to hang
Counter question. How did you determine that the checkpoint had hung?
if i done this in production, would it corrupt data ( i.e to shutdown the server during a checkpoint)?
Again, counter question. Why would you want to shut down a production server? You shouldn't need to restore to a production box daily so it shouldn't be a concern.
It won't corrupt data (SQL does not corrupt it's own data files) unless you delete the log file in the process, but if you do an unclean shutdown you're looking at some downtime. While SQL's shutting down it won't allow connections. Once it restarts it may have to do restart-recovery and, if there's been a problem with checkpoints, that can take a while and it cannot be stopped.
I've had a situation before where we stopped SQL because there were problems with checkpoint and performance. When it restarted, it was in recovery for 12 hours.
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
November 30, 2009 at 4:03 pm
I'll add one more question. How long did you notice that the SPID was executing?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply