Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456»»

Default trace - A Beginner's Guide Expand / Collapse
Author
Message
Posted Monday, August 23, 2010 10:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:35 AM
Points: 18, Visits: 242
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
Post #973612
Posted Monday, August 23, 2010 10:45 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:34 AM
Points: 2,278, Visits: 3,046
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
Post #973617
Posted Tuesday, August 24, 2010 9:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:35 AM
Points: 18, Visits: 242
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
Post #974205
Posted Tuesday, August 24, 2010 12:44 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 12:15 PM
Points: 365, Visits: 678
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. :)



Post #974365
Posted Tuesday, August 24, 2010 1:02 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:34 AM
Points: 2,278, Visits: 3,046
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
Post #974384
Posted Wednesday, August 25, 2010 6:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, March 27, 2014 8:35 AM
Points: 18, Visits: 242
That did the trick!
Duh! I should have looked closer to the where clause.

Thank you,
Lori
Post #974805
Posted Friday, September 03, 2010 1:39 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:26 PM
Points: 517, Visits: 230
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
Post #980492
Posted Tuesday, September 21, 2010 9:23 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 1:16 AM
Points: 20, Visits: 105
Helpful article Adam - thank you.
How can I use this to trace Queries and Sp activity?
Peter
Melbourne, Australia
Post #990861
Posted Thursday, December 02, 2010 12:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
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.


--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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1029487
Posted Thursday, December 02, 2010 1:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 35,959, Visits: 30,252
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."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1029496
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse