-- 1 - SCRIPT TO CHECK THE CURRENT DATABASE SIZE IN MB

SELECT  DB_NAME() AS DataBaseName, 
	Name AS FileName, 
	(SIZE / CONVERT(REAL, 1048576/(SELECT LOW FROM master.dbo.spt_values WHERE number = 1 AND type = 'E' ))) as SizeInMB,
	FileName AS Location
FROM	sysfiles

------------------

-- 2 - SCRIPT TO CREATE THE CLEAN UP PROCEDURE

CREATE PROCEDURE dnnHeroCleanUp
AS
BEGIN
	DECLARE @Recovery_Model_Desc nvarchar(20)
	DECLARE @SQL_Script nvarchar(100)

	DECLARE @Database nvarchar(max)
	DECLARE @DatabaseFileName nvarchar(max)
	DECLARE @LogFileName nvarchar(max)

	-- get the database name	
	SET @Database = DB_NAME()

	-- get the database file name
	SELECT	@DatabaseFileName = name
	from	SysFiles
	WHERE	status & 0x40 <> 0x40 AND
		UPPER(filegroup_name(groupid)) = 'PRIMARY'

	-- get the log file name
	SELECT	@LogFileName = name
	FROM	SysFiles
	WHERE	Status & 0x40 = 0x40

	-- get the current recover mode
	SELECT	@Recovery_Model_Desc = UPPER(Recovery_Model_Desc)
	FROM	Sys.Databases
	WHERE	Name = @Database

	-- if recovery mode is originally set to FULL then alter to SIMPLE so we can trucate
	IF @Recovery_Model_Desc = 'FULL'
	BEGIN
		SET @SQL_Script = 'ALTER DATABASE [' + @Database + '] SET RECOVERY SIMPLE'
		EXECUTE (@SQL_Script)
	END
	
	-- clean up the ScheduleHistory table
	TRUNCATE TABLE {objectQualifier}ScheduleHistory 

	-- clean up the EventLog table
	TRUNCATE TABLE {objectQualifier}EventLog 

	-- clean up the SiteLog table
	TRUNCATE TABLE {objectQualifier}SiteLog

	-- shrink the database file
	SET @SQL_Script = 'DBCC SHRINKFILE ([' + RTRIM(LTRIM(@DatabaseFileName)) + '], 1)'
	EXECUTE (@SQL_Script)

	-- shrink the log file
	SET @SQL_Script = 'DBCC SHRINKFILE ([' + RTRIM(LTRIM(@LogFileName)) + '], 1)'
	EXECUTE (@SQL_Script)
	
	--if recovery mode is originally set to FULL then set it back to FULL as we have temporarily changed it to SIMPLE
	IF @Recovery_Model_Desc = 'FULL'
	BEGIN
		SET @SQL_Script = 'ALTER DATABASE [' + @Database + '] SET RECOVERY FULL'
		EXECUTE (@SQL_Script)
	END
	
END

----------------

-- 3 - SCRIPT TO RUN THE CLEAN UP PROCEDURE

exec dnnHeroCleanUp

----------------

-- 4 - SCRIPT TO RUN THE DB SIZE BEFORE

SELECT  (SUM(SIZE) / CONVERT(REAL, 1048576/(SELECT LOW FROM master.dbo.spt_values WHERE number = 1 AND type = 'E' ))) as SizeInMB_BEFORE
FROM	sysfiles

----------------

-- 5 - SCRIPT TO RUN THE DB SIZE AFTER

SELECT  (SUM(SIZE) / CONVERT(REAL, 1048576/(SELECT LOW FROM master.dbo.spt_values WHERE number = 1 AND type = 'E' ))) as SizeInMB_AFTER
FROM	sysfiles