phermi
asked on
Can't ATTACH an SQL 2005 DataBase
Web application aborted and after that no more logins into it were accepted because:
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
I went and DEATTACH the DB selecting to UPDATE STATS, KEEP FULL TEEXT CATALOGS and DROP CONNECTIONS, DEATTACH was successful.
After that every time I try to re-attach the DB I got this:
TITLE: Microsoft SQL Server Management Studio
--------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEn
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
--------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Conne
--------------------------
CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\kpi.mdf'
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476
--------------------------
BUTTONS:
OK
--------------------------
f I stop SQL, I can copy the DB files. I have re-started SQL and the server. I have performed consistency check on all other DBs.
No luck. Any ideas will be highly appreciated.
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
I went and DEATTACH the DB selecting to UPDATE STATS, KEEP FULL TEEXT CATALOGS and DROP CONNECTIONS, DEATTACH was successful.
After that every time I try to re-attach the DB I got this:
TITLE: Microsoft SQL Server Management Studio
--------------------------
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEn
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
--------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Conne
--------------------------
CREATE FILE encountered operating system error 32(The process cannot access the file because it is being used by another process.) while attempting to open or create the physical file 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\kpi.mdf'
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=5123&LinkId=20476
--------------------------
BUTTONS:
OK
--------------------------
f I stop SQL, I can copy the DB files. I have re-started SQL and the server. I have performed consistency check on all other DBs.
No luck. Any ideas will be highly appreciated.
ASKER
Kevin,
Thanks. Yes, that;s the DB. Yes, I can rename, copy, delet it if I STOP SQL, otherwise nothing is possible. I am not an expert so there are new things for me every step down the road ... I can get the process explorer, but just because stopping SQL "releases" the DB I kind of convice SQL it doing or attemping to do something to the DB while UP.
I read about forcing DB into "suspect"mode .. but the DB needs to be listed .. ATTACHED ... and that is excatly what I can't do. If I rename and try to attach, it will tell me that the file does not exist ...
Meanwhile, I kept a copy of the DB and proceeddd to restore it from backup .... everything is fine except the consequent data lost ....
Thanks. Yes, that;s the DB. Yes, I can rename, copy, delet it if I STOP SQL, otherwise nothing is possible. I am not an expert so there are new things for me every step down the road ... I can get the process explorer, but just because stopping SQL "releases" the DB I kind of convice SQL it doing or attemping to do something to the DB while UP.
I read about forcing DB into "suspect"mode .. but the DB needs to be listed .. ATTACHED ... and that is excatly what I can't do. If I rename and try to attach, it will tell me that the file does not exist ...
Meanwhile, I kept a copy of the DB and proceeddd to restore it from backup .... everything is fine except the consequent data lost ....
Did you restore over the top of the files that would not behave? If so, you are out of options
ASKER
Kevin,
yes, but as I said, I kept a copy of the only one MDFfile and its log
yes, but as I said, I kept a copy of the only one MDFfile and its log
Have you tried to attach that with a different name?
ASKER
I remember trying it .. but wanted to give you realdat. I took a backup of my actual DB, DEATTCHED it and just for fun tried to ATTACHED back before attemping the test you suggested. I got the same stupid error that the kpi.mdf is in use ..
I do no t get this ...
I do no t get this ...
Are you trynig to attach the db files that are in a different location from where they started?
ASKER
nope ... original location
I'm lost....
When you restored, did you change the physical file names? otherwise the mdf and ldf have been overwritten by the restore
Do you have a copy of the problem mdf/ldfs on a local drive somewhere (different drive or folder) that you can try to attach?
When you restored, did you change the physical file names? otherwise the mdf and ldf have been overwritten by the restore
Do you have a copy of the problem mdf/ldfs on a local drive somewhere (different drive or folder) that you can try to attach?
ASKER
Sorry if I've confused you. Yes, I restored OVER the original names and Yes I still have a copy of the set that did not want to get attached
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You may have to go get Process Explorer from sysinternals.com to see what has a handle on that file. I recently had an issue where the databases would not detach, so I had to stop SQL Server, move the files, reastart, let the databases come up suspect, drop the database, move the files back and re-attach.