Results 1 to 6 of 6

Thread: Lost ldf file

  1. #1
    Join Date
    Dec 2002
    Posts
    2

    Unanswered: Lost ldf file

    Please help. Our server team deleted the ldf file as it grew to 78 gig. Is there andway to recreate this and attach the database. Or use an ldf from a previous day

  2. #2
    Join Date
    Oct 2002
    Posts
    369

    Re: Lost ldf file

    Q1 Is there anyway to recreate this and attach the database. Q2Or use an ldf from a previous day
    A1 If you use full, (diff if any), and transaction log backup dumps to restore to a time just before the TL file was deleted that should get your DB and the Log to the state it was at prior to the log deletion. (Obviously if no backup dumps are available that is not an option.)

    A2 Not if you are suggesting trying to use e.g.(today's DB with yesterdays Log) as the LSN would be off.

    Q1 Have you already attempted to use:
    exec sp_attach_single_file_db
    @dbname = 'DBName',
    @physname = 'c:\MsSql\Data\Physical_Name.MDF'

    Q2 What are the details of your current DB's condition (inoperable, up, etc.), i.e. why do you want / need to recreate the LDF?

    Deleting the TL is not a recommended way to reduce the size of a TL:
    http://dbforums.com/t623926.html
    Last edited by DBA; 12-13-02 at 11:37.

  3. #3
    Join Date
    Dec 2002
    Posts
    2
    The deleted the ldf in error. We then tried to attach the db without the ldf and it would error out because there was no ldf. We were under the impression that a new one would be created. We have solved the problem but is the a way to shrin the transaction log throuh sql server

  4. #4
    Join Date
    Oct 2002
    Posts
    369
    Q1 Is there a way to shrink the transaction log throuh sql server?
    A1 Yes. For example:

    USE YourDB
    GO
    DBCC SHRINKFILE (YourDBLog, 600)

  5. #5
    Join Date
    Oct 2004
    Posts
    4
    How did you solve the problem?

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    There are several ways how to "solve" this.

    One is what the poster mentioned, except the db needs to show "Suspect", and you need to stop the service. Since LDF file is already gone, - just start the service and a new LDF will get created.

    Another one is to use sp_attach_single_file_db, but it requires a successful detatch with sp_detach_db.

    Yet another is to use DBCC REBUILD_LOG(...) after putting the db in emergency mode (status = status | 32786)
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •