October 9, 2009 at 10:43 am
Hi,
Right now I'm working in a development environment where there are hundreds of users accessing the SQL Servers in the department. Some times, the server is so busy that some operations or tests can take hours to run. It has happened sometimes that someone has been killing processes to get more speed on his/her queries and thus scripts that had been running for hours are just stopped and have to be re-run. I've heard that there is a way of knowing what user did kill the process by reading the transaction log, but I don't know exactly how. It's very important to me to get that information backwards, son that leaving a trace running is not an option. Any help is appreciated.
Thanks in advance
PD: Don't ask me why but buying a transaction log reader tool is not an option. I know of someone here that did this before (to interpret the transaction log to find out who killed a process) but I have no way to ask him anymore.
October 9, 2009 at 11:56 am
oscar_otrebla (10/9/2009)
It has happened sometimes that someone has been killing processes to get more speed on his/her queries and thus scripts that had been running for hours are just stopped and have to be re-run.
Killing a process requires sysadmin permissions. I would suggest you lock the server down, ensure only the DBAs have sysadmin permissions. That should help in the long run.
I've heard that there is a way of knowing what user did kill the process by reading the transaction log, but I don't know exactly how. It's very important to me to get that information backwards, son that leaving a trace running is not an option. Any help is appreciated.
I'd be very surprised if that info is in the transaction log. I did a quick test and I can't see anything that gets logged when a kill statement runs.
Try the SQL error log. There will be an entry in there when a process gets killed.
From my tests:
Process ID 52 was killed by hostname MYRLIN, host process ID 5948.
That should be enough to identify the culprit. Then you can look at reducing that person's permissions so that they can't randomly kill processes
PD: Don't ask me why but buying a transaction log reader tool is not an option.
Probably because the prices start at $1000 (or they did last time I checked)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 9, 2009 at 2:01 pm
Thanks a lot for your reply GilaMonster!
It was perfect. The errorlog information is good enough for our purposes! I was thinking that maybe (as you mentioned) transaction log doesn't actually stores any information about this operations as it only keeps track of Transactions. Thanks you for leading me to the right path!:-D
Blessings!
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply