Cannot reset SQL 7 database after deleting LDF file

[Kirbydog]

We have a SQL 7 server with a database that is live, several days ago we found that the 'LDF' file had grown beyond the capacity of our hard drive, 15GB and the hard drive is 17GB so we deleted the LDF file by mistake.

Now we cannot get the database from the state of suspect because the errors that we are getting say that, some device or files are missing or not active. Our logs show that the primary file group is full. We have tried the sp_resetstatus proceedure and it is succssful but that is all we can do.

We looked in the Data directory and the MDF file is there but no LDF file, when we try and create the LDF file it says it is created but there is no LDF file.

We have started and stopped SQL and we have tried the DBCC commands to check the database but again we get an error message 945 with level 16, etc.

Any help from any and all of you is apprecaited ASAP...

Sincerely,

James Colbert

 

[JayKusch]

Have you tried detaching the DB and the reattaching it using sp_attach_single_file_db? This "may" reattach the MDF and recreate a new/empty LDF.

EXEC sp_detach_db @dbname = 'MyDB'

EXEC sp_attach_single_file_db @dbname = 'MyDB',
@physname = 'c:\MyDir\MyDB.mdf'



Thanks

J. Kusch

 

[JayKusch]

If that fails ... your last ditch efforts may be in putting the DB in Recovery mode and trying to DTS all the info out that you can get.

Read this FAQ for steps in accomplishing this.

faq962-4210

Thanks

J. Kusch

 

[Kirbydog]

Thanks J, I tried the first solution set and it worked but we had the database setup for replication, now we can not get it out of replication so that we can detach and reattach it.

Once we get the database removed from replication we believe that it will be ok? Any further suggestions?

James

 

[Corran007]

See this thread

http://www.tek-tips.com/viewthread.cfm?spid=962&sqid=778056&newpid=962

let us know if you still have problems

 

[Kirbydog]

Hello Folks,

Success, my solution was to restore my suspect database to a new database and then I changed my applications to point to the new database with the data still intact.

Whew, I will never ever delete a log file again... Thanks for all of the help. Happy 4th Of July,

Sincerely,

James Colbert