Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server Blog Forum

My name is Muthukkumaran Kaliyamoorthy and I am living in India (Chennai). I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server, and I’m specialized in Administration and Performance tuning.

Shrinking the log file script for SQL server database

Shrinking the log file script for SQL server database                               

This script mainly for a DBA’s, because most of the time the log file grow again and again and eats up all the disk space and finally an application goes down.

Errors from SQL server error log:

Error: 9002, Severity: 17, State: 2.

 

The transaction log for database ‘test’ is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

If you got an error “Error: 9002, Severity: 17, State: 2.” like this go and check the log_reuse_wait_desc column in sys.databases by running below command and see what value it returns.

 

SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases name='test'


Shrinking the files is not recommended but, anyway to prevent the database down we can shrink the log files at least some crucial situation. Try to avoid shrinking database files as much as possible. See my previous post How to avoid the shrinking.

--history propose
USE [master]
GO
CREATE TABLE [DBO].[tbl_get_logfiles_details](
            [dbname] [SYSNAME] NOT NULL,
            [backup_location] [SYSNAME]NOT NULL,
            [name] [SYSNAME] NOT NULL,
            [log_size] [INT] NULL,
            [log_usedsize] [INT] NULL,
            [log_usedsize%] [DECIMAL](18, 0) NULL,
            [log_reuse_wait_desc] [SYSNAME] NOT NULL,
            [freespace] [INT] NULL,
            [shrink_status] [SYSNAME] NULL,
            [date] [DATETIME] NULL
)

Note:

 

This procedure only works with the following criteria.

  • A backup disk can be a local disk.
  • The log backup has been done at least one time for the all databases.
  • The database recovery model does not simple.

This coding style has been taken from Support-Matrix Brent Ozar websites thanks to Brent.

The 1 GB log file size is rough calculation and you can alter the procedure whatever you want.

Important note: This procedure will not shrink the log file if it has an active transaction (VLF).

 

