aiopsit
asked on
SQL 2008 How To Move CHECKPOINT background process to a different database?
Hello. In SQL 2008 (SP1), the BACKROUND process CHECKPOINT comes to rest on user databases. That user database cannot be restored or detached because exclusive access cannot be obtained. Is there a way to get the CHECKPOINT process to move to another database without stopping/starting the SQL Server?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you can try
recovery interval option
"The recovery interval option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change recovery interval only when show advanced options is set to 1. The setting takes effect immediately without a server restart."
http://msdn.microsoft.com/en-us/library/ms191154.aspx
check
http://msdn.microsoft.com/en-us/library/ms190770.aspx
recovery interval option
"The recovery interval option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change recovery interval only when show advanced options is set to 1. The setting takes effect immediately without a server restart."
http://msdn.microsoft.com/en-us/library/ms191154.aspx
check
http://msdn.microsoft.com/en-us/library/ms190770.aspx
ASKER
Eugene, I understand how checkpoints work. The problem is that the checkpoint process stays on the last database it worked on - even though it is not doing anything (disk io, cpu, memory usage aren't moving. the status of the spid is BACKGROUND). When an attempt is made to restore that database, or detach that database, the operation fails because exclusive access could not be obtained. Personally I think this is a bug.
How do I move the checkpoint spid to a different database.
I can restart the SQL Server, but this seems a bit harsh.
How do I move the checkpoint spid to a different database.
I can restart the SQL Server, but this seems a bit harsh.
ASKER
I believe I have found a solution to this problem. It was found in http://sqlblog.com/blogs/linchi_shea/archive/2008/01/19/sql-server-checkpoint-i-o-behavior.aspx,
which was recommended by EugeneZ. Thank you very much.
Basically, I'm executing the Create Table and Populate statements in a different database (I'm dropping the table after it is populated). This causes the automatic checkpoint to "move" to that database. At this point I can restore or detach the database I wanted
which was recommended by EugeneZ. Thank you very much.
Basically, I'm executing the Create Table and Populate statements in a different database (I'm dropping the table after it is populated). This causes the automatic checkpoint to "move" to that database. At this point I can restore or detach the database I wanted
ASKER
My issue is resolved. I was not "given" a resolution. I was given links to related knowledge base articles from which I was able to derive a resolution.
Checkpoints and the Active Portion of the Log
http://msdn.microsoft.com/en-us/library/ms189573.aspx
--
let say if you need drop the database:
or you need to let checkpoint finish running as long as it takes
or
stop sql server: rename db data\log files - start sl server-> delete suspected mode your db..