Historically, if I go to rename a database that is currently being used by SQL Server, I get an exception.

On this Windows 8 desktop I'm in.. my primary desktop.. I'm able to MOVE SQL Server database files to a different partition.. without getting any errors.. even if the files are currently 'in use'. I don't understand WHY I can do this, I want to get an error when I go to move a file and it should be listed as being attached in SQL Server master.sys.databases.

I am also able to rename a file that is currently in use. The punchline is that I was talking to my network admin today, and he actually attempts to rename a .MDF file, in order to determine if this file is in use.

I don't like this. What I really want to be able to do is to MOVE files (using a DOS command) from one disk partition to another, but if they're currently attached to the default SQL Server instance, then don't try to move the file.

I was counting on this to work as advertised. I shouldn't be able to rename or move a file that is currently being used as an attached database in SQL Server.

Also, I'm sorry.. but I do have an non-standard hard-drive config.

Disk 0
System Reserved 350mb

Disk 1
C: (Primary volume, Windows 8 Enterprise, etc)

I'm specifically using this SSD solely for SQL Server Express on this machine, I have a half dozen attached databases at any one time and I'm actually working towards creating a MILLION detached databases (and occassionally re-attaching some of them casually when I need them).


SQL Express creates database with AUTO_CLOSE ON by default. Auto close databases automatically close themself (and thus release the file handles) whenever there are no sessions using the database. This works 'as advertised', you just have to read the small print:

True for all databases when using SQL Server 2000 Desktop Engine or SQL Server Express, and False for all other editions, regardless of operating system.

If you don't like the default behavior, simply turn auto-close off on the databases:

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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