Hi,
I am working on MS SQL Sever 2005 & 2000; I am interest on audit traces.
In MSSQL 2005 I am using built in system table like sys.trace_events I am
able to see list of events. When executing the following query.
SELECT * FROM sys.trace_events;
Is there any equal lent table available on MSSQLServer 2000. I tried a
lot, I am unable to get. Please any body help on this issue.
Thanks & Regards
-SomaSekharHi
I'm afraid you cannot get it in SQL Server 2000
sp_trace_setevent stored procedure has a parameter EventId and there is a
list of event numbers (See BOL)
"SomaSekhar" <SomaSekhar@.discussions.microsoft.com> wrote in message
news:6E597B1F-590C-403B-A964-9EF67AF5D923@.microsoft.com...
> Hi,
> I am working on MS SQL Sever 2005 & 2000; I am interest on audit traces.
> In MSSQL 2005 I am using built in system table like sys.trace_events I am
> able to see list of events. When executing the following query.
> SELECT * FROM sys.trace_events;
> Is there any equal lent table available on MSSQLServer 2000. I tried a
> lot, I am unable to get. Please any body help on this issue.
> Thanks & Regards
> -SomaSekhar|||Hi Uri,
What is the better solution for this. How to get list of EventId's and
EventClass name on SQL Server 2000. Because i am using
fn_trace_gettrable('trace file name', default), it's giving in EventClass as
a EventID. Insted of EventID i need EventClass name. How achive this in SQL
Server 2000. In SQL Server 2005 i am using INNER JOIN condion on
sys.trace_events i am comparing both event ID's i am getiting EventClass
name. Please check this query,
--This will work on MS SQL Server 2005
SELECT TextData,trace_event_id,category_id,name
FROM fn_trace_gettable('E:\Somu\trace_events.trc', default) EventLog
INNER JOIN sys.trace_events EventID ON EventLog.EventClass =
EventID.trace_event_id
NOTE : in the above query "E:\Somu\trace_events.trc - Insted of this give
give Ur trace file location"
What is the equallent Query in MS SQL Server 2000.
Thanks & Regards
-SomaSekhar
"Uri Dimant" wrote:
> Hi
> I'm afraid you cannot get it in SQL Server 2000
> sp_trace_setevent stored procedure has a parameter EventId and there is
a
> list of event numbers (See BOL)
>
>
> "SomaSekhar" <SomaSekhar@.discussions.microsoft.com> wrote in message
> news:6E597B1F-590C-403B-A964-9EF67AF5D923@.microsoft.com...
>
>|||Hi
Taken fro Vyas's web site
CREATE TABLE [dbo].[Events] (
[EventClass] [smallint] NOT NULL ,
[EventName] [varchar] (50) NOT NULL ,
[EventDescription] [varchar] (300) NULL
) ON [PRIMARY]
GO
CREATE UNIQUE CLUSTERED INDEX [UCI_Events_EventClass] ON
[dbo].[Events]([EventClass]) ON [PRIMARY]
GO
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(44,'SP:Stmt
S
tarting','SQL
statement inside a stored procedure is starting.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(45,'SP:Stmt
C
ompleted','SQL
statement inside a stored procedure has completed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(46,'Object:
C
reated','Indicates
that an object has been created, such as for CREATE INDEX, CREATE TABLE, and
CREATE DATABASE statements.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(47,'Object:
D
eleted','Indicates
that an object has been deleted, such as in DROP INDEX and DROP TABLE
statements.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(48,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(49,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(50,'SQL
Transaction','Tracks Transact-SQL BEGIN, COMMIT, SAVE, and ROLLBACK
TRANSACTION statements.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(51,'Scan:St
a
rted','Indicates
when a table or index scan has started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(52,'Scan:St
o
pped','Indicates
when a table or index scan has stopped.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(53,'CursorO
p
en','Indicates
when a cursor is opened on a Transact-SQL statement by ODBC, OLE DB, or
DB-Library.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(54,'Transac
t
ion
Log','Tracks when transactions are written to the transaction log.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(55,'Hash
Warning','Indicates that a hashing operation (for example, hash join, hash
aggregate, hash union, and hash distinct) that is not processing on a buffer
partition has reverted to an alternate plan. This can occur because of
recursion depth, data skew, trace flags, or bit counting.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(56,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(57,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(58,'Auto Upd
ate
Stats','Indicates an automatic updating of index statistics has occurred.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(59,'Lock:De
a
dlock
Chain','Produced for each of the events leading up to the deadlock.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(60,'Lock:Es
c
alation','Indicates
that a finer-grained lock has been converted to a coarser-grained lock (for
example, a row lock escalated or converted to a page lock).')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(61,'OLE DB
Errors','Indicates that an OLE DB error has occurred.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(62,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(63,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(64,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(65,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(66,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(67,'Executi
o
n
Warnings','Indicates any warnings that occurred during the execution of a
SQL Server statement or stored procedure.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(68,'Executi
o
n
Plan','Displays the plan tree of the Transact-SQL statement executed.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(69,'Sort
Warnings','Indicates sort operations that do not fit into memory. Does not
include sort operations involving the creating of indexes; only sort
operations within a query (such as an ORDER BY clause used in a SELECT
statement).')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(70,'CursorP
r
epare','Indicates
when a cursor on a Transact-SQL statement is prepared for use by ODBC, OLE
DB, or DB-Library.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(71,'Prepare
SQL','ODBC,
OLE DB, or DB-Library has prepared a Transact-SQL statement or statements
for use.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(72,'Exec Pre
pared
SQL','ODBC, OLE DB, or DB-Library has executed a prepared Transact-SQL
statement or statements.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(73,'Unprepa
r
e
SQL','ODBC, OLE DB, or DB-Library has unprepared (deleted) a prepared
Transact-SQL statement or statements.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(74,'CursorE
x
ecute','A
cursor previously prepared on a Transact-SQL statement by ODBC, OLE DB, or
DB-Library is executed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(75,'CursorR
e
compile','A
cursor opened on a Transact-SQL statement by ODBC or DB-Library has been
recompiled either directly or due to a schema change.Triggered for ANSI and
non-ANSI cursors.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(76,'CursorI
m
plicitConversion','A
cursor on a Transact-SQL statement is converted by SQL Server from one type
to another.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(77,'CursorU
n
prepare','A
prepared cursor on a Transact-SQL statement is unprepared (deleted) by ODBC,
OLE DB, or DB-Library.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(78,'CursorC
l
ose','A
cursor previously opened on a Transact-SQL statement by ODBC, OLE DB, or
DB-Library is closed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(79,'Missing
Column
Statistics','Column statistics that could have been useful for the optimizer
are not available.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(80,'Missing
Join
Predicate','Query that has no join predicate is being executed. This could
result in a long-running query.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(81,'Server M
emory
Change','Microsoft SQL Server memory usage has increased or decreased by
either 1 megabyte (MB) or 5 percent of the maximum server memory, whichever
is greater.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(82,'User Con
figurable
(0)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(83,'User Con
figurable
(1)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(84,'User Con
figurable
(2)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(85,'User Con
figurable
(3)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(86,'User Con
figurable
(4)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(87,'User Con
figurable
(5)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(88,'User Con
figurable
(6)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(89,'User Con
figurable
(7)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(90,'User Con
figurable
(8)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(91,'User Con
figurable
(9)','Event data defined by the user.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(92,'Data Fil
e Auto
Grow','Indicates that a data file was extended automatically by the
server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(93,'Log File
Auto
Grow','Indicates that a data file was extended automatically by the
server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(94,'Data Fil
e Auto
Shrink','Indicates that a data file was shrunk automatically by the
server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(95,'Log File
Auto
Shrink','Indicates that a log file was shrunk automatically by the server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(96,'Show Pla
n
Text','Displays the query plan tree of the SQL statement from the query
optimizer.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(97,'Show Pla
n
ALL','Displays the query plan with full compile-time details of the SQL
statement executed.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(98,'Show Pla
n
Statistics','Displays the query plan with full run-time details of the SQL
statement executed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(99,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(100,'RPC Out
put
Parameter','Produces output values of the parameters for every RPC.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(101,'Reserv
e
d','')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(102,'Audit S
tatement
GDR','Occurs every time a GRANT, DENY, REVOKE for a statement permission is
issued by any user in SQL Server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(103,'Audit O
bject
GDR','Occurs every time a GRANT, DENY, REVOKE for an object permission is
issued by any user in SQL Server.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(104,'Audit A
dd/Drop
Login','Occurs when a SQL Server login is added or removed; for sp_addlogin
and sp_droplogin.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(105,'Audit L
ogin
GDR','Occurs when a Microsoft Windows® login right is added or removed;
for sp_grantlogin, sp_revokelogin, and sp_denylogin.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(106,'Audit L
ogin Change
Property','Occurs when a property of a login, except passwords, is modified;
for sp_defaultdb and sp_defaultlanguage.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(107,'Audit L
ogin Change
Password','Occurs when a SQL Server login password is changed.Passwords are
not recorded.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(108,'Audit A
dd Login to
Server Role','Occurs when a login is added or removed from a fixed server
role; for sp_addsrvrolemember, and sp_dropsrvrolemember.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(109,'Audit A
dd DB
User','Occurs when a login is added or removed as a database user (Windows
or SQL Server) to a database; for sp_grantdbaccess, sp_revokedbaccess,
sp_adduser, and sp_dropuser.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(110,'Audit A
dd Member to
DB','Occurs when a login is added or removed as a database user (fixed or
user-defined) to a database; for sp_addrolemember, sp_droprolemember, and
sp_changegroup.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(111,'Audit A
dd/Drop
Role','Occurs when a login is added or removed as a database user to a
database; for sp_addrole and sp_droprole.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(112,'App Rol
e Pass
Change','Occurs when a password of an application role is changed.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(113,'Audit S
tatement
Permission','Occurs when a statement permission (such as CREATE TABLE) is
used.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(114,'Audit O
bject
Permission','Occurs when an object permission (such as SELECT) is used, both
successfully or unsuccessfully.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(115,'Audit
Backup/Restore','Occurs when a BACKUP or RESTORE command is issued.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(116,'Audit D
BCC','Occurs
when DBCC commands are issued.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(117,'Audit C
hange
Audit','Occurs when audit trace modifications are made.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(118,'Audit O
bject
Derived Permission','Occurs when a CREATE, ALTER, and DROP object commands
are issued.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(0,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(1,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(2,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(3,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(4,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(5,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(6,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(7,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(8,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(9,'Reserved
'
,'')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(10,'RPC:Com
p
leted','Occurs
when a remote procedure call (RPC) has completed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(11,'RPC:Sta
r
ting','Occurs
when an RPC has started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(12,'SQL:Bat
c
hCompleted','Occurs
when a Transact-SQL batch has completed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(13,'SQL:Bat
c
hStarting','Occurs
when a Transact-SQL batch has started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(14,'Login',
'
Occurs when
a user successfully logs in to SQL Server.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(15,'Logout'
,
'Occurs when
a user logs out of SQL Server.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(16,'Attenti
o
n','Occurs
when attention events, such as client-interrupt requests or broken client
connections, happen.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(17,'Existin
g
Connection','Detects
all activity by users connected to SQL Server before the trace started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(18,'Service
C
ontrol','Occurs
when the SQL Server service state is modified.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(19,'DTCTran
s
action','Tracks
Microsoft Distributed Transaction Coordinator (MS DTC) coordinated
transactions between two or more databases.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(20,'Login
Failed','Indicates that a login attempt to SQL Server from a client
failed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(21,'EventLo
g
','Indicates
that events have been logged in the Microsoft Windows NT® application
log.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(22,'ErrorLo
g
','Indicates
that error events have been logged in the SQL Server error log.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(23,'Lock:Re
l
eased','Indicates
that a lock on a resource, such as a page, has been released.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(24,'Lock:Ac
q
uired','Indicates
acquisition of a lock on a resource, such as a data page.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(25,'Lock:De
a
dlock','Indicates
that two concurrent transactions have deadlocked each other by trying to
obtain incompatible locks on resources the other transaction owns.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(26,'Lock:Ca
n
cel','Indicates
that the acquisition of a lock on a resource has been canceled (for example,
due to a deadlock).')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(27,'Lock:Ti
m
eout','Indicates
that a request for a lock on a resource, such as a page, has timed out due
to another transaction holding a blocking lock on the required resource.
Time-out is determined by the @.@.LOCK_TIMEOUT function, and can be set with
the SET LOCK_TIMEOUT statement.')
INSERT INTO [events]
([EventClass],[EventName],[EventDescription])VALUES(28,'DOP Even
t','Occurs
before a SELECT, INSERT, or UPDATE statement is executed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(29,'Reserve
d
','Use Event
28 instead.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(30,'Reserve
d
','Use Event
28 instead.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(31,'Reserve
d
','Use Event
28 instead.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(32,'Reserve
d
','')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(33,'Excepti
o
n','Indicates
that an exception has occurred in SQL Server.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(34,'SP:Cach
e
Miss','Indicates
when a stored procedure is not found in the procedure cache.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(35,'SP:Cach
e
Insert','Indicates
when an item is inserted into the procedure cache.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(36,'SP:Cach
e
Remove','Indicates
when an item is removed from the procedure cache.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(37,'SP:Reco
m
pile','Indicates
that a stored procedure was recompiled.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(38,'SP:Cach
e
Hit','Indicates
when a stored procedure is found in the procedure cache.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(39,'SP:Exec
C
ontextHit','Indicates
when the execution version of a stored procedure has been found in the
procedure cache.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(40,'SQL:Stm
t
Starting','Occurs
when the Transact-SQL statement has started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(41,'SQL:Stm
t
Completed','Occurs
when the Transact-SQL statement has completed.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(42,'SP:Star
t
ing','Indicates
when the stored procedure has started.')
INSERT INTO [events]
([EventClass],[EventName],& #91;EventDescription])VALUES(43,'SP:Comp
l
eted','Indicates
when the stored procedure has completed.')
"SomaSekhar" <SomaSekhar@.discussions.microsoft.com> wrote in message
news:1F3FC9E1-32F3-4B9E-A2A4-1C3FF2B30588@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> What is the better solution for this. How to get list of EventId's and
> EventClass name on SQL Server 2000. Because i am using
> fn_trace_gettrable('trace file name', default), it's giving in EventClass
> as
> a EventID. Insted of EventID i need EventClass name. How achive this in
> SQL
> Server 2000. In SQL Server 2005 i am using INNER JOIN condion on
> sys.trace_events i am comparing both event ID's i am getiting EventClass
> name. Please check this query,
> --This will work on MS SQL Server 2005
> SELECT TextData,trace_event_id,category_id,name
> FROM fn_trace_gettable('E:\Somu\trace_events.trc', default) EventLog
> INNER JOIN sys.trace_events EventID ON EventLog.EventClass =
> EventID.trace_event_id
> NOTE : in the above query "E:\Somu\trace_events.trc - Insted of this give
> give Ur trace file location"
> What is the equallent Query in MS SQL Server 2000.
> Thanks & Regards
> -SomaSekhar
> "Uri Dimant" wrote:
>|||Hi Uri,
Thank You very much. I will try this, but i want to know one thig with
out using this procedure any system tables is available on SQL Server 2000.
Thanks & Regards
-SomaSekhar
"Uri Dimant" wrote:
> Hi
> Taken fro Vyas's web site
> CREATE TABLE [dbo].[Events] (
> [EventClass] [smallint] NOT NULL ,
> [EventName] [varchar] (50) NOT NULL ,
> [EventDescription] [varchar] (300) NULL
> ) ON [PRIMARY]
> GO
> CREATE UNIQUE CLUSTERED INDEX [UCI_Events_EventClass] ON
> [dbo].[Events]([EventClass]) ON [PRIMARY]
> GO
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(44,'SP:Stm
tStarting','SQL
> statement inside a stored procedure is starting.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(45,'SP:Stm
tCompleted','SQL
> statement inside a stored procedure has completed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(46,'Object
:Created','Indicates
> that an object has been created, such as for CREATE INDEX, CREATE TABLE, a
nd
> CREATE DATABASE statements.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(47,'Object
:Deleted','Indicates
> that an object has been deleted, such as in DROP INDEX and DROP TABLE
> statements.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(48,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(49,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(50,'SQL
> Transaction','Tracks Transact-SQL BEGIN, COMMIT, SAVE, and ROLLBACK
> TRANSACTION statements.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(51,'Scan:S
tarted','Indicates
> when a table or index scan has started.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(52,'Scan:S
topped','Indicates
> when a table or index scan has stopped.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(53,'Cursor
Open','Indicates
> when a cursor is opened on a Transact-SQL statement by ODBC, OLE DB, or
> DB-Library.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(54,'Transa
ction
> Log','Tracks when transactions are written to the transaction log.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(55,'Hash
> Warning','Indicates that a hashing operation (for example, hash join, hash
> aggregate, hash union, and hash distinct) that is not processing on a buff
er
> partition has reverted to an alternate plan. This can occur because of
> recursion depth, data skew, trace flags, or bit counting.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(56,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(57,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(58,'Auto U
pdate
> Stats','Indicates an automatic updating of index statistics has occurred.'
)
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(59,'Lock:D
eadlock
> Chain','Produced for each of the events leading up to the deadlock.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(60,'Lock:E
scalation','Indicates
> that a finer-grained lock has been converted to a coarser-grained lock (fo
r
> example, a row lock escalated or converted to a page lock).')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(61,'OLE DB
> Errors','Indicates that an OLE DB error has occurred.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(62,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(63,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(64,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(65,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(66,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(67,'Execut
ion
> Warnings','Indicates any warnings that occurred during the execution of a
> SQL Server statement or stored procedure.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(68,'Execut
ion
> Plan','Displays the plan tree of the Transact-SQL statement executed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(69,'Sort
> Warnings','Indicates sort operations that do not fit into memory. Does not
> include sort operations involving the creating of indexes; only sort
> operations within a query (such as an ORDER BY clause used in a SELECT
> statement).')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(70,'Cursor
Prepare','Indicates
> when a cursor on a Transact-SQL statement is prepared for use by ODBC, OLE
> DB, or DB-Library.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(71,'Prepar
e SQL','ODBC,
> OLE DB, or DB-Library has prepared a Transact-SQL statement or statements
> for use.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(72,'Exec P
repared
> SQL','ODBC, OLE DB, or DB-Library has executed a prepared Transact-SQL
> statement or statements.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(73,'Unprep
are
> SQL','ODBC, OLE DB, or DB-Library has unprepared (deleted) a prepared
> Transact-SQL statement or statements.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(74,'Cursor
Execute','A
> cursor previously prepared on a Transact-SQL statement by ODBC, OLE DB, or
> DB-Library is executed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(75,'Cursor
Recompile','A
> cursor opened on a Transact-SQL statement by ODBC or DB-Library has been
> recompiled either directly or due to a schema change.Triggered for ANSI an
d
> non-ANSI cursors.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(76,'Cursor
ImplicitConversion','A
> cursor on a Transact-SQL statement is converted by SQL Server from one typ
e
> to another.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(77,'Cursor
Unprepare','A
> prepared cursor on a Transact-SQL statement is unprepared (deleted) by ODB
C,
> OLE DB, or DB-Library.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(78,'Cursor
Close','A
> cursor previously opened on a Transact-SQL statement by ODBC, OLE DB, or
> DB-Library is closed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(79,'Missin
g Column
> Statistics','Column statistics that could have been useful for the optimiz
er
> are not available.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(80,'Missin
g Join
> Predicate','Query that has no join predicate is being executed. This could
> result in a long-running query.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(81,'Server
Memory
> Change','Microsoft SQL Server memory usage has increased or decreased by
> either 1 megabyte (MB) or 5 percent of the maximum server memory, whicheve
r
> is greater.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(82,'User C
onfigurable
> (0)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(83,'User C
onfigurable
> (1)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(84,'User C
onfigurable
> (2)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(85,'User C
onfigurable
> (3)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(86,'User C
onfigurable
> (4)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(87,'User C
onfigurable
> (5)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(88,'User C
onfigurable
> (6)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(89,'User C
onfigurable
> (7)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(90,'User C
onfigurable
> (8)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(91,'User C
onfigurable
> (9)','Event data defined by the user.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(92,'Data F
ile Auto
> Grow','Indicates that a data file was extended automatically by the
> server.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(93,'Log Fi
le Auto
> Grow','Indicates that a data file was extended automatically by the
> server.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(94,'Data F
ile Auto
> Shrink','Indicates that a data file was shrunk automatically by the
> server.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(95,'Log Fi
le Auto
> Shrink','Indicates that a log file was shrunk automatically by the server.
')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(96,'Show P
lan
> Text','Displays the query plan tree of the SQL statement from the query
> optimizer.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(97,'Show P
lan
> ALL','Displays the query plan with full compile-time details of the SQL
> statement executed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(98,'Show P
lan
> Statistics','Displays the query plan with full run-time details of the SQL
> statement executed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(99,'Reserv
ed','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(100,'RPC O
utput
> Parameter','Produces output values of the parameters for every RPC.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(101,'Reser
ved','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(102,'Audit
Statement
> GDR','Occurs every time a GRANT, DENY, REVOKE for a statement permission i
s
> issued by any user in SQL Server.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(103,'Audit
Object
> GDR','Occurs every time a GRANT, DENY, REVOKE for an object permission is
> issued by any user in SQL Server.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(104,'Audit
Add/Drop
> Login','Occurs when a SQL Server login is added or removed; for sp_addlogi
n
> and sp_droplogin.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(105,'Audit
Login
> GDR','Occurs when a Microsoft Windows? login right is added or removed;
> for sp_grantlogin, sp_revokelogin, and sp_denylogin.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(106,'Audit
Login Change
> Property','Occurs when a property of a login, except passwords, is modifie
d;
> for sp_defaultdb and sp_defaultlanguage.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(107,'Audit
Login Change
> Password','Occurs when a SQL Server login password is changed.Passwords ar
e
> not recorded.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(108,'Audit
Add Login to
> Server Role','Occurs when a login is added or removed from a fixed server
> role; for sp_addsrvrolemember, and sp_dropsrvrolemember.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(109,'Audit
Add DB
> User','Occurs when a login is added or removed as a database user (Windows
> or SQL Server) to a database; for sp_grantdbaccess, sp_revokedbaccess,
> sp_adduser, and sp_dropuser.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(110,'Audit
Add Member to
> DB','Occurs when a login is added or removed as a database user (fixed or
> user-defined) to a database; for sp_addrolemember, sp_droprolemember, and
> sp_changegroup.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(111,'Audit
Add/Drop
> Role','Occurs when a login is added or removed as a database user to a
> database; for sp_addrole and sp_droprole.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(112,'App R
ole Pass
> Change','Occurs when a password of an application role is changed.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(113,'Audit
Statement
> Permission','Occurs when a statement permission (such as CREATE TABLE) is
> used.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(114,'Audit
Object
> Permission','Occurs when an object permission (such as SELECT) is used, bo
th
> successfully or unsuccessfully.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(115,'Audit
> Backup/Restore','Occurs when a BACKUP or RESTORE command is issued.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(116,'Audit
DBCC','Occurs
> when DBCC commands are issued.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(117,'Audit
Change
> Audit','Occurs when audit trace modifications are made.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(118,'Audit
Object
> Derived Permission','Occurs when a CREATE, ALTER, and DROP object commands
> are issued.')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(0,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(1,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(2,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(3,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(4,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(5,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(6,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(7,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(8,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(9,'Reserve
d','')
> INSERT INTO [events]
> ([EventClass],[EventName],[EventDescription])VALUES(10,'RPC:Co
mpleted','Occurs
> when a remote procedure call (RPC) has completed.')
> INSERT INTO [events]|||Soma
I'm not aware of it
"SomaSekhar" <SomaSekhar@.discussions.microsoft.com> wrote in message
news:695531DD-50FE-4A48-9578-B070055C0DD3@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> Thank You very much. I will try this, but i want to know one thig with
> out using this procedure any system tables is available on SQL Server
> 2000.
> Thanks & Regards
> -SomaSekhar
> "Uri Dimant" wrote:
>
