none
Transaction log too big RRS feed

  • Question

  • I am using SQL Server 2005
    My data file has only 2Gb and his log file has 25Gb. There is no users logged right now in this database and I need to reduce the size of the log before the disk runs out of space (it has only 0.4Gb free left)

    Disk usage report tells me that 99,5% of the log is used

    What is the best way to reduce the size of the transaction log?
    thanks
    Monday, April 21, 2008 10:14 AM

Answers

All replies

  • You can shrink the transaction log using the below command.. Since no space to take tlog backup we can truncate and shink.

    Backup log dbname with truncate_only
    go
    DBCC shrinkfile('logicalfilename',100)

    You can get the logical file using the below command

    select name from dbname.sys.database_files where type=1



    Monday, April 21, 2008 10:24 AM
    Moderator
  • Once shrinking completed check why your log file grown too big.

    Check the recovery model of the database. If it is full check with your business and change it to simple if needed, else schedule frequent transaction log backup to avoid the same in future. Refer Abnormal growth of transaction logs link
    Monday, April 21, 2008 10:27 AM
    Moderator
  • When I do a full backup wasn't it supposed to automatically truncate the log?
    I did a full back up 10 hours ago but it seems that the log is still very big.
    Monday, April 21, 2008 10:53 AM
  • Yes when you do full backup it will wash out committed transaction from the log file, but the log file won't be shrinked automatically. The size allocated will remain the same and the freespace in log file will increase after backup. You can use the below command to check % of log file used before backup and after backup.

    DBCC SQLPERF(LOGSPACE)
    Monday, April 21, 2008 11:17 AM
    Moderator
  • You really should investigate why your transaction log grew so large (so it does not just happen again).

     

    Are you running in full or simple recovery model?  If you are running in full recovery model, you have to do transaction log backups periodically, or else your log file will fill up, and autogrow.

     

    If you are running in simple recovery model, you might have long running transactions that cause the log to fill and grow. The query below will tell you how big your files are and how much space they have available inside.  The queries below can help you figure this stuff out.

     

    -- Get recovery model and log reuse wait desc

    SELECT [name], recovery_model_desc, log_reuse_wait_desc

    FROM sys.databases

     

    -- Individual File Size query

    SELECT name AS 'File Name' , physical_name AS 'Physical Name', size/128 AS 'Total Size in MB',

    size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB'--, *

    FROM sys.database_files;

     

    -- Find oldest transaction

    DBCC OPENTRAN

     

    -- Get input buffer for a SPID

    DBCC INPUTBUFFER(21)

     

    Monday, April 21, 2008 4:33 PM
    Moderator