SQL Server Performance

Sql Server 2008: How to shrink my log file when disk space is full

Discussion in 'SQL Server 2008 General DBA Questions' started by manikon, Oct 12, 2009.

  1. manikon New Member

    Hi,
    In my server, data file is in D drive and Log file is in F Drive. Now my log file used all the disk space. When I try to take database backup with no_log option i'm getting the below given error.BACKUP
    DATABASE dbname TO DISK='D:mydb.bak' with no_log Msg 3013, Level 16, State 1, Line 1
    BACKUP
    DATABASE is terminating abnormally.Msg 9002, Level 17, State 2, Line 1
    The
    transaction log for database 'dbname' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
    When I check log_reuse_wait_desc in sys.databases it shows LOG_BACKUP.
    How to clear my log file to gain space without losing data?
    Thanks in advance.
    Regards,
    Mani
  2. melvinlusk Member

    Switch the database to SIMPLE mode and then try the backup. From Books Online:
    "The BACKUP LOG WITH NO_LOG and WITH TRUNCATE_ONLY options have been discontinued. If you are using the full or bulk-logged recovery model recovery and you must remove the log backup chain from a database, switch to the simple recovery model. For more information, see Considerations for Switching from the Full or Bulk-Logged Recovery Model."
  3. venky2307 New Member

    Hi Mani,
    I assume that you have a full-database backup of your DB in question (this, as you are well aware, is mandatory for any kind of recovery processes that might be!) that is closest to your SLA needs!
    As a first step, you can first evaluate, or have an idea of your logspace of all your databases in the instance by using the command:
    DBCC SQLPERF(LOGSPACE);
    GO
    Once you have an idea as to the amount of space available vs. used, you can take appropriate actions that suits your environment.1. Since you are working with SQL Server 2008 (where future versions of the WITH NO_LOG may be banned/made dysfunctional with the usual backup commands...), you can start by:
    USE <dbname>; -- to set the context of currency of database of interest
    GO
    ALTER DATABASE <dbname> SET RECOVERY SIMPLE;
    GO
    DBCC SHRINKFILE (<LogFileName>, <TargetSize>);
    GO
    /* Reset the recovery model to FULL as before */
    ALTER DATABASE <dbname> SET RECOVERY FULL;
    GO
    Please remember the following points:
    The shrinking process for a transaction log addresses only the inactive virtual log files. So the parameter cannot be less than the overall size defined initially. More help is amply provided in the BOL for the corresponding version. One of the tips given in the MSDN is to check the size of the file in question after the shrinking process with the following command:
    SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMBFROM sys.database_files;
    GO
    Hope these small pointers help you combat your problem. Please share your experiences with the rest of the crowd.
    With best wishes,
    Venky
  4. manikon New Member

    Thanks Melvin & Venky for your useful information!
    I'm able to shrink my database log file.
  5. moh_hassan20 New Member

    ... and next step is to add new disks ,to avoid facing this problem again.[;)]
    Plan for storage capacity , not to exceed 80% of disk usage
  6. johnson_ef Member

    HI Mani,I have one suggestion too. You can set T-Log backups for this database if you observe the hug transactions and Log Growth. Like this way, you can limit the Log file growth up to some extend.Assume your backup files are in different drive.

Share This Page