Take the 2-minute tour ×
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.

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
add comment

1 Answer

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:

USE master;
GO
ALTER DATABASE yourdatabase SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE yourdatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
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
add comment

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.