usp_ShrinkDBLogs

USE MASTER
GO

If exists (Select Name From Sysobjects Where Name = 'usp_ShrinkDBLogs' And Type = 'P')
    Drop Proc usp_ShrinkDBLogs
GO

Create Procedure usp_ShrinkDBLogs(@DBName nVarChar(75) = N'')
AS
/***********************************************************************************************
*    Procedure: usp_ShrinkDBLogs
*    Parameter: @DBName input, optional, nVarChar(75)
*    Purpose  : Truncate Logs on one or all user databases.
*    Author     : Brandon Forest
*    Created  : 10/6/2008
***********************************************************************************************/IF @DBName = N''
    BEGIN
        Declare @SQLCmd nVarChar(200)
        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
            BEGIN TRY
                Set @SQLCmd = N'Backup Log @DatabaseName With Truncate_Only'
                Print @SQLCMD
                Exec sp_ExecuteSQL @SQLCmd, N'@DatabaseName nVarchar(75)', @DatabaseName = @DBName
            END TRY
            BEGIN CATCH
                RETURN(@@ERROR)
            END CATCH
            BEGIN TRY
                Set @SQLCmd = N'DBCC SHRINKDATABASE(@DatabaseName,10)'
                Print @SQLCMD
                Exec sp_ExecuteSQL @SQLCmd, N'@DatabaseName nVarchar(75)', @DatabaseName = @DBName
            END TRY
            BEGIN CATCH
                RETURN(@@ERROR)
            END CATCH
            Fetch Next From curD Into @DBName
        End
        Close curD
        Deallocate curD
    END
ELSE
    BEGIN
        Backup Log @DBName With Truncate_Only
        DBCC SHRINKDATABASE(@DBName,10)
    END
GO