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.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
The first ALTER may not be necessary, but it helps prevent someone else getting in as the "single" user.