Perform Log backup for all non-simple recovery databases to NUL & then shrink the Log files

You sure can come across some interesting requests as a DBA.
 
In this case the request was to quickly perform a log backup on a Dev server for each database (50+) so a log file shrink to free disk space could be performed.
 
Some of the scenario included:

  • Point in-time recovery was not necessary
  • They didn’t want to break Always On
  • The Dev server had insufficient space for the log backups

The following did the trick to backup the log files:

DECLARE @sqlcmd nvarchar(MAX);
DECLARE @db nvarchar(256);
DECLARE item_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name
 FROM sys.databases
 WHERE 1=1
 AND state_desc = 'ONLINE'
 AND is_read_only = 0
 AND recovery_model_desc <> 'SIMPLE'
ORDER BY name
OPEN item_cursor
FETCH NEXT FROM item_cursor INTO @db
WHILE @@FETCH_STATUS = 0 
BEGIN
 SET @sqlcmd = 'BACKUP LOG [' + @db + '] TO DISK = ''NUL'';'  
 EXEC (@sqlcmd )
 FETCH NEXT FROM item_cursor INTO @db
END
CLOSE item_cursor;  
DEALLOCATE item_cursor;

Next to reclaim the log file space the following was run:

DECLARE @shrink nvarchar(MAX);
DECLARE @db nvarchar(256);
DECLARE @file nvarchar(256);
DECLARE item_cursor CURSOR LOCAL FAST_FORWARD FOR
 SELECT DB_NAME(a.database_id),a.name
 FROM sys.master_files a
 WHERE 1=1
 AND a.type_desc = 'LOG'
 AND a.is_read_only <> 1
 AND a.database_id IN (SELECT b.database_id FROM sys.databases b WHERE b.state_desc = 'ONLINE' AND b.is_read_only <> 1)
OPEN item_cursor
FETCH NEXT FROM item_cursor INTO @db,@file
WHILE @@FETCH_STATUS = 0 
BEGIN
 SET @shrink = 'USE [' + @db + ']; DBCC SHRINKFILE(N''' + @file + ''',1) WITH NO_INFOMSGS;'  
 EXEC (@shrink)
 FETCH NEXT FROM item_cursor INTO @db,@file
END
CLOSE item_cursor;  
DEALLOCATE item_cursor;

Leave a Reply