DB Script To Clear Session Data From Database

From Znode Knowledge Base
Jump to: navigation, search
Problem Statement:

Earlier, expired sessions were not being deleted from the database, due to which database used to run out of disk space. To resolve this issue, a cleanup script was needed.

Script:

The SQL Agent job, ASPState_Job_DeleteExpiredSessions, is scheduled to execute every minute SQL job which includes dbo.DeleteExpiredSessions stored procedure by default.

This job will delete all expired sessions from the database.

Note: This script applicable only for cluster/load balanced environment.

Deleting Expired Sessions :

The following stored procedure needs to be executed in order to clean the expired sessions from the database :

Stored Procedure: DeleteExpiredSessions

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