Solved

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

Posted on 2011-02-14
6
1,705 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 42

Accepted Solution

by:
Eugene Z earned 500 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 42

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 42

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
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 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