Open session on quit and reload, still active

Hi,

This is a SQL issue but what you can use is to create a maintenance job on the SQL server who runs every x minute and kills not active sessions.

Then you would need to use a script like this one

[color=#0000ff]
Code Snippet
[color=#0000ff] [/color][color=#0000ff]DECLARE[/color] @v_spid [color=#0000ff]INT[/color]
[color=#0000ff] [/color][color=#0000ff]DECLARE[/color] Users [color=#0000ff]CURSOR[/color]
FAST_FORWARD [color=#0000ff]FOR[/color]
[color=#0000ff]SELECT[/color] SPID
[color=#0000ff]FROM[/color] master.dbo[color=#808080].[/color]sysprocesses [color=#808080]([/color]NOLOCK[color=#808080])[/color]
[color=#0000ff]WHERE[/color] spid[color=#808080]>[/color]50
[color=#808080]AND[/color] status[color=#808080]=[/color][color=#ff0000]'sleeping'[/color]
[color=#808080]AND[/color] [color=#ff00ff]DATEDIFF[/color][color=#808080]([/color]mi[color=#808080],[/color]last_batch[color=#808080],[/color][color=#ff00ff]GETDATE[/color][color=#808080]())>=[/color]60 [color=#008000]--Check sleeping connections that exists before 60 min..[/color]
[color=#808080]AND[/color] spid[color=#808080]<>[/color][color=#ff00ff]@@spid[/color]
[color=#0000ff] [/color][color=#0000ff]OPEN[/color] Users
[color=#0000ff] [/color][color=#0000ff]FETCH[/color] NEXT [color=#0000ff]FROM[/color] Users [color=#0000ff]INTO[/color] @v_spid
[color=#0000ff] [/color][color=#0000ff]WHILE[/color] [color=#808080]([/color][color=#ff00ff]@@FETCH_STATUS[/color][color=#808080]=[/color]0[color=#808080])[/color]
[color=#0000ff] BEGIN
[/color] [color=#0000ff]PRINT[/color] [color=#ff0000]'KILLing '[/color][color=#808080]+[/color][color=#ff00ff]CONVERT[/color][color=#808080]([/color][color=#0000ff]VARCHAR[/color][color=#808080],[/color]@v_spid[color=#808080])+[/color][color=#ff0000]'...'[/color]
[color=#0000ff]EXEC[/color][color=#808080]([/color][color=#ff0000]'KILL '[/color][color=#808080]+[/color]@v_spid[color=#808080])[/color]
[color=#0000ff]FETCH[/color] NEXT [color=#0000ff]FROM[/color] Users [color=#0000ff]INTO[/color] @v_spid
[color=#0000ff] END
[/color][color=#0000ff]CLOSE[/color] Users
[color=#0000ff] [/color][color=#0000ff]DEALLOCATE[/color] Users
[/color]

You need to only check spid above 50 users will not have a spid under 50.

Hope this help.
edited by ingvarorn on 11/27/2014

The script is missing from the post.

You see the script now David?

Thank you

And one more.

This is a script i run manually so if you are going to set it up as a job then you would need to remove the PRINT command but you could also replace it with xp_logevent to write a entry to the event logger on the server.

The format of xp_logevent is:
xp_logevent { error_number , 'message' } [ , 'severity' ]

Where the severity is the same as you see in event viewer, Information, Warning, Error and Critical.
The error_number needs to be above 50000 since under that is reserved for Microsoft (it will work but..)

Example how I would do this for the script above.
xp_logevent { 990016 , 'SQL job notactive sessions is KILLing '+CONVERT(VARCHAR,@v_spid)+'...' } [ , 'Information' ]