Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Can't ATTACH an SQL 2005 DataBase

Posted on 2007-03-29
11
Medium Priority
?
979 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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 1500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

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…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

715 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