SQL Server Performance Forum – Threads Archive

Identify Object Being Accessed

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!!

do a DBCC INPUTBUFFER(spid) on the server when you see the spid. the spids change pretty quickly so you should be pretty fast. The DBCC INPUTBUFFER will tell you what the spid was doing. It will give you the T-SQL or the proc that was being executed. From there on you need to figure out what objects are being used. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

I appreciate the reply, but I wanted to find a way to do this programatically.
I know I can get the spid and the sql info, but wanted to get the object id, so if for ex, there’s an ‘update table mytable’ statement, somewhere there is a corresponding objectid in a sys table or view. Any suggestions? tx

no the information is not stored anywhere. you’d have to write queries to find it. You can get the objectid for a table as SELECT Object_id(‘mytable’). ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

is there a way to join this info? That’s still kind of manual. 1.sp_who 2.dbcc inputbuffer(spid) 3.SELECT Object_id(‘mytable’).
Anyway to automate this? Nothing that says an object is being accessed or last access date -something like that? Tx,again!

you’d have to be saving the information to some table constantly and then query against the table. the spid could be running a proc, or a TSQL or a begin tran or a rollback or a delete. There’s no generic way to find the objectid direectly. you’d have to write a custom proc to run the trace with the events you want. If all your application access is through procs then perhaps you might have some luck since you can make some assumptions. If you have adhoc SQL queries coming in, then it makes it more difficult. ***********************
Dinakar Nethi
SQL Server MVP
***********************
http://weblogs.sqlteam.com/dinakar/

I appreciate keeping up w/the thread. So, assuming I follow your suggestion, (saving and querying) what would the logic be?
What info would i need to store and join – just at a high level.

Trace is the best option in this case…. you can capture all the information if you are using sysprocesses/inpubuffer… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

thx mohammedu: Where in inputbuffer or sysprocesses is the object name/id being affected? I would have to do a search/parse of the inputbuffer?? Really trying to avoid trace/profiler. Tx
Please advise.

I don’t think it is viable solution using inputbuffer… You can make use of the following code… and dump into a temp table and you can use charindex function to get the object name by looping through your sysobjects but it is hard for you to check the sysobjects of all dbs… create table #temp (EventTypeVarchar(50), Parametersint, EventInfo Varchar(256))
declare @sql Varchar(1000), @spid Varchar(5)
select @sql = ‘DBCC INPUTBUFFER ‘, @spid = 52
select @sql = @sql+'(‘[email protected]+’)’
select @sql
insert into #temp
exec (@sql)
select * from #temp
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

By the way if you are using sql server 2005 then you can make use of default trace which runs all the time and it is enabled by default… The trace is stored in the LOG directory for your SQL Server instance (i.e. C

rogram FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG) at the root of where your SQL Server is installed. MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

See thishttp://techrepublic.com.com/5208-6230-0.html?forumID=102&threadID=219119&start=0 too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

Thx, M and S. M: What is the default trace and how do you access it? S: This is very interesting, but does this require to put triggers on your table? Appreciate the brainpower here <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

You can’t modify the default trace but you read the trace from the log folder as I mentioned in my previous posting….
The trace is stored in the LOG directory for your SQL Server instance (i.e. C

rogram FilesMicrosoft SQL ServerMSSQL.1MSSQLLOG) at the root of where your SQL Server is installed. You can copy these trace files to different location for your use… MS keeps only 5 files I belive..
S: This is very interesting, but does this require to put triggers on your table?
Yes, you have to create the trigger on the source table and write it to a audit table… MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Either you have to trace the event using PROFILER (Logon audit) or use some sort of triggers.
Either way the third party tool will also have similar approach to get such information. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

]]>