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

DaniSQL gravatar image

DaniSQL
4.9k 33 35 39

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

2 answers: sort oldest

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

sp_lock gravatar image

sp_lock
9.1k 24 28 31

@Jonlee: I am not a developer and I dont know the effect of modifying the SP the way you did. BTW I have no blocking/deadlock issue. What is happening is when I check the SQL agent history every morning for error all I see is successful logs from this job since it runs every minute it bloated the log. so i was wondering if I can run the job once an hour if it doesn't affect anything or if there is a way to limit its logs so that I can see more of log of other jobs. Thanks.
Mar 24, 2010 at 12:52 PM DaniSQL
(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_durationFROM  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

sp_lock gravatar image

sp_lock
9.1k 24 28 31

@Jonlee: Thanks! I will send the result to my inbox instead of checking log manually every morning.
Mar 24, 2010 at 04:29 PM DaniSQL
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1945
x88
x86

asked: Mar 24, 2010 at 11:48 AM

Seen: 3706 times

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