usp_ShrinkAllLogs
This is an update to my previous script usp_ShrinkDBLogs.
The first script records the recovery mode of the database, changes it to simple, does a DBCC Shrinkdatabase, and then returns the recovery to the recorded mode. As before, you can use this script with a database name as the input parameter for a single database, or no parameters for all the databases.
The second script usp_Call_ShrinkAllLogs_Metrics, will call usp_ShrinkDBLogs and track both the recovery type and database size on disk from before and after the Shrink.
Note: Once this script has been run, full database backup(s) must be made to reinitialize the transaction Log Sequence Number (LSN).
[email protected]
Swim to Live, Live to Surf.
/*************** 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 
