PeterKellner.net

Problem

So, if you have been doing development with Visual Studio 2010, Entity Framework CodeFirst, SqlServer or SqlServerCE for any amount of time, you’ll quickly run into the problem that the database can not be reinitialized because it is open.  Basically, the scenario is this.

1)  Put in your Global.asax.cs file a line that always recreates the database (naturally because you are in a development mode and as you constantly change your model and seed data).  The line is something like this:  Database.SetInitializer(new DropCreateDatabaseAlways<SiteDB>());

2)  Run your application with something like Debug/Run (All is fine)

3)  Go into the database browser (either in Visual Studio or Enterprise Manager)  and look at some data.

4)  Run your application again and you will get this error: “[SqlException (0x80131904): Cannot drop database "NewYorkTimesDb" because it is currently in use.]”.  This is because your database browser has a connection to the database and until it is dropped, you can not drop the catalog.

Solutions

The easiest and guaranteed to work solution is just to restart your SqlServer database server (control panel, services).  This is what I use to do but finally got tired of that because it takes about 20 seconds and, well, I’m very impatient.

The other solution I found in the forums (can’t find the link right now) is to execute the following script from the master database catalog:

use master
ALTER DATABASE NewYorkTimesDb
   SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE NewYorkTimesDb SET MULTI_USER

Basically, just switching from multi-user to single-user and back clears the connections.

HTH’s!

In this post, we will show and explain a small TSQL Sql Server 2008 procedure that deletes all rows in a table that are older than some specified date.  That is, say the table has 10,000,000 rows in it the accumulated over the past 2 years. 

Say you want to delete all but the last 30 days of activity.  If you just simply say DELETE FROM table WHERE id>10000, you will cause this to happen in one transaction and likely, you will get an error.  That’s the best case.  The worst case is your system tries to do this, eventually consumes all the resources in your computer and crashes your server.

(more…)