How to truncate DNN log table

 

DotNetNuke has a tendency to fill up your database with eventlogs. The following SQL query will clear those logs and usually clear up a lot of space. 

 

First you'll need to connect to your database through SMSS: https://support.discountasp.net/kb/a1426/how-to-configure-sql-server-management-studio-to-connect-to-a-sql-server.aspx

 

Once you connected you'll want to click the plus sign to expand the database then click the plus sign next to "Databases". Then right click on the database and click "New Query".

 

 

 

 

 

Then copy and paste the following query:

 

IF EXISTS (SELECT * FROM sys.sysobjects WHERE id = object_id(N'dbo.[sys_currentDNNVersion]') AND Type = N'FN')
	DROP FUNCTION dbo.[sys_currentDNNVersion]
GO
-- --------- create tooling: --------- 

CREATE FUNCTION dbo.[sys_currentDNNVersion]()
	RETURNS Int
AS	
BEGIN
	DECLARE @Vers Int;
	SELECT Top(1) @Vers = Major * 10000 + Minor * 100 + Build FROM dbo.[Version] ORDER BY CreatedDate DESC;
	RETURN @Vers;
END
GO

IF dbo.[sys_currentDNNVersion]() >= 70400 BEGIN
	-- Drop Foreign Key Constraints:
	DECLARE @fkName nVarChar(100) = Null;
	SELECT @fkName = name FROM sys.foreign_keys 
	 WHERE parent_object_id = OBJECT_ID(N'dbo.[ExceptionEvents]')
	   AND Object_id IN (SELECT constraint_object_id  
	                      FROM  sys.foreign_key_columns F 
						  JOIN  sys.columns C ON F.parent_object_id = C.object_id AND F.parent_column_id = C.column_ID 
						  WHERE C.Name = N'LogEventID');
	IF Not @fkName Is Null
		Exec(N'ALTER TABLE dbo.[ExceptionEvents] DROP CONSTRAINT [' + @fkName +'];');

	SET @fkName = Null;
	SELECT @fkName = name FROM sys.foreign_keys 
	 WHERE parent_object_id = OBJECT_ID(N'dbo.[EventLog]')
	   AND Object_id IN (SELECT constraint_object_id  
	                      FROM  sys.foreign_key_columns F 
						  JOIN  sys.columns C ON F.parent_object_id = C.object_id AND F.parent_column_id = C.column_ID 
						  WHERE C.Name = N'ExceptionHash');
	IF Not @fkName Is Null
		Exec(N'ALTER TABLE dbo.[EventLog] DROP CONSTRAINT [' + @fkName +']')
END
GO

-- Truncate tables:
IF dbo.[sys_currentDNNVersion]() >= 70400 BEGIN
	TRUNCATE TABLE dbo.[Exceptions]
	TRUNCATE TABLE dbo.[ExceptionEvents]
	TRUNCATE TABLE dbo.[EventLog]
END ELSE
	TRUNCATE TABLE dbo.[EventLog]
GO

IF dbo.[sys_currentDNNVersion]() >= 70400 BEGIN
	-- Recreate Foreign Key Constraints (using common naming):
	ALTER TABLE dbo.[ExceptionEvents] 
	  WITH CHECK ADD CONSTRAINT [FK_ExceptionEvents_EventLog] 
		FOREIGN KEY([LogEventID])
		REFERENCES dbo.[EventLog] ([LogEventID])
	  ON DELETE NO ACTION;
	  
	ALTER TABLE dbo.[EventLog] 
	  WITH CHECK ADD CONSTRAINT [FK_EventLog_Exceptions] 
		FOREIGN KEY([ExceptionHash])
		REFERENCES dbo.[Exceptions] ([ExceptionHash])
	  ON DELETE CASCADE;
END
GO

DROP FUNCTION dbo.[sys_currentDNNVersion]
GO