Solved

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

Posted on 2011-02-14
6
1,692 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
BULK INSERT most recent CSV 19 45
SQL 2008 Conversion failed 7 20
Upgrading Integration Services 3 28
Removing SQL Replication from Microsoft SQL Server 2008 R2 2 23
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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 Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 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

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now