SQL Server Express 2008 not detaching auto-attached file?

The detach/close does happen. If it wouldn't happen then you could not possibly replace the MDF file, because it would be in use. The documentation you quote is not entirely accurate. The correct documentation is at SQL Server 2005 Express Edition User Instances:

User instance databases have the Auto Close option set so that if there are no connections to a database for 8-10 minutes, the database shuts down and the file is closed. This happens automatically, but it can take a while, especially if connection pooling is enabled for your connections.

Detaching the database from the instance by calling sp_detach_db will close the file. This is the method Visual Studio uses to ensure that the database file is closed when the IDE switches between user instances.

If I'd venture a guess I'd say that the database is not detached but auto-closed, and replcing the MDF after deleting the LDF will be (rightfully) seen as an error when trying to open the database.

As side notes:

One should never ever delete the LDF file. If you want to replce the database, replace both the MDF and the LDF with the new ones.
Make sure you replace with proper MDF and LDF versions. SQL Server can upgrade a database, but can never downgrade it.
Get the error. If SQL Express refuses to attach a database, it will give a reason. Look into the RANU created ERRORLOG (in the user profile), the systen event log, or attach profiler to the user instance.

This is at least partially incorrect. (1) I am not using User Instances (they are deprecated), and (2) The XCOPY deployment documentation I referenced specifically states to NOT distribute/copy the LDF file.That said, yes, the symptoms seem to match that the database is closed but not detached. Which is not what Microsoft says it does. Calling 'sp_detach_db' under all circumstances under which the application could close seems like a pretty ugly workaround.

chris 2009-12-14 18:49:59

Sry, I assumed you use AttachDBFilename since the overwhelming majority of users do that w/o realising the draw-backs.

Remus Rusanu 2009-12-14 18:52:18

Yes, I am using AttachDBFilename, since that is how "XCOPY Deployment" works. However, that is an entirely separate issue from RANU.

chris 2009-12-14 18:54:12

I wasn't aware in 2K8 the AtachDBFileName does not start a user isntance, as it did in 2005. If the 2008 xcopy attaches the db with auto-close and *leaves it attached* on disconnect (but auto-closed) then this would explain the behavior you see. Why do you need to detach and replace as a routine operation in your application? I undertsand at development time, but on deployment? At development time forcing a sp_detach_db seems acceptable, is what VS does under the covers anyway apparently.

Remus Rusanu 2009-12-14 19:03:18

In testing (and production, on the analysis side) we often need to swap in a different database file to work from a known baseline. Unfortunately this looks like a needlessly complicated implementation on the part of Microsoft.

chris 2009-12-15 15:07:12

ansaurus

tags:

views:

answers:

SQL Server Express 2008 not detaching auto-attached file?

related questions