Overview

The DNN event log stores each exception and all other registered events in the database. If these logs become too large, you can run into performance issues. If you are running SQL Server Express, you may be prevented from logging in and see the following error in your site logs:

Could not allocate space for object [object_name] in database [database_name] because the [filegroup_name] filegroup is full

There is a scheduler job that you should have enabled to ensure the table doesn't grow too large and cause performance issues. However, if the job is turned off or there were many events between executions of the scheduler, the cleanup job (and even a manual deletion command) might time out. 

In these cases, truncating the table (deleting the contents of the table without logging) may be the only option. While it was easy to run this command before DNN 7.4, in 7.4.0 and above the EventLog table was split into three tables which are linked by Foreign Key constraints. These constraints need to be dealt with in order to truncate the table.

Prerequisites

• Platform/Evoq 7.4 and above
• Access to the SQL Server or a SuperUser account

Diagnosis

If you are running into performance issues due to database table size

To find out the relative size of your tables, you can run this SQL query in SSMS or the SQL console against your site's database:

SELECT t.NAME AS TableName
	,t.object_id AS ObjectID
	,max(p.rows) AS NumberofRows
        ,SUM(a.used_pages) * 8 AS UsedSpaceKB
	,nullif(max(isnull(usage.last_user_update, cast(0 AS DATETIME))), cast(0 AS DATETIME)) AS LastUpdated
FROM sys.tables t
LEFT JOIN sys.dm_db_index_usage_stats usage ON usage.object_id = t.object_id
INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID
	AND i.index_id = p.index_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE t.NAME NOT LIKE 'dt%'
	AND t.is_ms_shipped = 0
	AND i.OBJECT_ID > 255
	AND p.index_id < 2
GROUP BY t.Name
	,t.object_id
ORDER BY 3 DESC
	,4 DESC

If you're seeing tables that are larger than 100,000 rows, it's worth investigating those tables to find out why they're so big. If those are log tables, they can likely be cleared unless there is some specific reason to keep them. You can see a more detailed breakdown of table size and expectations in the article Gathering environmental information to evaluate Server-Side Performance.

The tables we're concerned with for this article are EventLog and ScheduleHistory.

If you encounter a hard database size limit from SQL Server Express

SQL Server Express imposes a hard 10GB limit on database size. If you reach it, you may see the following error in your site logs, which may accompany a failure to log in:

Could not allocate space for object [object_name] in database [database_name] because the [filegroup_name] filegroup is full

You can run the following script to check your overall database size:

SELECT DB_NAME(database_id) AS [database]
	,CAST(SUM(CASE WHEN type_desc = 'ROWS' THEN size ELSE 0 END) * 8. / 1024 AS DECIMAL(8, 2)) AS rowSizeMB
	,CAST(SUM(size) * 8. / 1024 AS DECIMAL(8, 2)) AS TotalSizeMB
FROM sys.master_files WITH (NOWAIT)
WHERE database_id = DB_ID()
GROUP BY database_id

Solution

NOTE: A database backup should be performed before running the attached script. Additionally, it's advisable that this is done outside of business hours as the process could take some time to complete. Always test database edits in a safe environment first.

1. Access the Settings > SQL Console or SQL Server Management Studio
2. Execute the attached script 
3. Run the query: TRUNCATE TABLE ScheduleHistory
4. Clear Cache

The logs should be completely cleared out.

Testing

To test that the logs have been cleared out:

1. Go to Manage > Admin Logs to see if the Event logs are empty.
2. Go to Settings > Scheduler > History to see if the Schedule History is empty.