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
Solved

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

Posted on 2011-02-14
6
1,699 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 42

Accepted Solution

by:
EugeneZ 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:EugeneZ
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:EugeneZ
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL DATEADD 10 70
average of calculation (TSQL) 4 26
Need return values from a stored procedure 8 19
MS SQL Server select from Sub Table 14 23
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

809 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