As a SQL Server developer or administrator, you may often find yourself dealing with multiple client databases on your computer for testing purposes. However, managing these databases can become a challenge, especially when some of them are set to full recovery mode and their log files start to grow and consume valuable disk space.

In this article, we will discuss a method to automate the process of shrinking log files in SQL Server using a cursor. While this approach may not be the most recommended by experienced DBAs, it can be a practical solution for managing non-live databases in a testing environment.

First, let’s declare the variables needed for our script:

DECLARE @Name      varchar(50) -- database name
      , @Sqlstr    nvarchar(max) = '' -- first sql string
      , @Sqlstrx   nvarchar(max) = '' -- second sql string
      , @Log_Name  sysname -- the log name (not the file name, they can be different)

Next, we will use a cursor to loop through all the databases (excluding system databases) and shrink their log files:

DECLARE db_cursor CURSOR
FOR SELECT name
    FROM MASTER.dbo.sysdatabases
    WHERE name NOT IN ( 'master' , 'model' , 'msdb' , 'tempdb' )
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Name

Within the loop, we will retrieve the correct log name for each database and then shrink the log file:

WHILE @@Fetch_Status = 0
BEGIN
    -- The USE statement is necessary here to get the correct log name
    SET @Sqlstrx += 'USE [' + @Name + ']' + CHAR(13)
    -- Retrieve the log name (not the file name)
    SET @Sqlstrx += 'SELECT @Log_Name = name FROM sys.database_files WHERE type_desc = ''Log''' + CHAR(13)
    -- Execute the @Sqlstrx and declare @Logname as an output variable
    EXEC sp_executesql
         @Sqlstrx
       , N'@Log_Name sysname output'
       , @Log_Name = @Log_Name OUTPUT;

    -- Shrink the log file
    SET @Sqlstr += 'USE [' + @Name + ']' + CHAR(13)
    SET @Sqlstr += 'ALTER DATABASE [' + @Name + '] SET RECOVERY SIMPLE;' + CHAR(13)
    SET @Sqlstr += 'DBCC SHRINKFILE ([' + @Log_Name + '], 1);' + CHAR(13)
    SET @Sqlstr += 'ALTER DATABASE [' + @Name + '] SET RECOVERY FULL;' + CHAR(13)
    EXECUTE sp_executesql @Sqlstr

    FETCH NEXT FROM db_cursor INTO @Name
END

CLOSE db_cursor
DEALLOCATE db_cursor

It’s important to note that this script should only be used in non-live databases for testing purposes. Running it on production databases can have unintended consequences and should be avoided.

While using a cursor may not be the most efficient method, it can be a practical solution for automating the process of shrinking log files in a testing environment. However, it’s always recommended to explore alternative approaches and seek feedback from experienced DBAs to improve your SQL skills.

We hope this article has provided you with a useful method for managing log file growth in SQL Server. Feel free to share your thoughts and suggestions in the comments section below.