IF EXISTS (SELECT TOP 1 1 FROM SYS.procedures WHERE name = 'DeleteExpiredSessions') BEGIN
DROP PROCEDURE DeleteExpiredSessions
END
GO
CREATE PROCEDURE [dbo].[DeleteExpiredSessions]
AS
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW
DECLARE @now datetime
SET @now = GETUTCDATE()
CREATE TABLE #tblExpiredSessions
(
SessionId nvarchar(88) NOT NULL PRIMARY KEY
)
INSERT #tblExpiredSessions (SessionId)
SELECT SessionId
FROM [ASPState].dbo.ASPStateTempSessions WITH (READUNCOMMITTED)
WHERE Expires < @now
IF @@ROWCOUNT <> 0
BEGIN
DECLARE ExpiredSessionCursor CURSOR LOCAL FORWARD_ONLY
READ_ONLY
FOR SELECT SessionId FROM #tblExpiredSessions
DECLARE @SessionId nvarchar(88)
OPEN ExpiredSessionCursor
FETCH NEXT FROM ExpiredSessionCursor INTO @SessionId
WHILE @@FETCH_STATUS = 0
BEGIN
DELETE FROM [ASPState].dbo.ASPStateTempSessions WHERE SessionId = @SessionId AND Expires < @now
FETCH NEXT FROM ExpiredSessionCursor INTO @SessionId END
CLOSE ExpiredSessionCursor
DEALLOCATE ExpiredSessionCursor
END
DROP TABLE #tblExpiredSessions
RETURN 0
Creating SQL Job :
The below job is executed when the above stored procedure is executed :
ASPState' + '_Job_DeleteExpiredSessions Job :
/* Create the job to delete expired sessions */
-- Add job category
-- We expect an error if the category already exists.
PRINT 'If the category already exists, an error from msdb.dbo.sp_add_category is expected.' EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]' GO
BEGIN TRANSACTION DECLARE @JobID BINARY(16) DECLARE @ReturnCode int DECLARE @nameT nchar(200)
SELECT @ReturnCode = 0
-- Add the job
SET @nameT = N'ASPState' + '_Job_DeleteExpiredSessions'
EXECUTE @ReturnCode = msdb.dbo.sp_add_job
@job_id = @JobID OUTPUT,
@job_name = @nameT,
@owner_login_name = NULL,
@description = N'Deletes expired sessions from the session state database.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 0,
@delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
SET @nameT = N'ASPState' + '_JobStep_DeleteExpiredSessions'
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep
@job_id = @JobID,
@step_id = 1,
@step_name = @nameT,
@command = N'EXECUTE DeleteExpiredSessions',
@database_name = N'ASPState',
@server = N,
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 1,
@output_file_name = N,
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
SET @nameT = N'ASPState' + '_JobSchedule_DeleteExpiredSessions'
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule
@job_id = @JobID,
@name = @nameT,
@enabled = 1,
@freq_type = 4,
@active_start_date = 20001016,
@active_start_time = 0,
@freq_interval = 1,
@freq_subday_type = 4,
@freq_subday_interval = 1,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:
GO