I'd like to know which is the best solution possible to reduce the SQL Server transaction log in the live production server without downtime ?

  1. Database full backup - which should commit transaction log ? (like in Exchange Server ?)

  2. executing the following T-SQL Script from SSMS manually during the production working hours ?

    -- Otto R. Radke - http://ottoradke.com
    -- Info: T-SQL script to shrink a database's transaction log. Just set the
    -- database name below and run the script and it will shrink the
    -- transaction log.
    -- Update the line below with the name of the database who's transaction
    -- log you want to shrink.
    -- Don't change anything below this line.
    -- Declare variables
    DECLARE @SqlStatement as nvarchar(max)
    DECLARE @LogFileLogicalName as sysname
    -- Alter the database to simple recovery
    EXEC ( @SqlStatement )
    -- Make sure it has been altered
    SELECT [name], [recovery_model_desc] FROM sys.databases WHERE [name] = DB_NAME()
    -- Set the log file name variable
    SELECT @LogFileLogicalName = [Name] FROM sys.database_files WHERE type = 1
    -- Shrink the logfile
    DBCC Shrinkfile(@LogFileLogicalName, 1)
    -- Alter the database back to FULL
    EXEC ( @SqlStatement )
    -- Make sure it has been changed back to full
    SET @SqlStatement = 'SELECT [name], [recovery_model_desc] FROM ' + DB_NAME() + '.sys.databases WHERE [name] = ''' + DB_NAME() + ''''
    EXEC ( @SqlStatement )
  1. Creating maintenance plan - On Demand - for DB shrinking ?

so what's the difference and purpose of those methods ?

Any help would be greatly appreciated.


1 Answer 1


Are you using the log backups for point in time recovery? If not, then one method to manage the log file would be to set the Recovery Model to Simple and manually shrink the databse from SSMS (right click database... Tasks... Shrink... Databse). This will shrink the database files for you. The Simple Recovery model should keep the log in line.

  • Joe, thanks for your reply at the moment there is no backup set at all and I am hitting the 98% transaction log disk space full :-| this is now in production live DB so based on your suggestion I need to commit the 36 GB+ transaction log and then change the recovery model to SIMPLE ? (during business hours) Apr 15, 2011 at 2:09
  • 1
    Not sure what you mean by "commit", but changing to simple will allow you to shrink the log file; if you aren't backing up the logs, FULL recovery is not getting you anything. Your script does what joe is suggesting, but sets it back to full when it's done. I set to simple, shrink, then if you need point-in-time recovery, set back to full and get a regular log backup to disk set up. And if you don't have full backups, get that set up too!
    – SqlACID
    Apr 15, 2011 at 3:01
  • Glad to help...
    – joeqwerty
    Apr 16, 2011 at 13:09

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .