SQL Server Performance Forum – Threads Archive
sp_attach_db fails
OK, So I have a 48GB Database that was in the middle of an update when I received a message that the log file was full and I needed to do a backup. Now normally when I do this I just detach the DB, delete the .LDF file and reattach using sp_attach_db. A new, empty log files gets created. When I attempted the detach, I received the same message about the log file. When I refreshed the detach had worked and the DB was nolonger in the list. When I try to reattach I get an error message… Server: Msg 1813, Level 16, State 2, Line 1Could not open new database ‘market’. CREATE DATABASE is aborted.
Device activation error. The physical file name ‘f:MSSQLdatamarket_Log.LDF’ may be incorrect. What happened? Why won’t it just recreate the LDF, why is it still looking for the old one? How can I get past this? I followed a ‘unsupported’ solution posted by the moderator with no luck. Help please. Vincenzo0506
To attach single .MDF file then use SP_ATTACH_SINGLE_FILE_DB which will create new log file as specified. Refer to this KBAhttp://support.microsoft.com/defaul…port/kb/articles/q271/2/23.ASP&NoWebContent=1 for more information. May I know about that unsupported solution. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Did you try to Checkpoint and after detach?
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
The unsupported method was posted by you, Satya. Here are a few of the steps: sp_configure ‘allow’, 1
select dbid, status, name from sysdatabases where name =’marketguide’
update sysdatabases set status=32768 where dbid=16
sp_configure ‘allow’, 1 go reconfigure with override
select dbid, status, name from sysdatabases dbcc rebuild_log (‘marketguide’, ‘f:mssqldatamarketguide_log.ldf’)
update sysdatabases set status=16 where dbid=16
sp_configure ‘allow’, 0
go
reconfigure with override and I tried the sp_attach_single…. I get the same message. I am looking for help that is not in the book. the db is some kind of transient state and needs the ldf file, but it’s gone.
I found the followin on the net. No good news, but I hope help. I did almost the exact same thing. After banging my head against the wall for about 8 hours, I called Microsoft
Technical Support and paid $250 for them to walk me through how to fix it. I should
have written down the procedure, but it was really involved — and all that I wanted
to do was get the database back up so that I wouldn’t be fired. The procedure
involved weird stuff like going into system tables and changing system- level flags. I suggest that you shell out the $250 and call them. I bet they’ll get you up and
running in less than an hour. >TEST–Original MessageTEST– In attempting to backup a transaction file (LDF from
>SQL2K) that got way (16GB) too big, it was corrupted and lost. I still have the MDF
>file for this database but…….
Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
What if you use EM. Right click on Database, select All tasks – > Attach database.
Select the mdf file.
You will get a message telling you that the filename for the log file is incorrect and that a new log file will be created.
Click Yes. Do you still get the same error? /Argyle
When I try to attach the server via Ent. Mgr, I select the dbf file and it populates a box with the original file names and locations. The mdf has a green check box and the ldf has a red X. When I try to change th efile name on the ldf, my OK box goes away. If I try to attach the way it is (Red X), I get the following error: Error 1813: Could not open new database ‘market’. CREATE DATABASE is aborted.
Device activation error. The physical filename ‘f:MSAQLdatamarket_log.LDF’ may be incorrect. Then I click OK. The next message is Attaching database has failed. is there a utility or DBCC command to fix MDF files? Vincenzo0506
I guessed it was my proposal and surprised that it didn’t worked. IS this database consists multiple log files?
If so, use sp_attach_db rather than sp_attach_single_file_db to attach a database with multiple log files. You cannot attach a database that has been created with multiple log files without also attaching all the log files. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Sorry. This had one MDF file and one LDF file, neither command works.
Do you have the backup for this database?
Have you detached the DB previously, any issues or errors?
Is the physical path & logical names for the database are similar? Try to delete the current .LDF file and try attching .MDF file alone.
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
There is another copy, but it is 48GB and they are seperated by a very active T-1, so copying would take a while and degrade the connection for our customers. Yes, I have detached, deleted the ldf and attached without problems. I do it all the time to shrink LDF files. The Physical and logical names have not changed. That is esactly what I am doing. There was an error during an update and I was instructed to run a BACKUP to truncate the log file. That failed, so I decided to do it teh way I always do, detach, delete, attach. The detach gave me the same error, run BACKUP to shrink log file. But the detach actually worked because the dtabase was out of the list. So I deleted the LDF file and tried to reattach. That is where I am now. Usually it will just recreate the LDF file, but there is something not right in the MDF file and it is looking for it. It doesn’t exist.
There you go due to the continued process you might have missed running DBCC checks before detaching (as its suggested). To reduce the Tlog size its normal practice to use DBCC SHRINKFILE by truncating the transaction log or to truncate just issue BACKUP LOG… WITH TRUNCATE_ONLY. Though my proposal (which is unsupported) has not worked the only option is to take backup of other database and restore on this machine even though it crunches the performance a bit during the process. Or a last resort try contacting MS Support for a solution or fix. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Thank you for your help Satya. Vincenzo
I acknowledge my process couldn’t helped and only the way is to goto MS Support as a last resort, will be glad to know the resolution once you fixed the issue apart from restoring from the copy. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I willpost any updates, but I doubt I am going to pay Microsoft to solve this.
So I made another attempt to fix this. I renamed my file.
I created the new db.
I took it offline.
I renamed the new file and the old file back to the original.
I tried to put it back online but it failed.
so, I renamed the LDF file and tried again, and it failed.
I did this…:
sp_configure ‘allow’, 1
reconfigure with override
select dbid, status, name from sysdatabases where name =’market’
update sysdatabases set status=32768 where dbid=16
sp_configure ‘allow’, 1 go reconfigure with override
it let me place it on line in dbo mode
then I did the following:
select dbid, status, name from sysdatabases dbcc rebuild_log (‘market’, ‘f:mssqldatamarket_log.ldf’)
it told me the LDF existed, so I renamed it(again) and the command completed sucessfully and sugested I run
DBCC CHECKDB ( ‘market’ , REPAIR_REBUILD ) WITH ALL_ERRORMSGS
it needed the file in single usermode, so i changed it and ran it again.
I placed it in normal mode and it actually looks OK. It was your assurance that your procedure should have worked. that made me try again. thank you.
]]>