Solved

Can't ATTACH an SQL 2005 DataBase

Posted on 2007-03-29
11
924 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
Comment Utility
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
Comment Utility
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
Comment Utility
Did you restore over the top of the files that would not behave?  If so, you are out of options
0
 

Author Comment

by:phermi
Comment Utility
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
Comment Utility
Have you tried to attach that with a different name?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:phermi
Comment Utility
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
Comment Utility
Are you trynig to attach the db files that are in a different location from where they started?
0
 

Author Comment

by:phermi
Comment Utility
nope ... original location
0
 
LVL 21

Expert Comment

by:Kevin3NF
Comment Utility
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
Comment Utility
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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

771 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now