SQL Server 2000 Cannot Attach Database, Problem with LDF File

Posted on 2003-03-24
Medium Priority
Last Modified: 2012-05-04
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
Question by:btaplin
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +3
LVL 34

Expert Comment

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

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

LVL 23

Expert Comment

ID: 8196138

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'


Author Comment

ID: 8196190
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???
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 23

Expert Comment

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

Author Comment

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

Expert Comment

ID: 8196306
Ok, let me know how it goes....

Expert Comment

ID: 8196342
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.

Expert Comment

ID: 8206653
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.

Author Comment

ID: 8213442
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.


Expert Comment

ID: 8439371
Dear expert(s),

A request has been made to close this Q in CS:

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.


Community Support Moderator
Experts Exchange

Accepted Solution

modulo earned 0 total points
ID: 8457412
Saving this Q as a PAQ and refunding the 150 points to the questionner


Community Support Moderator
Experts Exchange

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question