Identify object accessed.....2005/2000

Question

Post reply

Identify object accessed.....2005/2000

sql_jr

SSCommitted

Points: 1612
More actions
May 1, 2007 at 7:35 am

#178056

hi,
I was hoping to find a way to capture/identify the object accessed that correlates to a particular spid.(sysprocesses)
I know there are some new dynamic views in SQL2K5, but also need a method for 2000. (Please help w/both)
For example, if I see a particular spid, I want to know what object it is accessing. Does this info exist in any system table or function? I know I can do a dbcc inputbuffer to get partial sql, but wanted to get the object name its running against (No profiler please) THX!!

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

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

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

why ?

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

sql_jr SSCommitted Points: 1612 More actions · Answer 2

why? Maybe for non-intrusive logging and correlation of events.

I know the best ways are triggers and/or profiler, but wanted to avoid this.

Now that you know why, can you suggest from your expertise Thanks much!

Anders Pedersen SSChampion Points: 11410 More actions · Answer 3

Not sure this is what you are looking for, but DBCC Inputbuffer will at least give you something...

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

In general triggers are absolutely the worst way to go as they willd egrade performance. If it's complaince issues then you should check out Compliance Manager from Idera, I use this product and it is very very good.

http://www.idera.com/Products/SQLcm/

( I do not work for Idera btw. )

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

sql_jr SSCommitted Points: 1612 More actions · Answer 5

Thx, know about SQLcm, but trying to avoid the $$ for now.

I meant triggers are the most common way, but agree that it is a bad idea -which is why i want to avoid them

On another note, however, you would differ on that assessment for SQL2k5's DDL triggers - they work a bit differently than triggers on the tables directly, right? thx

Anders Pedersen SSChampion Points: 11410 More actions · Answer 6

I use DDL triggers to log changes to the database, with no noticed performance degradation. Yesterday it wrote 10196 changes to my logging table.

Don't ask why we have that many changes, but that includes permission changes. I'm still working on cleaning that up so we don't have that many.... but with loading data from ISAM files it is a bit of a challenge when the data has to be kept online both in the ISAM and in the tables, so a lot of renames etc of tables.