February 5, 2008 at 7:52 am
The other day our third-party multi-database application locked up. Since we don't have a DBA here, the application manager asked me to take a look at what the problem was. My first thought was to use Enterprise Manager to check Current Activity, but the system was so tied up in knots, I couldn't access it. So I went to Query Analyzer, and ran sp_who2. Using the BlkBy column, we were able to identify which SPID seemed to be at the root of the problem, so we killed it. That freed up everything stuck behind it, and we were back in business.
Well, almost, because killing that SPID obviously interrupted whatever it was trying to do. Now I know that using EM, I can click on the ProcessID to view the last TSQL command batch, and maybe get a hint of what the SPID is trying to do.
My question is, from within QA, what could/should I have done to determine what was going on (or not) with the SPID that I killed?
Thanks,
Mattie
February 5, 2008 at 11:06 am
Did you check ProgramName in the output of sp_who2?
Or did you try to run SELECT * FROM master..sysprocesses?
February 5, 2008 at 11:19 am
DBCC INPUTBUFFER(spid) might be what you're looking for.
February 6, 2008 at 10:06 am
Thanks so much to both of you. I don't recall looking at ProgramName when I ran sp_who2, but I don't think it would have been too helpful, because there are only two applications that run against the data: the application itself, and then an additional reporting application. And since it's a web based application, there is only one Login for everyone using it, so that wouldn't be much help (unless it was the reporting application, and I don't think it was).
It looks like the DBCC INPUTBUFFER command is what I should have run, which would have given me the EventInfo, and from that point I could have researched what tables were in use based on that.
Thanks,
Mattie
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply