by softlion
6. January 2009 14:52
The standard ASP.NET 2/3/3.5 SP locks the ASPStateTempSessions table for a long time, which in turns locks running applications. This becomes very useful when SQL Server Agent jobs (especially xxx_Job_DeleteExpiredSessions) were disabled for some time for some reason.
A better SP looks like this:
1: SET ANSI_NULLS ON
2: GO
3: SET QUOTED_IDENTIFIER ON
4: GO
5:
6: ALTER PROCEDURE [dbo].[DeleteExpiredSessions]
7: AS
8:
9: DECLARE @now datetime
10: SET @now = GETUTCDATE()
11: declare @count int
12: set @count = 1
13:
14: set ROWCOUNT 1000
15: while @count != 0
16: begin
17: begin tran
18: DELETE [Softlion].dbo.ASPStateTempSessions
19: WHERE Expires < @now
20: set @count = @@ROWCOUNT
21: commit
22: end
23: set ROWCOUNT 0
24:
25: RETURN 0
You may also add the hint WITH (ROWLOCK) on the delete statement to be sure no lock are set on a range which may contains rows currently used by the application.