Sql-server – How to reduce the SQL Server transaction log on the fly for production DB

sql serversql-server-2005sql-server-2008

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.
    ------------------------------------------------------------------------------
    USE 
    ------------------------------------------------------------------------------
    -- Don't change anything below this line.
    ------------------------------------------------------------------------------
    GO
    -- Declare variables
    DECLARE @SqlStatement as nvarchar(max)
    DECLARE @LogFileLogicalName as sysname
    -- Alter the database to simple recovery
    SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY SIMPLE'
    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
    SET @SqlStatement = 'ALTER DATABASE ' + DB_NAME() + ' SET RECOVERY 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.

Thanks.

Best Answer

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.

Related Topic