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.

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
D: SSD

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).

share|improve this question
add comment

1 Answer

up vote 10 down vote accepted

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:

ALTER DATABASE [<dbname>] SET AUTO_CLOSE OFF;
share|improve this answer
    
thanks, I don't know how I haven't ran into this before :) –  Aaron Kempf Mar 26 '13 at 13:44
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.