2

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.

Improve this question

1 Answer 1

Reset to default
3

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.

Improve this answer
3
  • 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) Commented 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! Commented Apr 15, 2011 at 3:01
  • Glad to help... Commented Apr 16, 2011 at 13:09

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.