Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm working in the following scenario: I'm creating an script that recreates an entire database. The script involves destruction an recreation of the database and all its objects, as well as a great number of insertions from linked servers. The idea is to be able to recreate the database at will until the development phase ends. My problem is that the script generates high log activity, and soon a CHECKPOINT occurs. I'm killing all connections in my script prior to drop the database, but as CHECKPOINT is a system process I can't kill it, and my script fails. I don't need recovery for this database at this stage, my question is if I can disable the logs for this database to avoid the CHECKPOINT.

share|improve this question
    
Suggest changing the title to "CHECKPOINT prevents database drop" – RLF Sep 2 '13 at 19:43
up vote 0 down vote accepted

No, Logs cannot be disabled. Even if you set the database option for SIMPLE recovery model, the log will still exist since it is necessary for transactionally consistent activities, which are a mainstay of SQL Server.

Nonetheless, running in the SIMPLE recovery model should reduce the conflicts that you are facing. Since this is SQL Server 2008, why not try getting rid of the database prior to rebuilding with something like the following:

EDIT: Minor update to script to show how to keep control of the database.

USE master;
GO
ALTER DATABASE yourdatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
GO
USE yourdatabase; -- So that you become the SINGLE_USER for the next step
GO
ALTER DATABASE yourdatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
USE master;
GO
DROP DATABASE yourdatabase; 
GO

The first ALTER may not be necessary, but it helps prevent someone else getting in as the "single" user.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.