usp_ShrinkAllLogs

/*************** Script 1 ****************/USE [master]
GO

/****** Object: StoredProcedure [dbo].[usp_ShrinkAllLogs] Script Date: 10/29/2009 09:40:23 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_ShrinkAllLogs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_ShrinkAllLogs]
GO

/****** Object: StoredProcedure [dbo].[usp_ShrinkAllLogs] Script Date: 10/29/2009 09:40:23 ******/SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


Create Procedure [dbo].[usp_ShrinkAllLogs](@DBName nVarChar(75) = N'')
AS
/***********************************************************************************************
*    Procedure: usp_ShrinkAllLogs
*    Parameter: @DBName input, optional, nVarChar(75)
*    Purpose  : Truncate Logs on one or all user databases.
*    Author     : Brandon Forest
*    Created  : 10/06/2008
*    Mod Date : 08/12/2009 -- Changed to recovery model methodology for SQL 2008 compatibility.
*    Mod Date : 10/29/2009 -- Queried recovery model from sys.databases before switching to simple.
*  Mod By     : Brandon Forest
***********************************************************************************************/IF @DBName = N''
    BEGIN
        Declare @SQLCmd nVarChar(2000)
        Declare @State nVarChar(15)
        Declare curD Cursor For
            Select Name From Sys.Databases Where Name Not In ('master', 'tempdb', 'model', 'msdb')
        Open curD
        Fetch Next From curD Into @DBName
        While @@Fetch_Status = 0 
        Begin
            Set @State = (SELECT [recovery_model_desc] FROM sys.databases Where [name] = @DBName)
            BEGIN TRY
                --Set @SQLCmd = N'Backup Log @DatabaseName With Truncate_Only'
                Set @SQLCmd = N'ALTER DATABASE ' + @DBName + ' SET RECOVERY SIMPLE WITH NO_WAIT'
                Print @SQLCMD
                Execute(@SQLCmd)
            END TRY
            BEGIN CATCH
                RETURN(@@ERROR)
            END CATCH
            BEGIN TRY
                Set @SQLCmd = N'DBCC SHRINKDATABASE(' + @DBName + ',10)'
                Print @SQLCMD
                Execute(@SQLCmd)
            END TRY
            BEGIN CATCH
                RETURN(@@ERROR)
            END CATCH
            BEGIN TRY
                Set @SQLCmd = N'ALTER DATABASE ' + @DBName + ' SET RECOVERY ' + @State + ' WITH NO_WAIT'
                Print @SQLCMD
                Execute(@SQLCmd)
            END TRY
            BEGIN CATCH
                RETURN(@@ERROR)
            END CATCH
            Fetch Next From curD Into @DBName
        End
        Close curD
        Deallocate curD
    END
ELSE
    Set @State = (SELECT [recovery_model_desc] FROM sys.databases Where [name] = @DBName)
    BEGIN TRY
    --Set @SQLCmd = N'Backup Log @DatabaseName With Truncate_Only'
        Set @SQLCmd = N'ALTER DATABASE ' + @DBName + ' SET RECOVERY SIMPLE WITH NO_WAIT'
        Print @SQLCMD
        Execute(@SQLCmd)
    END TRY
    BEGIN CATCH
        RETURN(@@ERROR)
    END CATCH
    BEGIN TRY
        Set @SQLCmd = N'DBCC SHRINKDATABASE(' + @DBName + ',10)'
        Print @SQLCMD
        Execute(@SQLCmd)
    END TRY
    BEGIN CATCH
        RETURN(@@ERROR)
    END CATCH
    BEGIN TRY
        Set @SQLCmd = N'ALTER DATABASE ' + @DBName + ' SET RECOVERY ' + @State + ' WITH NO_WAIT'
        Print @SQLCMD
        Execute(@SQLCmd)
    END TRY
    BEGIN CATCH
        RETURN(@@ERROR)
    END CATCH

GO

/*************** Script 2 ****************/USE [master]
GO

/****** Object: StoredProcedure [dbo].[usp_ShrinkAllLogs] Script Date: 10/29/2009 14:42:33 ******/IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Call_ShrinkAllLogs_Metrics]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_Call_ShrinkAllLogs_Metrics]
GO

/****** Object: StoredProcedure [dbo].[usp_ShrinkAllLogs] Script Date: 10/29/2009 14:42:33 ******/SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

Create Procedure [dbo].[usp_Call_ShrinkAllLogs_Metrics](@DBName nVarChar(75) = N'')
AS
/***********************************************************************************************
*    Procedure: usp_Call_ShrinkAllLogs_Metrics
*    Parameter: @DBName input, optional, nVarChar(75)
*    Purpose     : Calls usp_ShrinkAllLogs and tracks metrics.
*    Author      : Brandon Forest
*    Created : 10/29/2009
***********************************************************************************************/Declare @DBState Table (DBName nVarchar(50), DBStateBefore nVarchar(10), DBStateAfter nVarChar(10), LogSizeBefore int, LogSizeAfter int)
Declare @SQLCmd nVarchar(500)

Insert Into @DBState
(DBName, DBStateBefore, LogSizeBefore)
SELECT a.[name] as DBName, a.[recovery_model_desc], b.size 
FROM sys.databases a
Inner Join sys.master_files b on a.database_id = b.database_id
Where b.type_desc = 'LOG' and a.name Not In('master', 'tempdb', 'msdb', 'model', 'distribution')

IF @DBName = N'' 
    BEGIN 
        Exec master.dbo.usp_ShrinkAllLogs 
    END
ELSE
    BEGIN 
        Set @SQLCmd = N'Exec master.dbo.usp_ShrinkAllLogs ' + @DBName
        Execute(@SQLCmd)
    END;
    
With cteStateAfter 
As
(
    SELECT a.[name] as DBName, a.[recovery_model_desc], b.size 
    FROM sys.databases a
    Inner Join sys.master_files b on a.database_id = b.database_id
    Where b.type_desc = 'LOG' and a.name Not In('master', 'tempdb', 'msdb', 'model', 'distribution')
)

Update @DBState
Set DBStateAfter = b.recovery_model_desc, LogSizeAfter = b.size
From @DBState a Inner Join cteStateAfter b On a.DBName = b.DBName

Select * From @DBState
GO