May 4, 2009 at 9:55 am
Hey all...I've run a substantial trace file and am currently importing it into my Database. I did a quick check on the import and have noticed that the EventClass column has switched to an EventClassID. In looking online, I see queries like
SELECT TE.name, T.*
FROM dbo.tracefiletable T -- table that contains the trace results
JOIN dbo.sys.trace_events TE ON T.EventClass = TE.trace_event_id
ORDER BY RowNumber
but when I try and run it, I get "Invalid object name dbo.sys.trace_events'.". I've looked in the Master database, and cannot find anything. Any ideas? The only thing I can think of is that the table is created when the trace is being run, and because I'm importing the file into a different database on a different server, it can't see the table that was created. I may be way of base, but that's the only logical explanation.
Any ideas would be appreciated...
Thanks,
Chris
May 4, 2009 at 10:17 am
you are having sql server 2000 and sys. schema are in sql server 2005+... so that will not work here.
see fn_trace_gettable() in BOL for more info.
What do you want to do?
HTH...
Vinay.
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
May 4, 2009 at 10:21 am
Hey Vinay...thanks for the reply. I haven't checked BOL yet but wanted to answer your question...What I'm looking at is a trace file tracking errors and deadlocks etc. in the database; I wanted to pull it into a table in my database to do a Count by Time and User. I could do the counts based on event ID and then back track, but I'm lazy and figured this would be a good skill to have!
I'll check out BOL after lunch and post back if I have any further questions. Thanks again! 😀
Chris
May 4, 2009 at 12:44 pm
Hey Vinay - I checked out BOL and when I try and run the query in my Query Analyser, I get "cannot find file c:\trace.trc". Any ideas what else I can try?
Appreciate your help...
Thanks,
Chris
May 4, 2009 at 12:53 pm
eg.
SELECT * FROM ::fn_trace_gettable(('c:\my_trace.trc', -1)
GO
my_trace.trc is the profiler trace file you have to save it on you system and access it.
Thanx.
Vinay
http://rdbmsexperts.com/Blogs/
http://vinay-thakur.spaces.live.com/
http://twitter.com/ThakurVinay
May 7, 2009 at 4:38 pm
Hey all...
Just as an alternative, a friend of mine sent me this query. He's got a permanent table in his Master database to reference from, and now so do I! 😀 Just join your imported trace file to the EventID in this one, and you get a very quick list of the events.
HTH!
Chris
USE master
GO
CREATE TABLE sp_EventID_Table (ID int, Description varchar(50) )
GO
SET NOCOUNT ON
GO
INSERT INTO sp_EventID_Table VALUES (0, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (1, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (2, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (3, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (4, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (5, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (6, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (7, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (8, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (9, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (10, 'RPC:Completed')
INSERT INTO sp_EventID_Table VALUES (11, 'RPC:Starting')
INSERT INTO sp_EventID_Table VALUES (12, 'SQL:BatchCompleted')
INSERT INTO sp_EventID_Table VALUES (13, 'SQL:BatchStarting')
INSERT INTO sp_EventID_Table VALUES (14, 'Login')
INSERT INTO sp_EventID_Table VALUES (15, 'Logout')
INSERT INTO sp_EventID_Table VALUES (16, 'Attention')
INSERT INTO sp_EventID_Table VALUES (17, 'ExistingConnection')
INSERT INTO sp_EventID_Table VALUES (18, 'ServiceControl')
INSERT INTO sp_EventID_Table VALUES (19, 'DTCTransaction')
INSERT INTO sp_EventID_Table VALUES (20, 'Login Failed')
INSERT INTO sp_EventID_Table VALUES (21, 'EventLog')
INSERT INTO sp_EventID_Table VALUES (22, 'ErrorLog')
INSERT INTO sp_EventID_Table VALUES (23, 'Lock:Released')
INSERT INTO sp_EventID_Table VALUES (24, 'Lock:Acquired')
INSERT INTO sp_EventID_Table VALUES (25, 'Lock:Deadlock')
INSERT INTO sp_EventID_Table VALUES (26, 'Lock:Cancel')
INSERT INTO sp_EventID_Table VALUES (27, 'Lock:Timeout')
INSERT INTO sp_EventID_Table VALUES (28, 'DOP Event')
INSERT INTO sp_EventID_Table VALUES (29, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (30, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (31, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (32, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (33, 'Exception')
INSERT INTO sp_EventID_Table VALUES (34, 'SP:CacheMiss')
INSERT INTO sp_EventID_Table VALUES (35, 'SP:CacheInsert')
INSERT INTO sp_EventID_Table VALUES (36, 'SP:CacheRemove')
INSERT INTO sp_EventID_Table VALUES (37, 'SP:Recompile')
INSERT INTO sp_EventID_Table VALUES (38, 'SP:CacheHit')
INSERT INTO sp_EventID_Table VALUES (39, 'SP:ExecContextHit')
INSERT INTO sp_EventID_Table VALUES (40, 'SQL:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (41, 'SQL:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (42, 'SP:Starting')
INSERT INTO sp_EventID_Table VALUES (43, 'SP:Completed')
INSERT INTO sp_EventID_Table VALUES (44, 'SP:StmtStarting')
INSERT INTO sp_EventID_Table VALUES (45, 'SP:StmtCompleted')
INSERT INTO sp_EventID_Table VALUES (46, 'Object:Created')
INSERT INTO sp_EventID_Table VALUES (47, 'Object:Deleted')
INSERT INTO sp_EventID_Table VALUES (48, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (49, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (50, 'SQL Transaction')
INSERT INTO sp_EventID_Table VALUES (51, 'Scan:Started')
INSERT INTO sp_EventID_Table VALUES (52, 'Scan:Stopped')
INSERT INTO sp_EventID_Table VALUES (53, 'CursorOpen')
INSERT INTO sp_EventID_Table VALUES (54, 'Transaction Log')
INSERT INTO sp_EventID_Table VALUES (55, 'Hash Warning')
INSERT INTO sp_EventID_Table VALUES (56, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (57, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (58, 'Auto Update Stats')
INSERT INTO sp_EventID_Table VALUES (59, 'Lock:Deadlock Chain')
INSERT INTO sp_EventID_Table VALUES (60, 'Lock:Escalation')
INSERT INTO sp_EventID_Table VALUES (61, 'OLE DB Errors')
INSERT INTO sp_EventID_Table VALUES (62, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (63, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (64, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (65, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (66, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (67, 'Execution Warnings')
INSERT INTO sp_EventID_Table VALUES (68, 'Execution Plan')
INSERT INTO sp_EventID_Table VALUES (69, 'Sort Warnings')
INSERT INTO sp_EventID_Table VALUES (70, 'CursorPrepare')
INSERT INTO sp_EventID_Table VALUES (71, 'Prepare SQL')
INSERT INTO sp_EventID_Table VALUES (72, 'Exec Prepared SQL')
INSERT INTO sp_EventID_Table VALUES (73, 'Unprepare SQL')
INSERT INTO sp_EventID_Table VALUES (74, 'CursorExecute')
INSERT INTO sp_EventID_Table VALUES (75, 'CursorRecompile')
INSERT INTO sp_EventID_Table VALUES (76, 'CursorImplicitConversion')
INSERT INTO sp_EventID_Table VALUES (77, 'CursorUnprepare')
INSERT INTO sp_EventID_Table VALUES (78, 'CursorClose')
INSERT INTO sp_EventID_Table VALUES (79, 'Missing Column Statistics')
INSERT INTO sp_EventID_Table VALUES (80, 'Missing Join Predicate')
INSERT INTO sp_EventID_Table VALUES (81, 'Server Memory Change')
INSERT INTO sp_EventID_Table VALUES (82, 'User Configurable 0')
INSERT INTO sp_EventID_Table VALUES (83, 'User Configurable 1')
INSERT INTO sp_EventID_Table VALUES (84, 'User Configurable 2')
INSERT INTO sp_EventID_Table VALUES (85, 'User Configurable 3')
INSERT INTO sp_EventID_Table VALUES (86, 'User Configurable 4')
INSERT INTO sp_EventID_Table VALUES (87, 'User Configurable 5')
INSERT INTO sp_EventID_Table VALUES (88, 'User Configurable 6')
INSERT INTO sp_EventID_Table VALUES (89, 'User Configurable 7')
INSERT INTO sp_EventID_Table VALUES (90, 'User Configurable 8')
INSERT INTO sp_EventID_Table VALUES (91, 'User Configurable 9')
INSERT INTO sp_EventID_Table VALUES (92, 'Data File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (93, 'Log File Auto Grow')
INSERT INTO sp_EventID_Table VALUES (94, 'Data File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (95, 'Log File Auto Shrink')
INSERT INTO sp_EventID_Table VALUES (96, 'Show Plan Text')
INSERT INTO sp_EventID_Table VALUES (97, 'Show Plan ALL')
INSERT INTO sp_EventID_Table VALUES (98, 'Show Plan Statistics')
INSERT INTO sp_EventID_Table VALUES (99, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (100, 'RPC Output Parameter')
INSERT INTO sp_EventID_Table VALUES (101, 'Reserved')
INSERT INTO sp_EventID_Table VALUES (102, 'Audit Statement GDR')
INSERT INTO sp_EventID_Table VALUES (103, 'Audit Object GDR')
INSERT INTO sp_EventID_Table VALUES (104, 'Audit Add/Drop Login')
INSERT INTO sp_EventID_Table VALUES (105, 'Audit Login GDR')
INSERT INTO sp_EventID_Table VALUES (106, 'Audit Login Change Property')
INSERT INTO sp_EventID_Table VALUES (107, 'Audit Login Change Password')
INSERT INTO sp_EventID_Table VALUES (108, 'Audit Add Login to Server Role')
INSERT INTO sp_EventID_Table VALUES (109, 'Audit Add DB User')
INSERT INTO sp_EventID_Table VALUES (110, 'Audit Add Member to DB')
INSERT INTO sp_EventID_Table VALUES (111, 'Audit Add/Drop Role')
INSERT INTO sp_EventID_Table VALUES (112, 'App Role Pass Change')
INSERT INTO sp_EventID_Table VALUES (113, 'Audit Statement Permission')
INSERT INTO sp_EventID_Table VALUES (114, 'Audit Object Permission')
INSERT INTO sp_EventID_Table VALUES (115, 'Audit Backup/Restore')
INSERT INTO sp_EventID_Table VALUES (116, 'Audit DBCC')
INSERT INTO sp_EventID_Table VALUES (117, 'Audit Change Audit')
INSERT INTO sp_EventID_Table VALUES (118, 'Audit Object Derived
Permission')
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply