The job "ASPState_Job_DeleteExpiredSessions" filling up SQL Agent job history

Question

question

DaniSQL asked Mar 24, '10

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

sql-server-2005 sql-agent job

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-03-24T11:56:06Z

sp_lock answered Mar 24, '10

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-03-24T13:28:30Z

sp_lock answered Mar 24, '10

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question