Forum

Hi Aderson

Thanks for this good video, but I tried to run the script on my site

DNN : 7.2.1

but I got the following error when I tried to create the stored procedure

System.ArgumentException: Parameter '@Recovery_Model_Desc' specified but none of the passed arguments have a property with this name (in 'CREATE PROCEDURE dnnHeroCleanUp AS

hope you can give me solution for this as I need to shrink my database urgently

Yes, I took this part only

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

This seems to be a limitation in DNN7 that you cannot use @ symbols in queries that you post under Host/SQL.

See this issue: https://dnntracker.atlassian.net/brow...

You can potentially take the procedure and break it down into smaller parts and execute them manually instead of via an stored procedure.

I will revisit this procedure at some in the future so I can address this as well.

For now you may want to try to run these individually each line at a time. I recommend a backup before doing that.

-- clean up the ScheduleHistory table
TRUNCATE TABLE {objectQualifier}ScheduleHistory 
go


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


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


-- shrink the database file
DBCC SHRINKFILE ([emideast_db_data], 1)
go


-- shrink the log file
DBCC SHRINKFILE ([emideast_db_log], 1)
go

Best regards,

Aderson