Recently there's an open tran session that always hold tempdb log size till reach full in my SQL server. I did check on the SPID session but returns NULL value all the time. So what I do is just manually kill the SPID in order to release the tempdb log size.
Therefore, I would like to know if there any way on how I gonna setup an automate job/trigger to get kill that session (with NULL value) which holding the log size.
SQL Server Version : SQL 2014 Standard Edition
Thanks. Hope can get some idea in here. Appreciated.
Killing the SPID seems like treating the symptom rather than fixing the root cause. While you can create a scheduled job or something like that to kill the SPID, my guess is that you would run into some other problem unless you find the root cause.
I don't have any guesses as to what might be causing the issue. I would start by looking at the processes and and investigate perhaps using sp_whoisactive or some similar tools. Also there are number of system views that will give you some insight.
I second what @JamesK said. Try to identify the root cause. What application is hiding behind the session? Maybe username will lead you somewhere. Any other tasks on the SQL Agent that might be running at the same time? Application doing some kind of maintenance, any other activity etc?
Killing the SPID will fix your space issue, but you may end up with some processes not being able to finish. It can be a number of things, but if you need to kill session on SQL Server in order to fix it - something is not right.
There are some applications that after killing session on the SQL will retry it, so you may end up killing sessions over and over.