We are using Dotnetnuke 4.5.5 with Microsoft SQL 2005 database. We have faced the blocking issues in past and I would like to share issue & resolution tips with everyone.
Dotnetnuke has a very good logging mechanism to log error and events (audit trail). This can be easily configured per portal or globally for all portals. To edit go to Admin -> "Event Viewer" page.
By default logging is enable for every event so system creates lots of log entries. Dotnetnuke runs a scheduler to delete these log entries (configurable). This Scheduler use a store procedure "PurgeEventLog" to delete old log entries based on configuration setting. This store procedure “PurgeEventLog” causes the SQL blocking issue and ultimately bring the website down. The store procedure looks like bellow:
| PROCEDURE [dbo].[PurgeEventLog] AS DELETE FROM dbo.EventLog FROM dbo.EventLogConfig elc WHERE ( SELECT COUNT(*) FROM dbo.EventLog el WHERE el.LogConfigID = elc.ID and dbo.EventLog.LogTypeKey = el.LogTypeKey and el.LogCreateDate >= dbo.EventLog.LogCreateDate ) > elc.KeepMostRecent AND elc.KeepMostRecent<>-1 |
Incase there are lots of log entries in the EventLog table then procedure “PurgeEventLog” will take longer time to purge all those log entries and blocks other SQL users.
Resolution of the issue: We had to ask DBA to truncate the EventLog table to delete all the log entries. To test this issue you can open the even log page and it will show error that can’t load page.
To avoid this make sure the logging is disabled. Only use logging when you are working on a issue or enable it only for errors and exceptions.
Thank you for sharing. I had the same issue, your post helped to solve that easily. After having so many troubles with SQL I decided to find a good variant of data room in virtual data rooms reviews.
ReplyDelete