x

The job "ASPState_Job_DeleteExpiredSessions" filling up SQL Agent job history

Hi All,

I have these job(ASPState_Job_DeleteExpiredSessions) running every minute on a few servers and it fills SQL Agent job history and make it hard for me to check things at a glance. I don't exactly understand what the job does but from my quick research on Google i figured it is kind of necessary to delete expired ASP sessions states. So how can I get rid history of the job of the job history page? Also is the job really necessary?

Thanks,

EDIT: Can I make it run once every hour?

Edit: Here is the procedure that the job runs every minute:

CREATE PROCEDURE dbo.DeleteExpiredSessions AS DECLARE @now datetime SET @now = GETUTCDATE()

     DELETE [ASPState].dbo.ASPStateTempSessions
     WHERE Expires < @now

     RETURN 0 

more ▼

asked Mar 24, 2010 at 11:48 AM in Default

avatar image

DaniSQL
4.9k 33 39 43

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

What version of ASP.NET are you using?

There can be issues with the "shipped" SP that causes blocking/deadlocks. If you are using 2.0 then TEST the SP below.

ALTER PROCEDURE dbo.DeleteExpiredSessions AS DECLARE @now datetime SET @now = GETUTCDATE()

CREATE TABLE #ExpiredSessions ( SessionID nvarchar(88) NOT NULL PRIMARY KEY )

INSERT #ExpiredSessions (SessionID) SELECT SessionID FROM [ASPState].dbo.ASPStateTempSessions WHERE Expires < @now

DECLARE SessionCursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR SELECT SessionID FROM #ExpiredSessions ORDER BY CHECKSUM(NEWID()) DECLARE @SessionID nvarchar(88)

OPEN SessionCursor FETCH NEXT FROM SessionCursor INTO @SessionID WHILE @@FETCH_STATUS = 0 BEGIN DELETE FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionID = @SessionID FETCH NEXT FROM SessionCursor INTO @SessionID END

CLOSE SessionCursor DEALLOCATE SessionCursor DROP TABLE #ExpiredSessions RETURN 0

GO

more ▼

answered Mar 24, 2010 at 11:56 AM

avatar image

sp_lock
10.5k 27 37 37

(comments are locked)
10|1200 characters needed characters left

Dani,

Sorry I got the wrong end of the stick. You could change the schedule but on a high volume web server it could cause issue (performance on ASPSTATE db).

The filter in the log viewer is limited... You could try using the script below that I have used in the past. Just change the WHERE to exclude your session state job.

SELECT sj.name, sj.description, sjh.run_date , sjh.run_time , sjh.run_status as step_status, -- 0 failed, 1 sucess, 2 retry, 3 cancelled, 4 running sjh.run_duration as step_duration

FROM msdb.dbo.sysjobs sj JOIN msdb.dbo.sysjobhistory sjh on sj.job_id = sjh.job_id WHERE sjh.step_id >= 1 ORDER BY sjh.run_date desc, sjh.run_time desc

more ▼

answered Mar 24, 2010 at 01:28 PM

avatar image

sp_lock
10.5k 27 37 37

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2016
x107
x98

asked: Mar 24, 2010 at 11:48 AM

Seen: 4631 times

Last Updated: Mar 24, 2010 at 12:46 PM

Copyright 2016 Redgate Software. Privacy Policy