SQL server maintains a record of active user sessions in the table SYSPROCESSES from the MASTER database, inside sysprocesses there is a column named “login name” and we base our script on sysprocesses to clear the ACTIVITY table from the DYNAMICS database.
Once ACTIVITY table has been cleaned out we are ready to clean 2 tables from the TEMP database first we clean DEX_SESSION and then DEX_LOCK in order to eliminate locks and processes in temp tables.
The next step is to clean batch activity (SY00800) and resource activity (SY00801) in order to have a valid session clean up.
Here is the code:
delete from DYNAMICS..ACTIVITY
where USERID not in (select loginame from master..sysprocesses)
delete from tempdb..DEX_SESSION
where session_id not in (select SQLSESID from DYNAMICS..ACTIVITY)
delete from tempdb..DEX_LOCK
where session_id not in (select SQLSESID from DYNAMICS..ACTIVITY)
delete from DYNAMICS..SY00800
where USERID not in (select USERID from DYNAMICS..ACTIVITY)
delete from DYNAMICS..SY00801
where USERID not in (select USERID from DYNAMICS..ACTIVITY)
Note: Make sure you have a backup of your databases when running scripts that can modify your data.
Also I am including the KB’s published that show how to remove records from DEX_LOCK and DEX_SESSION that working together make the script I just provided.
No comments:
Post a Comment