October 2, 2004 at 5:00 am
Hello
the intent: detach db1 from its files in db1 dir , rename db1 dir to
db2 dir & re-attach db1 (as db2) to the files in db2 dir.
sp_detach 'seems' to work, as a refresh in Ent. Mgr. shows the absence of db1. However, when I try to rename the DIR containing the db1 files
SQL Serv tells me "Files are in use!!"
Yet when I do the same thing in Ent. Mgr. everything works as expected.
What on earth am I missing?
Please help.
October 2, 2004 at 8:39 am
Well, SQL should only hold those files long enough to update the statistics then release the database files on a detach. Could there be another process holding the files or the directories from being changed? Have you tried moving the files to a directory name that isn't the same as dir1 or dir2 just a temp space to move the files to?
October 3, 2004 at 5:21 am
Yep thats what I thought - there IS 'something' hogging those files, trouble is Ent.
Mgr. does not tell me what, as the entries in the process mix hold resources only on
the msdb & master dbs.
So what is this spook-of-a-ghost process?
do you have an sp or function that can help me find the culprit?
I have tried 'autoclose on' option - no luck (strange though, as I would have expected
the db to close off, as there were no users for it [except of course.. casper]
I have tried liberalizing permisions ... - no luck
I have tried different user logins ..-
I have tried consoling myself that this is a bad dream ...-
I have not tried recreating the db with a minimal set of permissions.. should I?
What about this 'default-db' thing.. could it be that someone has a hold on the db
whithout my knowledge?
Thanx for the suggestion - will go & try it
Any other advice will be greatly appreciated
best regards
HB
October 3, 2004 at 10:22 am
You could try the File Handle Analyzer utility located at http://www.wilsonc.demon.co.uk/d7fileanalyzer.htm. Might be able to reveal the process holding on to the files.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
October 11, 2004 at 2:44 am
Y E S S
Tried the file handle analyzer & yes that did it !!there was another 'external' process not releasing resources -
Thanx Pete & the rest of you for asssiting - problem solved!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply