RE: Humongous transaction log file - Repost! - URGENT!

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Andras Jakus (andras.jakus_at_vodafone.com)
Date: 03/30/04 

Date: Mon, 29 Mar 2004 20:06:07 -0800

HI

Have you backup from .mdf file?

When no, drop the failed database, create more free space temporary on log disk (eg: detach another databases, move temporary the log files to another location or backup logs and truncate), restore from backup, detach database, delete the .ldf file and use sp_attach_single_file_db

When yes, drop the database, copy the mdf file to original location and use sp_attach_single_file_db

When the database restored, verfy the recovery modell, I think, You use "Full".
Perform the log backup periodically on maintenance plan. (Only log backup clear out the log.)

For more information use the Books Online.

JBandi

     
     ----- Bill Nguyen wrote: -----
     
     This was posted last week with inaccurate information:
     SQLserver 2000 on Win2K server
     Database (mdf) file size: 213MB
     Log (LDF) file size: 160GB! Yes, 160 GIGABITES!
     
     I had to stop sql services, delete the log file in order to free the
     diskspace for other databases to run.
     Now when I tried to restore the database from a Database maintenance Plan
     backup, it would put the LOG file right back (160GB) and keep running, I
     don't know for how long before the restore process can finish!
     When I cancelled the restore, the database also gone! I found the MDF file
     in the folder and tried to attach it to the server but it won't let me. I
     even created an empty LDF file to fool the system but it didn't work, of
     course (silly me!)
     Any suggestion is greatly appreciated!
     
     Bill
     
     
     
     

Relevant Pages

  • Re: MSDb and Master maintenance
    ... You can't do log backup on master. ... > Starting maintenance plan 'DB Maintenance Plan msdb' on ... > Database msdb: ...
    (microsoft.public.sqlserver.server)
  • Re: Restore Log Files with Move
    ... A log backup always follows a full backup. ... What is important is the MOVE for the first (database) ... I don't think you even could specify a MOVE option in 7.0 for the following RESTOREs. ... >I know that you can restore a full backup to a different ...
    (microsoft.public.sqlserver.server)
  • Re: Emergency PLEASE PLEASE PLEASE!
    ... you restore the final log backup (or the log backup, ... > In EM when you restore the full backup, make sure you go to the Options ... > and select "Leave database read-only and able to restore additional ... > SQL Server MVP ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Server Log restore error
    ... Are you trying to restore above the same database then try below:- ... --Restore the full database with No recovery ... >I performed a SQL Server Log backup from enterprise manager to backup the ...
    (microsoft.public.sqlserver.tools)
  • Re: Restoring File Backups
    ... Now you can restore the full backup to a new database with NORECOVERY ... AFter the restore of FULL backup rectore the transaction log backup took ...
    (microsoft.public.sqlserver.server)