Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scottsta
Starting Member
1 Post |
 Posted - 2005-11-19 : 21:21:46
|
I had a LDF log fill a HD, so I detached the DB, deleted the LDF and tried to reattach. This has worked in the past since I don't care about the transaction log and rolling back.When trying to re-Attach, SQL Enterprise pops an expected dialog:"The filename specified for the log file is incorrect. A new log file may be created. Do you want to continue?"I click Yes, and get the error:Title: Microsoft SQL-DMO (ODBC SQLState: 42000)Text: Error 1813: Could not open new database 'DB_Core. CREATEDATABASE is aborted. Device activiation error. The physical file name 'c:\program files\Microsoft SQL Server\MSSQL\data\DB_Core_Log.LDF' may be incorrect. I've found several articles that mention problems restoring a DB when there are multiple files involved, but this was a simple MDF+LDF pair.Any ideas if and how this .mdf can be re-attached??Appreciate your expertise!  |
|
|
Kristen
Test
22859 Posts |
Posted - 2005-11-20 : 02:42:25
|
| I don't know about reattaching your file, but there is a "single file attach" process which you might try. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=single+file+attachMake sure you have a backup copy of your MDF file, stored in a different folder, before you experiment.A couple of observations from an old hand - basically "Don't do this!"First make sure that the database recovery model is set to SIMPLE and not FULL. (Right click database : Properties : Options) This will stop SQL Server keeping transactions on the database.If it is already set to SIMPLE and you still get unacceptably large LDF file then there must be some large transactions going on - like very large deletes, or index rebuilds or somesuch. If you must then SHRINK the LDF instead of the detach/reattach.http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Shrink+a+large+Log+FileHowever, if the file is only going to grow back to its original size fairly soon you would be better to leave it alone - 1) SQL Server has to waste effort re-extending the file and 2) the file will probably get more fragmented over time if you use the Shrink process. In both cases performance will suffer.Your best bet right now might be to restore from backup. If you don't have backups, and use the DETACH method as a means of making backups, then you need to move to making backups. There are plenty of stories about ATTACH failing - and you don't have any options left at that point! You can always try restoring a backup to a brand new database to prove that the backup file is sound, but you only get one shot at an ATTACHKristen |
 |
|
|
|
|
|
|
|
Attach DB Fails when LDF is Deleted