DB Script To Clear Session Data From Database
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.
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')
CREATE PROCEDURE [dbo].[DeleteExpiredSessions]
DROP TABLE #tblExpiredSessions
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 */
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
SET @nameT = N'ASPState' + '_Job_DeleteExpiredSessions'
SET @nameT = N'ASPState' + '_JobStep_DeleteExpiredSessions'
@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,
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
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave:GO