We help IT Professionals succeed at work.

Original SQL 2005 db files locked after relocating files to 2nd partition

OnsiteSupport
OnsiteSupport asked
on
I used SQL Server Managem Studio to successfully copy my DB files to new partition.  However, the old files still have a lock on them.  Any ideas on how to resolve?  I am also concerned that they are still in use even though they haven't grown since I moved them.

Thanks
Craig
Comment
Watch Question

Commented:
Well, the only way to move them is to use ALTER DATABASE and move the files, but the files don't actually move, their locations change in the SQL Server system tables.

In order to move the files, you need to stop the SQL Server and move them and then start the SQL Server back up.

The other way to move database files, is to Detach the database and then move the files and then reattach the database from the new files in the new location.
David ToddSenior Database Administrator

Commented:
Hi,

See http://msdn.microsoft.com/en-us/library/ms345483.aspx.

The steps are
1. Set database offline
2. Move the physical files
3. Update the location of the file
4. Bring the database online
5. Verify

SELECT name, physical_name AS CurrentLocation, state_desc
FROM sys.master_files
WHERE database_id = DB_ID(N'<database_name>');

If the system thinks the files are where you think they should be, then the lock most likely needs SQL to be restarted.

HTH
  David
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
You don't need to stop SQL Server to move user databases.
Check if database is set for read-only or if they are in read-only system folder.

Commented:
This is right, you can take the databases offline to move the databases, so SQL Server can remain online.

I only indicated to stop SQL Server because if you were moving ALL the databases, then it would be just as simple to move them all at once, and it would not be valuable to have all the databases offline, but you certainly can do that.

Sorry if I confused the issue.  I may have misunderstood the question.
David ToddSenior Database Administrator

Commented:
Hi,

To Sumerise:
Moving the data files via SSMS it is equally likely that you in fact added new files to the filegroups, hence the lock on the original files.

Use that check to see what the system thinks the correct files are.

The page I linked to is the new, preferred method of moving files, instead of the detach and attach that dbaduck referred to, and this method came in for SQL 2005.

I don't believe that having the file tagged read-only by the filesystem will prevent you deleting it.

HTH
  David

Author

Commented:
I detached the DB in SSMS.  Copied the DB and Log files to the new location.  Attached the files.
I am concerned that SQL has a lock on the old files because they are in use in some way? Is there a way to find this out?

Thanks,
Craig

Commented:
please define: "the old files still have a lock on them"

can you rename old files? are you sure that you attached new -not old mdf ldf files?

Commented:
also check NTFS permissions of the old files ..
Commented:
You can also verify that the old files are not the ones attached, by going into the Management Studio and right clicking on the database and choosing Properties, then go to Files on the left and verify that the path of the files attached are indeed in the right place.

If they are, there should not be any locks.

You can also go into Master database and do

select *
from sys.master_files

and look for any of the files that are the same as the old files.

Author

Commented:
Perfecto!!! thank you.  Sorry it took so long to respond. I didn't realize your response had been posted.