Re-attach problem- dead in water- help!

[AZdesertdog]

Weekly we run a housekeeping script that detaches the dbase, deletes the transaction log and then re-attaches and have never had a problem.
Script is:
---------------------------------------------------------
EXEC sp_detach_db @dbname = 'rmprod'
go

exec xp_cmdshell 'erase d:\mssql2k\mssql\Data\RMProd_Log.LDF'
go
// delete the log file then run the next command
// This error on the attach is ok because I'm letting it //create a new log:

-- Device activation error. The physical file name 'D:\MSSQL2K\MSSQL\data\RMProd_Log.LDF' may be incorrect.
EXEC sp_attach_single_file_db @dbname = 'RMProd',
@physname = 'd:\mssql2k\mssql\Data\RMProd_Data.MDF'
go
----------------------------------------------------------
Today for some reason we're Getting this error on the attach:
-------------------------------------------------------
Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'RMProd'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\MSSQL2K\MSSQL\data\RMProd_Log.LDF' may be incorrect.

Any ideas would be helpful! Thanks.

 

[nigelrivett]

Not very helpful but occassionally a detach/attach fails.
Can you restore from a backup?

I would recommend taking a backup before the procedure you are executing.

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[nigelrivett]

You can try creating a new database and copying the files over it as in

http://www.nigelrivett.net/RecoverCorruptDatabase.html

======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[AZdesertdog]

We looked over the recovery sheet but don't have enough physical space to move/rename the mdf file and create another of the same size to be copied into. Any other thoughts?

 

[SpiderBear6]

Try removing the database from sql server and then attaching it again. Sometimes sql server gets itself tied in knots and think it has a database attached when really the database is only partly attached.

--------------------------------------
"We are star-stuff. We are the universe made manifest trying to figure itself out."
-- Delenn in Babylon 5 - "A Distant Star"