Solved

Can't ATTACH an SQL 2005 DataBase

Posted on 2007-03-29
11
949 Views
Last Modified: 2011-09-20
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.SmoEnum)
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.ConnectionInfo)
------------------------------
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'. (Microsoft SQL Server, Error: 5123)

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.
0
Comment
Question by:phermi
  • 6
  • 5
11 Comments
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18815492
Is c:\...kpi.mdf the data file you are trynig to attach?  Can you rename it?

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

Author Comment

by:phermi
ID: 18815607
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 ....
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18815800
Did you restore over the top of the files that would not behave?  If so, you are out of options
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:phermi
ID: 18816412
Kevin,
yes, but as I said, I kept a copy of the only one MDFfile and its log
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18816537
Have you tried to attach that with a different name?
0
 

Author Comment

by:phermi
ID: 18817093
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 ...
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18817197
Are you trynig to attach the db files that are in a different location from where they started?
0
 

Author Comment

by:phermi
ID: 18818024
nope ... original location
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 18818151
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?
0
 

Author Comment

by:phermi
ID: 18818196
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
0
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 500 total points
ID: 18818257
So my suggestion then is to put the copies of the files you have in a different physical location on the box than the one that got restored, and then attach as "Mydatabase_safe"

If that works, the next step is to compare the data and start moving it back into the restored db.  I use SQL Data Compare from Red Gate Software for this.  Well worth every dime, and I am not affiliated with them.
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Review MS SQL cluster diagram 9 89
SQL Express connecting form remote error 26 7 45
Truncate vs Delete 63 105
How can I get this column in my query? 2 46
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question