CREATE PROCEDURE Usp_dba_shrink_logfiles
/*
Summary:        Shrinking the log files to prevent the disk running out of space
Contact:        Muthukkumaran Kaliyamoorhty SQL DBA
Description:    This Sproc will take the each of the log files one by
one and check their size more than 1GB then this will do two kinds of work.
1. Check the log filesize >1GB AND log reuse wait type is not log backup then shrink
2. Check the log filesize >1GB AND log reuse wait type is log backup AND the
drive size greater than backup size then take a backup AND shrink it

ChangeLog:
Date                          Coder                                                    Description
2011-03-11                 Muthukkumaran Kaliyamoorhty               created
2011-04-18                 Muthukkumaran Kaliyamoorhty               added aditional part for make the VLF to inactive

*******************All the SQL keywords should be written in upper case*********************/
AS
  BEGIN
      SET nocount ON

      DECLARE @dbname SYSNAME
      DECLARE @minid INT
      DECLARE @maxid INT
      DECLARE @filename SYSNAME
      DECLARE @log_filesize INT
      DECLARE @sql SYSNAME
      DECLARE @sql1 SYSNAME
      DECLARE @sql2 SYSNAME
      DECLARE @sql3 SYSNAME
      DECLARE @sql4 SYSNAME
      DECLARE @size INT
      DECLARE @log_used_size INT
      DECLARE @bak_locatiON SYSNAME
      DECLARE @freespace INT
      DECLARE @logwait_type SYSNAME

      ---------------------------------------------------------------------------------------------
      --inserting the drive space.
      ---------------------------------------------------------------------------------------------
      CREATE TABLE #drive_size
        (
           drive     CHAR(1),
           freespace INT
        )

      INSERT INTO #drive_size
      EXEC MASTER..Xp_fixeddrives

      DECLARE @tbl_loop TABLE (
        id     INT IDENTITY,
        dbname SYSNAME)

      INSERT INTO @tbl_loop
      SELECT Db_name(f.database_id)
      FROM   MASTER.sys.databases d
             JOIN MASTER.sys.dm_os_performance_counters p
               ON ( d.name = p.instance_name )
             JOIN MASTER.sys.dm_os_performance_counters s
               ON ( d.name = s.instance_name )
             JOIN MASTER.sys.master_files f
               ON ( d.database_id = f.database_id )
             JOIN msdb.dbo.backupset bs
               ON ( Db_name(f.database_id) = bs.database_name )
             JOIN msdb.dbo.backupmediafamily bf
               ON ( bs.media_set_id = bf.media_set_id )
             JOIN #drive_size ds
               ON ( ds.drive = LEFT(bf.physical_device_name, 1) )
      WHERE  p.counter_name LIKE 'log file(s) used size (kb)%'
             AND s.counter_name LIKE 'log file(s) size (kb)%'
             AND f.type_desc = 'log'
             AND f.database_id NOT IN( 1, 2, 3, 4 )
             AND f.size / 128 > 1024
             AND bs.TYPE = 'l'
      GROUP  BY Db_name(f.database_id),
                f.name,
                s.cntr_value / 1024,
                p.cntr_value / 1024,
                CAST(CAST(p.cntr_value AS FLOAT) / CAST(s.cntr_value AS FLOAT)AS
                     DECIMAL(18, 2))
                * 100,
                d.log_reuse_wait_desc,
                ds.freespace
      ORDER  BY s.cntr_value / 1024

      SELECT @minid = MIN(id)
      FROM   @tbl_loop

      SELECT @maxid = MAX(id)
      FROM   @tbl_loop

      ---------------------------------------------------------------------------------------------
      --Archive the log file size for future reference
      ---------------------------------------------------------------------------------------------
      INSERT INTO MASTER.dbo.tbl_get_logfiles_details
      SELECT Db_name(f.database_id),
             MAX(bf.physical_device_name),
             f.name,
             s.cntr_value / 1024,
             p.cntr_value / 1024,
             CAST(CAST(p.cntr_value AS FLOAT) / CAST(s.cntr_value AS FLOAT)AS
                  DECIMAL(
                  18, 2))
             * 100,
             d.log_reuse_wait_desc,
             ds.freespace,
      'Shrinklog',
      Getdate()
      FROM   MASTER.sys.databases d
             JOIN MASTER.sys.dm_os_performance_counters p
               ON ( d.name = p.instance_name )
             JOIN MASTER.sys.dm_os_performance_counters s
               ON ( d.name = s.instance_name )
             JOIN MASTER.sys.master_files f
               ON ( d.database_id = f.database_id )
             JOIN @tbl_loop t
               ON ( Db_name(f.database_id) = t.dbname )
             JOIN msdb.dbo.backupset bs
               ON ( Db_name(f.database_id) = bs.database_name )
             JOIN msdb.dbo.backupmediafamily bf
               ON ( bs.media_set_id = bf.media_set_id )
             JOIN #drive_size ds
               ON ( ds.drive = LEFT(bf.physical_device_name, 1) )
      WHERE  p.counter_name LIKE 'log file(s) used size (kb)%'
             AND s.counter_name LIKE 'log file(s) size (kb)%'
             AND f.type_desc = 'log'
             AND f.database_id NOT IN( 1, 2, 3, 4 )
             AND f.size / 128 > 1024
             AND bs.TYPE = 'l'
      GROUP  BY Db_name(f.database_id),
                f.name,
                s.cntr_value / 1024,
                p.cntr_value / 1024,
                CAST(CAST(p.cntr_value AS FLOAT) / CAST(s.cntr_value AS FLOAT)AS
                     DECIMAL(18, 2))
                * 100,
                d.log_reuse_wait_desc,
                ds.freespace
      ORDER  BY s.cntr_value / 1024

      WHILE ( @minid <= @maxid )
        BEGIN
            SELECT @dbname = Db_name(f.database_id),
                   @bak_locatiON = MAX(bf.physical_device_name),
                   @filename = f.name,
                   @log_filesize = s.cntr_value / 1024,
                   @size = p.cntr_value / 1024,
                   @log_used_size = CAST(CAST(p.cntr_value AS FLOAT) / CAST(
                                         s.cntr_value AS FLOAT)
                                         AS DECIMAL(18, 2))
                                    * 100,
                   @logwait_type = d.log_reuse_wait_desc,
                   @freespace = ds.freespace
            FROM   MASTER.sys.databases d
                   JOIN MASTER.sys.dm_os_performance_counters p
                     ON ( d.name = p.instance_name )
                   JOIN MASTER.sys.dm_os_performance_counters s
                     ON ( d.name = s.instance_name )
                   JOIN MASTER.sys.master_files f
                     ON ( d.database_id = f.database_id )
                   JOIN @tbl_loop t
                     ON ( Db_name(f.database_id) = t.dbname )
                   JOIN msdb.dbo.backupset bs
                     ON ( Db_name(f.database_id) = bs.database_name )
                   JOIN msdb.dbo.backupmediafamily bf
                     ON ( bs.media_set_id = bf.media_set_id )
                   JOIN #drive_size ds
                     ON ( ds.drive = LEFT(bf.physical_device_name, 1) )
            WHERE  p.counter_name LIKE 'log file(s) used size (kb)%'
                   AND s.counter_name LIKE 'log file(s) size (kb)%'
                   AND f.type_desc = 'log'
                   AND f.database_id NOT IN( 1, 2, 3, 4 )
                   AND f.size / 128 > 1024
                   AND bs.TYPE = 'l'
                   AND t.id = @minid
            GROUP  BY Db_name(f.database_id),
                      f.name,
                      s.cntr_value / 1024,
                      p.cntr_value / 1024,
                      CAST(CAST(p.cntr_value AS FLOAT) / CAST(
                           s.cntr_value AS FLOAT)AS
                           DECIMAL(18, 2))
                      * 100,
                      d.log_reuse_wait_desc,
                      ds.freespace
            ORDER  BY s.cntr_value / 1024

            ---------------------------------------------------------------------------------------------
            --Check the log filesize >1GB AND log reuse wait type is not log backup then shrink.
            ---------------------------------------------------------------------------------------------
            IF( @log_filesize >= 1024
                AND @logwait_type <> 'log_backup' )
              BEGIN
                  CHECKPOINT

                  SET @sql='EXEC (''USE[' + @dbname + '];DBCC SHRINKFILE(' +
                           @filename
                           +
                           ')'')'

                  EXEC @sql
              END
            ---------------------------------------------------------------------------------------------
            --Check the log filesize >1GB AND log reuse wait type is log backup AND the drive size
            -- greater than backup size then take a backup AND shrink it.
            ---------------------------------------------------------------------------------------------
            ELSE
              IF( @log_filesize >= 1024
                  AND @logwait_type = 'log_backup'
                  AND @log_filesize < @freespace )
                BEGIN
                    SET @sql1='BACKUP LOG [' + @dbname + '] TO DISK=''' +
                              @bak_locatiON
                              +
                              ''''

                    EXEC @sql1

                    EXEC @sql1

                    SET @sql2='EXEC (''USE[' + @dbname + '];DBCC SHRINKFILE(' +
                              @filename +
                              ')'')'

                    EXEC @sql2

                    UPDATE MASTER.dbo.tbl_get_logfiles_details
                    SET    shrink_status = 'Shrink&Bak'
                END
              ---------------------------------------------------------------------------------------------
              --If the first two conditions are not reduce the log file size because of active VLF,
              --then take a log backup again and mark the active VLF to inactive VLF then shrink it.
              ---------------------------------------------------------------------------------------------
              ELSE
                IF( @log_filesize >= 1000
                    AND @log_filesize < @freespace )
                  BEGIN
                      SET @sql3='BACKUP LOG [' + @dbname + '] TO DISK=''' +
                                @bak_locatiON +
                                ''''

                      EXEC @sql3

                      EXEC @sql3

                      SET @sql4='EXEC (''USE[' + @dbname + '];DBCC SHRINKFILE('
                                +
                                @filename
                                +
                                ')'')'

                      EXEC @sql4

                      UPDATE MASTER.dbo.tbl_get_logfiles_details
                      SET    shrink_status = 'Shrink&Bak_3'
                  END

            SET @minid=@minid + 1
        END
  END

Comments

Leave a comment on the original post [www.sqlserverblogforum.com, opens in a new window]

Loading comments...