?
Solved

SQL 2008 How To Move CHECKPOINT background process to a different database?

Posted on 2011-02-14
6
Medium Priority
?
1,767 Views
Last Modified: 2012-05-11
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?
0
Comment
Question by:aiopsit
  • 3
  • 3
6 Comments
 
LVL 43

Accepted Solution

by:
Eugene Z earned 1500 total points
ID: 34895571
you can not "Move" BACKROUND process CHECKPOINT  to another databases
----

check Events That Cause Checkpoints
from

http://msdn.microsoft.com/en-us/library/ms188748.aspx

----
you need to check you I\O system and what tranasctions you are running against this database :
http://sqlblog.com/blogs/linchi_shea/archive/2008/01/19/sql-server-checkpoint-i-o-behavior.aspx
http://sqlblog.com/blogs/linchi_shea/archive/2008/01/19/sql-server-checkpoint-i-o-behavior.aspx
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 34895597
one more

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..
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 34895626
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
0
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:aiopsit
ID: 34896289
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.

0
 

Author Comment

by:aiopsit
ID: 34896467
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
0
 

Author Closing Comment

by:aiopsit
ID: 34896492
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.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question