Advertisements
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
