How to Keep ELMAH_Error Table Rows Getting Too Big by Automatically Deleting Rows

Prevent Log errors from getting Too Big in ELMAH_Error SQL Server Table

I recently ran into an issue with the ELMAH Error logger Table getting way TOO Big in my SQL Server database.  The table row size in the ELMAH_Error table had over 200,000 rows. This was way more than I ever needed to look at, and was causing other issues with my SQL Server database.  So I needed to change the ELMAH_LogERROR stored procedure so that it would automatically delete error log data when the TOTAL Row Count was over 10000 rows, while still keeping the most recent 10000 records for me to be able to review the error messagees. You could very easily change this to 1,000 or some other number depending on how many rows you need to look at in your ELMAH_Error table. NOTE: I'm using ELMAH version 1.2.2 in the example below.

Here's the ELMAH_LogError stored procedure code, that will automatically delete records from the ELMAH_Error table when the row size is over 10,000 rows:

    @Application NVARCHAR(60),
    @Host NVARCHAR(30),
    @Type NVARCHAR(100),
    @Source NVARCHAR(60),
    @Message NVARCHAR(500),
    @User NVARCHAR(50),
    @AllXml NTEXT,
    @StatusCode INT,
    @TimeUtc DATETIME



		DECLARE  @TotalRowCount INT
		SELECT @TotalRowCount = (SELECT COUNT(ErrorId) FROM [dbo].[ELMAH_Error])

		/* Automatically delete error log data when TOTAL Row Count is over 10000 */
		IF @TotalRowCount is not null AND @TotalRowCount > 10000

			-- DELETE all ROWS except the most recent 10000
			-- numbered rows
			WITH goners AS (
				FROM [dbo].[ELMAH_Error]
			DELETE FROM goners
			WHERE rn > 10000


That's it, hope that helps!



Add your comment

by Anonymous - Already have an account? Login now!
Your Name:  

Enter the text you see in the image below
What do you see?
Can't read the image? View a new one.
Your comment will appear after being approved.

Related Posts

Here's one way to setup your connection string for a SQL Server 2008 Express database for a live website using ASP.NET on Windows Server 2008. Open "Sql Server Configuration Manager" -> the click on "SQL Server Network Configuration" -> then click...  more »

If you ever wanted to change a field to null in a Sql Server 2005 Management Studio result set, you're in luck because there is a quick and easy way to do it.  more »

UPDATE 12-16-2011: If you are using ASP.NET and your website / database is live, and you want to take the database offline, you're better off putting an App_Offline.htm file in your website main directory to first take the website down for maintance (I...  more »

I recently installed SQL Server 2008 database on my local computer and forgot to enable the common language runtime (CLR). Because I had forgotten to turn it on, some portions of my sites were not fuctioning properly due SQL Server not being able to...  more »

This step-by-step goes through the process of importing delimited text files into a SQL Server 2005 database. The example should help you better understand how to import different types of flat files into SQL Server 2005.  more »

Here's a couple quick SQL database tips for finding and deleting duplicate values in a SQL Server table.To find duplicates in a Column use the following SQL: SELECT ColName1, COUNT(*) TotalCountFROM TableName GROUP BY ColName1 HAVING (COUNT(ColName1) >...  more »

Here's a step by step process to enable a remote connection to your SQL Server 2008 Express database that is configured on Windows Server 2008 using an assigned port #. 1) First make sure the SQL Server Authentication is enabled and the User name that...  more »

When using SQL Server 2008, you may receive a Save (Not Permitted Dialog) box pop-up when trying to save changes to an existing table. The dailog box reads: "Saving changes is not permitted. The changes you have made require the following tables to be...  more »

I recently upgraded from Microsoft SQL Server 2005 to Microsoft SQL Server 2008 on my Windows Vista computer. After successfully installing SQL Server 2008, I could not find the Reporting Services instance installed. I have a feeling this was due to the...  more »

I recently ran into the following error while trying to get Elmah to work / complie with my Web Application Project in Visual Studio 2010: Could not load file or assembly 'file:///C:\Users\DOUGDELL2\Documents\Visual Studio...  more »

Here's how you can enable SQL Server Authentication in a SQL Server 2008 database, and then add a User login and password account for connecting to the database. Open SQL Server Management Studio In the Object Explorer sidebar, right-click on the top SQL...  more »

Are you backing up your SQL Server 2008 databases daily? You should be, especially if you don't want to lose any of your precious data that you're storing. It's incredibly easy to setup a maintenance plan in SQL Server 2008 to automatically back up one or...  more »