Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

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
0
btaplin
Asked:
btaplin
  • 4
  • 3
  • 2
  • +3
1 Solution
 
arbertCommented:
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
0
 
adatheladCommented:
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
0
 
btaplinAuthor Commented:
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???
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
adatheladCommented:
I guess you don't have a backup of the database that you can restore?
0
 
btaplinAuthor Commented:
That's what we're doing right now. We were hoping that there is a way that we could restore without losing any data.
0
 
adatheladCommented:
Ok, let me know how it goes....
0
 
AycexCommented:
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.
0
 
TheSpiritCommented:
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.
0
 
btaplinAuthor Commented:
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.


0
 
moduloCommented:
Dear expert(s),

A request has been made to close this Q in CS:
http://www.experts-exchange.com/Community_Support/CleanUp/Q_20602355.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
0
 
moduloCommented:
Saving this Q as a PAQ and refunding the 150 points to the questionner

modulo

Community Support Moderator
Experts Exchange
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now