Figure out Source of SPID retrospectively

Question

Figure out Source of SPID retrospectively

whitty0351

SSC Enthusiast

Points: 172
More actions
February 10, 2020 at 12:33 pm

#3724319

Hi All
I am looking for a way to find the source of a SPID that ran a query last night. I have inherited an SQL environment and there are DBCC queries being ran at 10pm each night. They are not being ran as SQL Agent Jobs or scheduled SP's so I assume they are being ran remotely from a monitoring platform or management server. I can see in the SQL Server log they are being ran under the local admin account and I can see the SPID but I am not sure if it is possible to query the hostname retrospectively.
I can connect at 10pm one night, check the logs for the SPID and run sp_who2 and get it that way but I would like to avoid working so late in the evening if there is another way.
Thanks 🙂

Viewing 6 posts - 1 through 5 (of 5 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

You can get information of the DBCC executions by querying the default trace file for the Audit DBCC Event. It will have login, host name, application name, etc.

Sue

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

If you want to capture the commands without having to run sp_who2 (and oh boy, a ton of ways to get information out of the database better than that), just set up an Extended Events session to capture the command.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Sue_H wrote:

You can get information of the DBCC executions by querying the default trace file for the Audit DBCC Event. It will have login, host name, application name, etc.
Sue

Just a caution here... the lifetime expectancy of data in the default trace can be extremely low on a really busy system. It's less than 90 seconds on my main production box. It would be nice if MS allowed us to increase the rollover rows a bit.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

Jeff Moden wrote:

Sue_H wrote:
You can get information of the DBCC executions by querying the default trace file for the Audit DBCC Event. It will have login, host name, application name, etc.
Sue
Just a caution here... the lifetime expectancy of data in the default trace can be extremely low on a really busy system. It's less than 90 seconds on my main production box. It would be nice if MS allowed us to increase the rollover rows a bit.

Oh, god, you're going to hate me.

You could look to the system_health Extended Event session. Not only does it capture this data (and more), but you can edit it to capture more data if you want.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Sue_H SSC Guru Points: 90860 More actions · Answer 5

Grant Fritchey wrote:

Jeff Moden wrote:
Sue_H wrote:
You can get information of the DBCC executions by querying the default trace file for the Audit DBCC Event. It will have login, host name, application name, etc.
Sue
Just a caution here... the lifetime expectancy of data in the default trace can be extremely low on a really busy system. It's less than 90 seconds on my main production box. It would be nice if MS allowed us to increase the rollover rows a bit.
Oh, god, you're going to hate me.
You could look to the system_health Extended Event session. Not only does it capture this data (and more), but you can edit it to capture more data if you want.

And likely to still be in the default trace as well. And yeah I'm aware they roll over. So do the built in extended events. Nothing wrong with looking at the default trace though - it's there, it's not adding any overhead to the system, a reference to look something up. Nothing wrong with that.

Sue