I have about 25-30 client databases on my computer for testing purposes when I support or do new development for them. Unfortunately, the databases were set up with simple recovery for some and full recovery for others. The simple recovery databases are not an issue for this purpose, but the full recovery databases have a tendency to grow in the log files and clog up my hard drive.

I had to continuously go and check which log files were bloated and shrink them till one day I decided to automate the process. Now I am not as sharp as some of you so I used what I knew best. I used what I know will most probably be crucified by the DBAs here. I used a cursor.

I have done in-depth research on this matter because I did not know how to do this. I knew one thing and that is I had to find a way to automate this process because going through all these databases and shrinking them one by one was not an option for me anymore. The cursor was the only way I could find to do this.

Positive criticism is welcome. I am open to learning new ways of doing SQL. Let us keep one thing in mind, these are not live databases and therefore I can use a script like this to test with. I have run this script on my local databases and had no issues. It actually worked very well.

First, of all, we need to declare the variables for this 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)

Let us start the cursor. We need a list of databases, excluding system databases, to shrink their logs.

-- We need to declare the cursor
DECLARE db_cursor CURSOR
-- we need to get a list of all the databases excluding the system database in the FOR statement
FOR SELECT name
    FROM MASTER.dbo.sysdatabases
    WHERE name NOT IN ( 'master' , 'model' , 'msdb' , 'tempdb' )
-- open the cursor to use the database names in the select
OPEN db_cursor
-- now get the first record from the cursor into the variable
FETCH NEXT FROM db_cursor INTO @Name

Now we will start looping through all the database names from the select.

WHILE @@Fetch_Status = 0
BEGIN
    --The USE statement is necessary here in order to get the correct log name
    SET @Sqlstrx+='USE [' + @Name + ']' + CHAR(13)
    --Now to get the log name. Note: not the file name. I saw that some file names are different from the 
    --log names
    SET @Sqlstrx+='select @Log_Name = name from sys.database_files ' + CHAR(13)
    SET @Sqlstrx+='where type_desc = ' + '''Log''' + CHAR(13)
    --We execute the @Sqlstrx and declare @Logname as an output variable
    --then pass @Log_Name to the output variable
    EXEC sp_executesql
         @Sqlstrx
       , N'@Log_Name sysname output'
       , @Log_Name = @Log_Name OUTPUT;

We can now work with the output variable and shrink the log.

---------------------------------------------------------------------------------------------------
    --The USE statement is necessary again to have the right database to shrink the log for.
    SET @Sqlstr+='USE [' + @Name + ']' + CHAR(13)
    --The database must be set to simple recovery mode to shrink
    SET @Sqlstr+='ALTER DATABASE [' + @Name + '] ' + CHAR(13)
    SET @Sqlstr+='SET RECOVERY SIMPLE; ' + CHAR(13)
    --The following code will shrink the log  to the specified size.
    SET @Sqlstr+='DBCC SHRINKFILE ([' + @Log_Name + '], 1); ' + CHAR(13)
    --Now you can set the database back to full recovery mode
    SET @Sqlstr+='ALTER DATABASE [' + @Name + '] ' + CHAR(13)
    SET @Sqlstr+='SET RECOVERY FULL; ' + CHAR(13)
    -- Execute the @Sqlstr set up above
    EXECUTE sp_executesql
            @Sqlstr
    -- Get the next record from the cursor
    FETCH NEXT FROM db_cursor INTO @Name
END
--When the loop is finished we can close and deallocate the cursor
CLOSE db_cursor
DEALLOCATE db_cursor

This dynamic query took a great portion of my life to finish. I did this in my spare time because my colleagues was not positive that this would be a safe and good query to run on my databases. The more they doubted this query the more adamant I was to finish it and to run it. I am just thankful that it did not have a negative impact on my databases.

I thank SSC for making it possible for me to share this with you. There was a long time that I could not spend time to contribute anything here but that has changed now and I hope to be more active again on this website.

Below is the full query for those interested. I look forward to all your comments.

DECLARE @Name      varchar(50)
      , @Sqlstr    nvarchar(max) = ''
      , @Sqlstrx   nvarchar(max) = ''
      , @Log_Name  sysname
-------------------------------------------------------------------------------------
-- I am doing the cursor to get all the databases one by one (excluding system databases)
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
WHILE @@Fetch_Status = 0
BEGIN
    --The USE statement is necessary here in order to get the correct log name
    SET @Sqlstrx+='USE [' + @Name + ']' + CHAR(13)
    --Now to get the log name. Note: not the file name
    SET @Sqlstrx+='select @Log_Name = name from sys.database_files ' + CHAR(13)
    SET @Sqlstrx+='where type_desc = ' + '''Log''' + CHAR(13)
    EXEC sp_executesql
         @Sqlstrx
       , N'@Log_Name sysname output'
       , @Log_Name = @Log_Name OUTPUT;
--Now that we have the log name we can go ahead and shrink the log
    ---------------------------------------------------------------------------------------------------
    --The USE statement is necessary again to have the right database to shrink the log for.
    SET @Sqlstr+='USE [' + @Name + ']' + CHAR(13)
--The database must be set to simple recovery mode th shrink
    SET @Sqlstr+='ALTER DATABASE [' + @Name + '] ' + CHAR(13)
    SET @Sqlstr+='SET RECOVERY SIMPLE; ' + CHAR(13)
--The following code will shrink the log  to the specified size.
    SET @Sqlstr+='DBCC SHRINKFILE ([' + @Log_Name + '], 1); ' + CHAR(13)
--Now tou can set the database back to full recovery mode
    SET @Sqlstr+='ALTER DATABASE [' + @Name + '] ' + CHAR(13)
    SET @Sqlstr+='SET RECOVERY FULL; ' + CHAR(13)
    EXECUTE sp_executesql
            @Sqlstr
    FETCH NEXT FROM db_cursor INTO @Name
END
CLOSE db_cursor
DEALLOCATE db_cursor