Solved

Can't ATTACH an SQL 2005 DataBase

Posted on 2007-03-29
11
965 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

733 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