Hi.

I have a problem that seems to be specific to SQL server 2008.

When restoring a backup (each backup contains multiple databases), the SQL server will nearly always start a CHECKPOINT command (which I can see when running "sp_who2").

This causes the backup restore to fail because the database is being locked by the CHECKPOINT command, and the CHECKPOINT command remains active for several minutes afterwards before dissappearing.

This happens on databases that are not in use (of course) when the backup restore is started, and even after stopping and restarting the SQL server service (or computer) just before starting the restoration.

It happens only on SQL Server 2008. 2005 and earlier does not seem to start the CHECKPOINT command, or if it does, it finishes before I'm able to spot it in sp_who2, and before it causes any problems with the backup restore.

Is it possible to somehow disable this CHECKPOINT feature, or reconfigure it so that it works properly, like in SQL server 2005?

May be your disks are not behaving good to write the transactions that are supposed to write quickly.

Check Current Disk queue length on the disks & it seems in your case SQL Server was trying to push the I/Os harder with more outstanding async I/O write requests when it detected that a checkpoint was issuing small block sized I/Os. 

Thanks for the reply.

Where do I check the disk queue length?

Also, the SQL Server 2008 is running on the same physical disk as the SQL server 2005 that works fine, so it's not a system-wide disk problem - only with SQL Server 2008.

specifically for checkpoint you can change recovery interval using sp_configure, that's what drives checkpoint behaviour in sql server

Checkpoint is to write the Dirty pages to the disk so that reduce the data loss . Are you restoring the database with REPLACE Command.

Madhu

I believe you need to explain what kind of transactions are running on this SQL 2008 instance.

http://sqlblog.com/blogs/linchi_shea/archive/2008/01/19/sql-server-checkpoint-i-o-behavior.aspx fyi on the behaviour.

 I've done some more testing.

I checked the recovery interval using sp_configure, and it's identical on both the 2005 server on which it works fine, and the 2008 server (which I have now upgraded to SQL Server 2008 developer edition).

 The reason the backups are being stopped, is because the CHECKPOINT process on the 2008 server seems to be constantly working on the various databases, which causes the DBName in "sp_who2" to change to those bases when it's working on them. (there is as far as I can see only one CHECKPOINt process, which is always there regardless of whether there is any activity or not)

 The CHECKPOINT process is also there on the 2005 server, but there it has a status of SUSPENDED, and never seems to change its DBName to anything except "master" and occassionally "tempdb" regardless of whether there is activity or not.

 The problem with the way CHECKPOINT is behaving on 2008 is that the program that performs the restoring of the backups does a check between restoring each database, using "sp_who2" to see if any processes are working on that DB - if the CHECKPOINT happens to be running on that particular DB just then (which it very often does, possibly because a query for the database's ..sysfiles table is done just beforehand to get the filenames) it termiantes the restore process.

This is probably not a bug - the 2 versions of SQL server just behave differently, and it just happens to cause problems in this particular case.

 So basically, I need a way to make the 2008 server's checkpoint process behave like the 2005 one.