The job "ASPState_Job_DeleteExpiredSessions" filling up SQL Agent job history

Question

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

Answer 1

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

Answer 2

0

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

answered Mar 24 '10 at 01:28 PM

sp_lock
8.2k ● 20 ● 26 ● 29

@Jonlee: Thanks! I will send the result to my inbox instead of checking log manually every morning.

Mar 24 '10 at 04:29 PM DaniSQL

(comments are locked)

The job "ASPState_Job_DeleteExpiredSessions" filling up SQL Agent job history

Follow this question

Related Questions