none
Reducing the SQL Server transaction log on the fly for production DB ? RRS feed

  • Question

  • Hi All,
    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 <YourDatabaseName>
    ------------------------------------------------------------------------------
    -- 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 )
    ------------------------------------------------------------------------------
    
    
    3. Creating maintenance plan - On Demand - for DB shrinking ?
    so what's the difference and purpose of those methods ?

    /* Server Support Specialist */
    Friday, April 15, 2011 1:24 AM

Answers

  • > Full backup (may be daily or depends on business need)
    > T-log should run on regular basis (may be 30 min or so.. based on RTO and RPO)
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, April 18, 2011 1:30 PM
    Moderator
  • Yes. that should be fine.
    You need to check with business as well if they want point in time recovery? Are they OK to loose 5 hr 59 min of data?

    Lets say backup happend at 12 noon, and someone deleted data from table at 5:30 then only option you have to restore 1200 hrs backup. Is that OK?
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, April 18, 2011 2:36 PM
    Moderator
  • Though the script in point 2 seems to be fine I wouldn't personally keep changing the recovery model for a database in a prodcution environment . Decide what is your recovery strategy in case of a failure , if you want a point in time recovery then go for setting the full recovery model and keep performing log backups at regular intervals and (shrink the log file only in case of disk running of space) .
    Just use a plain DBCC SHRINKFILE on the log file rather than any fancy scripts.
     
    If you are not looking for any point in time recovery (SLA) then go for simple recovery where only full backups are possible and then use dbcc shrinkfile only in case of disk running out of space.

     


    Thanks, Leks
    Friday, April 15, 2011 1:48 AM
    Answerer
  • Run the below queries

    use db_name
    go
    sp_helpfile
    go

    Take the name for log file and use IT in the next statement

    DBCC SHRINKFILE ('logfilename',finalsize)

    So it should be something like

    DBCC SHRINKFILE ('log_file_name',1024)

    See if the size of physical file reduces , if not then it means there are active transactions that are holding your end of logfile and
    you might have to run the below query to see what is holding your logfile

    select name , log_reuse_Wait_desc from sys.databases


    Thanks, Leks
    Friday, April 15, 2011 2:28 AM
    Answerer

All replies

  • 1. Database full backup doesn't clear or truncate the log file in FULL recovery model . ONly a transaction log backup in full recovery mode will help , and in simple recovery mode full backup or checkpoint will truncate the log

    2. Procedure seems to be fine , but remember as long as the active transactions are there in your database you might not see a physical file size reduction . The active transactions may relate to current transaction in db , long running db , replication , mirroring or cdc and so on

    3. Do not shinrk the database as whole as it also shrinks the data file there by fragmenting the index which can drasticlaly reduce your query performance


    Thanks, Leks
    Friday, April 15, 2011 1:33 AM
    Answerer
  • wow, thanks for your quick reply, so in this case my plan is to:

    1. execute the Stored Procedure in option #2

    2. after that is done I shall set the recovery mode to SIMPLE instead of FULL ?

    I'm getting low in the disk space now and worried if it will stop the database under working hours now.


    /* Server Support Specialist */
    Friday, April 15, 2011 1:38 AM
  • Just to add to Leks comment, transaction log should be scheduled of regular basis to avoid this situation. Shrinking of MDF files should be avoided.
    I have seen many customers who schedule backup and shrink operation my maintenance plan which is not desirable.
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Friday, April 15, 2011 1:46 AM
    Moderator
  • Though the script in point 2 seems to be fine I wouldn't personally keep changing the recovery model for a database in a prodcution environment . Decide what is your recovery strategy in case of a failure , if you want a point in time recovery then go for setting the full recovery model and keep performing log backups at regular intervals and (shrink the log file only in case of disk running of space) .
    Just use a plain DBCC SHRINKFILE on the log file rather than any fancy scripts.
     
    If you are not looking for any point in time recovery (SLA) then go for simple recovery where only full backups are possible and then use dbcc shrinkfile only in case of disk running out of space.

     


    Thanks, Leks
    Friday, April 15, 2011 1:48 AM
    Answerer
  • Leks, thanks for the quick reply this database is for SAP analytics only, therefore it is OK to make it as simple recovery as I'll back it up FULL nightly which mean I can always revert back to the last night backup in case anything goes wrong.

     

    at the moment I just need to shrink/commit 35 GB of transaction log because the disk space is running very low.


    /* Server Support Specialist */
    Friday, April 15, 2011 1:51 AM
  • Run the below queries

    use db_name
    go
    sp_helpfile
    go

    Take the name for log file and use IT in the next statement

    DBCC SHRINKFILE ('logfilename',finalsize)

    So it should be something like

    DBCC SHRINKFILE ('log_file_name',1024)

    See if the size of physical file reduces , if not then it means there are active transactions that are holding your end of logfile and
    you might have to run the below query to see what is holding your logfile

    select name , log_reuse_Wait_desc from sys.databases


    Thanks, Leks
    Friday, April 15, 2011 2:28 AM
    Answerer
  • I second that Leks.Changing the recovery model will break the log chain.

    Albert,

    Your script is ok but the problem is the script will not best for production system because of LSN broken.

    You can get same corparate standard script here which will not change the recovery model instead of

    do more log backup.

    http://sqlserverblogforum.blogspot.com/2011/04/shrinking-log-file-script-for-sql.html


    Muthukkumaran Kaliyamoorthy SQL DBA MyBlog-->sqlserverblogforum Please click the Mark as Answer button if a post solves your problem!
    Monday, April 18, 2011 10:09 AM
  • Balmukund, thanks for the suggestion, so I have already run the script that I posted to shrink the log file (now it is only several hundred megabytes big) and have made the transaction log backup to simple.

    SO in this case shall I just create  maintenance plan to:

    1. Full DB backup ... after that followed by
    2. Full Transaction log backup

    is that ok ?


    /* Server Support Specialist */
    Monday, April 18, 2011 1:18 PM
  • > Full backup (may be daily or depends on business need)
    > T-log should run on regular basis (may be 30 min or so.. based on RTO and RPO)
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, April 18, 2011 1:30 PM
    Moderator
  • ok, so in this case is it OK to run the full transaction log backup eventhough the DB has been set as SIMPLE mode ? and during the working hours ?
    /* Server Support Specialist */
    Monday, April 18, 2011 1:41 PM
  • You can't take log backups in simple recovery model
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, April 18, 2011 1:51 PM
    Moderator
  • oh ? so in this case I shall just run Full DB backup every 6 hours with the DB maintenance plan during the working hours ?
    /* Server Support Specialist */
    Monday, April 18, 2011 1:57 PM
  • Yes. that should be fine.
    You need to check with business as well if they want point in time recovery? Are they OK to loose 5 hr 59 min of data?

    Lets say backup happend at 12 noon, and someone deleted data from table at 5:30 then only option you have to restore 1200 hrs backup. Is that OK?
    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog: http://blogs.msdn.com/blakhani
    Team Blog: http://blogs.msdn.com/sqlserverfaq
    Monday, April 18, 2011 2:36 PM
    Moderator
  • Yes I have they accepted that and they can generate the data again.

    many thanks to all who participate in this thread.


    /* Server Support Specialist */
    Monday, April 18, 2011 11:25 PM