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
16 Comments
Anonymous
Muthu,
Its nice one.
Is there any reason to run the log backup two times or you forget to remove it
EXEC @sql1
EXEC @sql1
—
Ram
Muthukkumaran Kaliyamoorthy
Ram,
Thanks my dear friend for checking my blog scripts.
Nope it depends,
sometimes the shrinking will not shrink the log files if it has active VLF to making the VLF's to inactive. I have run the log backup twice (maybe it depends)
Jagadish
I shrink the database log file by the suggestion given by the query select name, log_reuse_wait_desc from sys.databases. if it has log backup as a field, I go with log backup. Often I used to take twice log backup to shrink, then shrink happens. I’m not clear why we go for second log backup. It is been a question mark for me since long time. Could you please explain
Muthukkumaran kaliyamoorthy
Hi Jagadish,
As long as, you do not have any active VLF in the file you can shrink. The twice is “it depends”. It may reduce or increase. Sometimes a single log backup will write all the active VLF to the data file.
vinoth
Muthu,
Excellent script.Very well written i like this procedure.
Anonymous
Hi,
Definitely it's good script even though its not shrinking the log file fully.In history table its 'nothing', If i take a log backup once again its shrinking.
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/9f978fc7-0dcb-4035-b96d-1b58a81e3b1f
Muthukkumaran Kaliyamoorthy
@Anonymous,
Yep May be your log file have active transaction that's why you can't shrink the file, nothing with the log_reuse_wait_desc. Log Backup may change the active vlf to inactive vlf.
See my twitter link Paul randal has replied regarding this.
http://twitter.com/#!/mentions
Muthukkumaran Kaliyamoorthy
@vinoth Thanks This style has taken from Brent site.
Muthukkumaran Kaliyamoorthy
@Anonymous,
Now i added the 3rd-condition.
EXEC @sql3
If the first 2 conditions are not shrink the log file ,it will check the log file size > 1000 then make a log backup once again and shrink it.
This will definitely shrink if it shows log_reuse_wait_desc="nothing".Thanks for your reply.
Anonymous
Muthu
I went for vacation da.I know about you.as i am the first person to check yours (:-
Ram
Muthukkumaran
Hey ram i know… i know.
Thanks man.
Ladainian
I told my grandmother how you hpeled. She said, bake them a cake!
Pingback:
Marielle Baza
Keep working ,impressive job!
Mary Ann
$author Thank you so much for a wonderful blog. It was such a great article. Have a great day!
priyanshi
hi muthu
what to change in script if i do not have backupmedia locally , i want to have backup log to disk=’nul’ also with compression