Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Default trace - A Beginner's Guide


Default trace - A Beginner's Guide

Author
Message
lorisj33
lorisj33
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 307
Great article, thanks!

I'm not seeing the actual objectName for anything though and tried it with both SQL 2005 SP2 and SQL 2008 SP2 CTP.
Thoughts on why this data is null? I'm using the scripts per your article examples.

Thanks!
Lori
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2324 Visits: 3135
You have to be in the right database for object_name() to return the right name for a given object_id. For example, an object_id for a table or procedure in adventure works will return null if you execute it against master.



My blog: http://jahaines.blogspot.com
lorisj33
lorisj33
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 307
OK, I just re-ran the test per the script verifying I'm in the right DB and the ObjectName is still Null. Per the default trace it should be capturing this data right? What else can I try?

Thanks!
Lori
Marcia Q
Marcia Q
Old Hand
Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)Old Hand (365 reputation)

Group: General Forum Members
Points: 365 Visits: 699
As per http://msdn.microsoft.com/en-us/library/ms175848.aspx

Displaying Object Names When Viewing Traces
--------------------------------------------------------------------------------

If you wish to display the name of an object rather than the object identifier (Object ID), you must capture the Server Name and Database ID data columns along with the Object Name data column.


Disclaimer: I haven't actually tried this. Smile
Adam Haines
Adam Haines
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2324 Visits: 3135
Lori,

The script in the article does use the objectname counter to return the object, if all of your objects are returning null, you may have a filter where objectname is null. I had a filter for null objectname names in the beginning of the article to highlight database modifications. You have to make sure you filter where objectname is not null or something specific.



My blog: http://jahaines.blogspot.com
lorisj33
lorisj33
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 307
That did the trick!
Duh! I should have looked closer to the where clause.

Thank you,
Lori
Susan Van Eyck
Susan Van Eyck
Mr or Mrs. 500
Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)Mr or Mrs. 500 (529 reputation)

Group: General Forum Members
Points: 529 Visits: 326
This is incredibly cool! I just queried the trace to find the cause of a mysterious nightly error I've been seeing for months! Turns out its from a SQL Agent Job that isn't even scheduled to run (so of course I wasn't looking at it).

Thanks for this very helpful article. It's great to have another tool in my DBA bag of tricks :-)

Susan
Tergum Rufus
Tergum Rufus
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 108
Helpful article Adam - thank you.
How can I use this to trace Queries and Sp activity?
Peter
Melbourne, Australia
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45347 Visits: 39936
I recently had cause to revisit this fine article... well done, Adam. Great for neophytes and alumni alike.

As a side bar, here's the code I ended up using which could actually be turned into an iTVF...

SELECT trc.LoginName,
-- trc.LoginSID,
trc.SPID,
trc.IsSystem,
trc.HostName,
trc.ApplicationName,
trc.ServerName,
trc.DatabaseName,
trc.ObjectName,
evt.Category_ID,
CategoryName = cat.name,
trc.TextData,
trc.StartTime,
trc.EventClass,
trc.EventSubClass,
EventName = evt.name
FROM FN_TRACE_GETTABLE( (
SELECT CAST([Value] AS NVARCHAR(4000)) --Drive, path, and filename of trace file
FROM FN_TRACE_GETINFO(0) --0 = Current Server
WHERE [Property] = 2 --2 = Trace File Path
)
,0) trc --0 = Latest default trace file
INNER JOIN sys.Trace_Events evt ON eventclass = trace_event_id
INNER JOIN sys.Trace_Categories cat ON evt.category_id = cat.category_id
WHERE trc.DatabaseName = 'somedatabasenamehere'
AND trc.ObjectName = 'somesqlserverobjectnamehere'
;




Heh... and, no... I don't code for things to work on case sensitive servers. :-P

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45347 Visits: 39936
peter-970097 (9/21/2010)
Helpful article Adam - thank you.
How can I use this to trace Queries and Sp activity?
Peter
Melbourne, Australia


I don't believe you can. The default trace wasn't setup to detect such things. You'll have to setup your own trace for such things.

--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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search