Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
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) –  Albert Widjaja Apr 15 '11 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 '11 at 3:01
    
yes that does the trick, thanks guys ! –  Albert Widjaja Apr 16 '11 at 4:09
    
Glad to help... –  joeqwerty Apr 16 '11 at 13:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.