Post reply

Who killed the process

  • February 23, 2011 at 3:00 pm

    #246108

    Is there any way to trace from which login the KILL spid statement given.

    Does all the KILL Spid statement is traced out in sql server

  • February 23, 2011 at 3:49 pm

    #1290672

    Not sure about finding the login without additional traces already running but from the SQL Server logs you can tell which host killed a SPID. If you can trace the host back to a user then you would know who killed a SPID.

    You should see an entry like this in the SQL log:

    Process ID <SPID> was killed by hostname <MachineName>, host process ID <ProcessID>.

  • February 23, 2011 at 3:59 pm

    #1290674

    Thanks JeremyE .

    I did some search and got some info ..here s that

    The information about killed sessions was available on one of the undocumented DMV sys.dm_os_ring_buffers , with ring_buffers type 'RING_BUFFER_CONNECTIVITY' .

    It has information about the SPID , Server IP, Local IP(Machine from where connection was established) and the time .

    I have scripted a basic query to extract the available information from the DMV.

    SELECT

    DATEADD (ms, r.[timestamp] - sys.ms_ticks, getdate()) as Record_time,

    CAST(record AS XML).value(N'(/Record/ConnectivityTraceRecord/Spid)[1]','int') AS Spid,

    CAST(record AS XML).value(N'(/Record/ConnectivityTraceRecord/RemoteHost)[1]','VARCHAR(20)') AS RemoteHost,

    CAST(record AS XML).value(N'(/Record/ConnectivityTraceRecord/RemotePort)[1]','VARCHAR(20)') AS RemotePort,

    CAST(record AS XML).value(N'(/Record/ConnectivityTraceRecord/LocalHost)[1]','VARCHAR(20)') AS LocalHost,

    CAST(record AS XML).value(N'(/Record/ConnectivityTraceRecord/LocalPort)[1]','VARCHAR(20)') AS LocalPort

    from sys.dm_os_ring_buffers r

    CROSS JOIN sys.dm_os_sys_info sys

    WHERE ring_buffer_type= 'RING_BUFFER_CONNECTIVITY'

    AND CAST(record AS XML).value(N'(/Record/ConnectivityTraceRecord/TdsDisconnectFlags/SessionIsKilled)[1]','int') = 1

    ORDER BY record_time DESC

    One of the links about sys.dm_os_ring_buffers

    http://blogs.msdn.com/b/sql_protocols/archive/2008/05/20/connectivity-troubleshooting-in-sql-server-2008-with-the-connectivity-ring-buffer.aspx

  • February 23, 2011 at 4:03 pm

    #1290677

    Very cool. Thanks for posting the info.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply