Post reply

Identify a spid

  • September 9, 2005 at 6:37 am

    #115159

    Is there a way to go back in time to identify a spid at a certain time. I had a locking issue that resulted in someone leaving a locked record open over night. It was SPID 85 all last night. This morning the SPID is cleared out. Is that information in a table somewhere so I can identify who SPID 85 was last night or am I out of luck after the fact?

  • September 9, 2005 at 7:00 am

    #588688

    Sorry but you can't get this sort of thing retrospecively out of SQL.

    You could always implement some auditing for blocking events or failing that kick off a profiler trace overnight.

  • September 9, 2005 at 7:10 am

    #588693

    Thanks. I did not think so but wanted another voice. Thanks

  • September 9, 2005 at 7:27 am

    #588702

    You can schedule a database job that runs a lightweight check on the processes and in the event of :

    a) blocker/blockee situations,

    b) open transactions for a long time,

    c) hung sessions

    It can get more information from sysprocesses, syslockinfo, DBCC OPENTRAN, DBCC INPUTBUFFER(spid) and fn_get_sql() (if using SP3 or higher) and log that into an audit table and/or send that out as an alert to the DBA/Developers. You can take a look at MSDN for a blocker stored procedure provided by Microsoft PSS group that will help you further in doing this. Use the URL below:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;271509

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

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