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:
ALTER PROCEDURE [dbo].[ELMAH_LogError]
SET NOCOUNT ON
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 (
SELECT ROW_NUMBER() OVER(ORDER BY [TimeUtc] DESC) AS rn, [ErrorId]
DELETE FROM goners
WHERE rn > 10000
That's it, hope that helps!