Skip to main content

PurgeEventLog – Cause SQL Blocking in Dotnetnuke Website

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.

Labels:

Comments

  1. John Barness6:32 AM, May 16, 2016

    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

Post a Comment

Popular posts from this blog

SharePoint WebPart Error - Unable to add selected web part (s) – MOSS 2007

ISSUE: Recently I faced this issue that when I try to deploy my WebPart.  Unable to add selected web part(s). WebPartName: Cannon unregister UpdatePanel with ID ‘ctl00RTMPanel’ since it was not registerd with the ScripManager. This might occure if the UpdatePanel was removed from the control tree and later added again, which is not supported. Parameter name: updatePanel.   I faced this issue twice and both the time the issue was not what display here. This could be sure to any error in the WebPart. Following are list resolution from multiple instances.   RESOLUTION: 1. Check the WebPartName.webpart file. Check whether you have a proper PublicKeyToken value. When I face the issue the value was PublicKeyToken=$PublicKeyToken$. If you have used WSP builder then in the Visual Studio you will find this file at \WebPartsProject\12\TEMPLATE\FEATURES\WebPartName\WebPartName.webpart. Sometime when you create a new WebPart using WSP builder then you do not get
4 comments
Read More >>>

TIME – 25 Best Blogs of 2009

Every year Time makes a list of best blogs in the world. They chose 25 blogs from millions of blogs in the world, spanning politics, housekeeping, astronomy and everything in between. Below if a direct link to the TIME website for the list. I also copied it to easy comparison of last year best with this year best blogs.   25 Best Blogs of 2009     25 Best Blogs 2008   25 Best Blogs of 2009 Talking Points Memo The Huffington Post Lifehacker Metafilter The Daily Dish by Andrew Sullivan Freakonomics BoingBoing Got2BeGreen Zen Habits The Conscience of a Liberal: Paul Krugman Crooks and Liars Generación Y Mashable Slashfood Official Google Blog synthesis bleat /Film Seth Godin's Blog Deadspin: Spor
Post a Comment
Read More >>>

Microsoft 2007 - Microsoft Technology That Defines a New Decade

Last year Microsoft came with VS.NET 2005, SQL Server 2005 etc. VS.NET 2005 was a major change over VS.NET 2003 in terms of feature, web controls and the way you program. Same is true for SQL Server 2005 you can now use C# or VB.NET to create store procedures and store procedures could be a .NET class. So no more missing of Oracle package in SQL Server. Be prepared for much more then this as Microsoft is now changing everything. Year 2007 Microsoft will bring a new Operating System named Vista (formerly code-named "Longhorn") First Look... that have completly new file system named WinFs and 1000s of new features. Vista will also come with WinFX which is set of next-generation managed APIs provided by Microsoft. Microsoft have created a new section in their MSDN website [ View ... ] so that people can start preparing for the next generation i.e. 2007. Here you can get information on Windows Presentation Foundation WPF (formerly code named "Avalon") provides the fo
Post a Comment
Read More >>>