asked on

SQL Server 2000 Cannot Attach Database, Problem with LDF File

We are trying to attach a database but it doesn't like the ldf files (there are 2).
One of the ldf files grew too large and became corrupt when we ran out of disk space.

We have renamed the log files, no luck.
We have moved the log files off the computer, no luck.

Can you delete the reference to the ldf files in the mdf file?

Or are there any other suggestions?

Thank you in advance

arbert

yes, but you're going to lose any transactions that were in the log.

checkout http://dbforums.com/archive/43/2002/04/4/259654

Brett

adathelad

Hi,

You can use this to attach a database with one MDF file. It will build a new log file, and ignore the existing 2.

EXECUTE sp_attach_single_file_db 'DatabaseName', 'Path to your MDF file including MDF file name'

Cheers

btaplin

ASKER

We had tried sp_attach_single_file_db earlier with no success. This was the error we received. Maybe this will shed some light.

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'Intranet'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'd:\Program Files\Microsoft SQL Server\MSSQL\data\Intranet_Log.LDF' may be incorrect.
Device activation error. The physical file name 'd:\Program Files\Microsoft SQL Server\MSSQL\data\Intranet_Log2_Log.LDF' may be incorrect.

SQL Server is still looking for the log files.

Any suggestions???

adathelad

I guess you don't have a backup of the database that you can restore?

btaplin

ASKER

That's what we're doing right now. We were hoping that there is a way that we could restore without losing any data.

adathelad

Ok, let me know how it goes....

btaplin

ASKER

I'm going to try this and see if it helps.

http://www.storageadmin.com/Articles/Index.cfm?ArticleID=26044

http://www.storageadmin.com/Files/09/26044/Listing_03.txt

Aycex

If you have the database just make a complete backup, drop the database withh all connected files ie ldf and mdf, then recreate the database abd restore the back up this should give you a completely fresh start.

TheSpirit

OK here is one to try.
This is one to try on a different server,
Create a new database with the same name, file names and locations as the one that has 'gone', then copy your MDF file over the new 'blank' one (you will need to stop SQL before) then start SQL, I think this will come up suspect. I used a dbcc command rebuild_log, but I don't have details on it anymore (its undocumented) I think I found details on the SQL Mag website.

btaplin

ASKER

Thanks for all of your suggestions.
We tried all kinds of things.

sp_attach_db: no luck
sp_attach_single_file_db: no luck
dbcc command rebuild_log: no luck
created a new database: no luck

This worked for us though (Thank God, since our last backup was in January, shame on us).

We updated to SQL Server Service Pack 3 (we're not sure if this is what fixed the problem, possibly SQL Server was corrupted) and reloaded the copy of the database and log file we had backed up right after we had initially realized there was a problem.

Possibly we had corrupted the file(s) somehow, but we are unsure how as we detached the files to make a tape backup, then immediately tried to reattach so that we could shrink the log file.

At this point we were unable to reattach the files. The tape backup was fine however, so we are unsure how this could be the case.

modulo

Dear expert(s),

A request has been made to close this Q in CS:
https://www.experts-exchange.com/questions/20602355/I-would-like-to-clean-up-2-postings.html

Without a response in 72 hrs, a moderator will finalize this question by:

- Saving this Q as a PAQ and refunding the points to the questionner

When you agree or disagree, please add a comment here.

Thank you.

modulo

Community Support Moderator
Experts Exchange

ASKER CERTIFIED SOLUTION

modulo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial