How do i know if a trace is running?

Hi all,

We had a problem with a database so i used profiler to see where it came from a couple of days ago. Now the performnce on the server has gone really bad, so one of the things i want to check if maybe the trace is still running somewhere on the background. How can i check this? When i run the profiler, i can't see it in current activity. How can be certain this is or is not the problem?

I'm using SQL 2000 SP4.

Thanks in advance,

John

There is a system function that you can run that will return the information for a trace, or all existing traces. The function call is:

select*from::fn_trace_getinfo(default)

|||

Thanks Tim,

There were none, the problem was somewhere else.

Thanks,

John

How do I get to identify all tables accessed?

How do I get to identify all tables accessed during a particular period of
usage?
Used SQL Profiler and set up the following:
Objects: Object:Closed & Object:Opened.
TSQL SQL: BatchStarting & SQL:StmtCompleted.
Columns:
DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
Filter: Applied a filer to the databaseId as I wanted to identify only
tables accessed in a particular DB.
But on running a simple SQL SELECT – the following columns “DatabaseName,
ObjectId and ObjectName” columns were empty.
Why is this?
Please let me know what I’m missing out on?
(Note: A similar question had been posted earlier on – but there was no
satisfactory answer to it.)
Cheers!
SQLCatz
This is what Books Online has to say about Object:Closed and Opened:
The event classes Object:Closed and Object:Opened are provided for running traces on SQL Server 7.0
and earlier. These objects do not exist in SQL Server 2000.
What events did you get in the trace? If you get TSQL SQL: BatchStarting & SQL:StmtCompleted, then
you can't expect to see any object etc.
Consider capturing the Execution Plan event. You can filter on particular tables using the TextData
column. this is so far the only reliable way I found to audit access for a set of tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:82798F82-2F21-44B8-B0D7-926DB4F8CBF5@.microsoft.com...
> How do I get to identify all tables accessed during a particular period of
> usage?
> Used SQL Profiler and set up the following:
> Objects: Object:Closed & Object:Opened.
> TSQL SQL: BatchStarting & SQL:StmtCompleted.
> Columns:
> DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
> Filter: Applied a filer to the databaseId as I wanted to identify only
> tables accessed in a particular DB.
> But on running a simple SQL SELECT – the following columns “DatabaseName,
> ObjectId and ObjectName” columns were empty.
> Why is this?
> Please let me know what I’m missing out on?
> (Note: A similar question had been posted earlier on – but there was no
> satisfactory answer to it.)
> Cheers!
> SQLCatz
|||Hello Tibor,
Thank you for the prompt reply!
I do not want to filter on certain tables - want to get all the tables that
the t-sql statements access while a set of scripts is running. In order to
filter the tables - I need to have all their names. In this case - I do not
have the complete list. Was hoping that by running the profiler - I'd be able
to get all the tables that were accessed.
Cheers!
SQLCatz

How do I get to identify all tables accessed?

How do I get to identify all tables accessed during a particular period of
usage?
Used SQL Profiler and set up the following:
Objects: Object:Closed & Object:Opened.
TSQL SQL: BatchStarting & SQL:StmtCompleted.
Columns:
DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
Filter: Applied a filer to the databaseId as I wanted to identify only
tables accessed in a particular DB.
But on running a simple SQL SELECT – the following columns “DatabaseName
,
ObjectId and ObjectName” columns were empty.
Why is this?
Please let me know what I’m missing out on?
(Note: A similar question had been posted earlier on – but there was no
satisfactory answer to it.)
Cheers!
SQLCatzThis is what Books Online has to say about Object:Closed and Opened:
The event classes Object:Closed and Object:Opened are provided for running t
races on SQL Server 7.0
and earlier. These objects do not exist in SQL Server 2000.
What events did you get in the trace? If you get TSQL SQL: BatchStarting & S
QL:StmtCompleted, then
you can't expect to see any object etc.
Consider capturing the Execution Plan event. You can filter on particular ta
bles using the TextData
column. this is so far the only reliable way I found to audit access for a s
et of tables.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:82798F82-2F21-44B8-B0D7-926DB4F8CBF5@.microsoft.com...
> How do I get to identify all tables accessed during a particular period of
> usage?
> Used SQL Profiler and set up the following:
> Objects: Object:Closed & Object:Opened.
> TSQL SQL: BatchStarting & SQL:StmtCompleted.
> Columns:
> DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
> Filter: Applied a filer to the databaseId as I wanted to identify only
> tables accessed in a particular DB.
> But on running a simple SQL SELECT – the following columns “DatabaseNa
me,
> ObjectId and ObjectName” columns were empty.
> Why is this?
> Please let me know what I’m missing out on?
> (Note: A similar question had been posted earlier on – but there was no
> satisfactory answer to it.)
> Cheers!
> SQLCatz|||Hello Tibor,
Thank you for the prompt reply!
I do not want to filter on certain tables - want to get all the tables that
the t-sql statements access while a set of scripts is running. In order to
filter the tables - I need to have all their names. In this case - I do not
have the complete list. Was hoping that by running the profiler - I'd be abl
e
to get all the tables that were accessed.
Cheers!
SQLCatz

How do I get to identify all tables accessed?

How do I get to identify all tables accessed during a particular period of
usage?
Used SQL Profiler and set up the following:
Objects: Object:Closed & Object:Opened.
TSQL SQL: BatchStarting & SQL:StmtCompleted.
Columns:
DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
Filter: Applied a filer to the databaseId as I wanted to identify only
tables accessed in a particular DB.
But on running a simple SQL SELECT â' the following columns â'DatabaseName,
ObjectId and ObjectNameâ' columns were empty.
Why is this?
Please let me know what Iâ'm missing out on?
(Note: A similar question had been posted earlier on â' but there was no
satisfactory answer to it.)
Cheers!
SQLCatzThis is what Books Online has to say about Object:Closed and Opened:
The event classes Object:Closed and Object:Opened are provided for running traces on SQL Server 7.0
and earlier. These objects do not exist in SQL Server 2000.
What events did you get in the trace? If you get TSQL SQL: BatchStarting & SQL:StmtCompleted, then
you can't expect to see any object etc.
Consider capturing the Execution Plan event. You can filter on particular tables using the TextData
column. this is so far the only reliable way I found to audit access for a set of tables.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQLCatz" <SQLCatz@.discussions.microsoft.com> wrote in message
news:82798F82-2F21-44B8-B0D7-926DB4F8CBF5@.microsoft.com...
> How do I get to identify all tables accessed during a particular period of
> usage?
> Used SQL Profiler and set up the following:
> Objects: Object:Closed & Object:Opened.
> TSQL SQL: BatchStarting & SQL:StmtCompleted.
> Columns:
> DatabaseId, ObjectName, ObjectId, EventClass, TextClass, TextData
> Filter: Applied a filer to the databaseId as I wanted to identify only
> tables accessed in a particular DB.
> But on running a simple SQL SELECT â' the following columns â'DatabaseName,
> ObjectId and ObjectNameâ' columns were empty.
> Why is this?
> Please let me know what Iâ'm missing out on?
> (Note: A similar question had been posted earlier on â' but there was no
> satisfactory answer to it.)
> Cheers!
> SQLCatz|||Hello Tibor,
Thank you for the prompt reply!
I do not want to filter on certain tables - want to get all the tables that
the t-sql statements access while a set of scripts is running. In order to
filter the tables - I need to have all their names. In this case - I do not
have the complete list. Was hoping that by running the profiler - I'd be able
to get all the tables that were accessed.
Cheers!
SQLCatz

how do I determine what tables/views are being accesed

How can i determine what tables/views are being accesed,
when, how and by who. I have attempted using profiler but
with no joy as yet.
Hi,
You can use a 3rd party tool as well. Tool is Entegra by Lumigent
(www.lumigent.com).
Did you tried the Event clause -- TSQL -- Batch starting option in Profiler
? That gives you all the DML commands executed / duration and
user who executed.
Thanks
Hari
MCDBA
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:175ec01c418a5$3164da20$a401280a@.phx.gbl...
> How can i determine what tables/views are being accesed,
> when, how and by who. I have attempted using profiler but
> with no joy as yet.
|||That only gives me the sp executed, i need to know what
tables/views are used.
basicly i want to know when a table/view is accesed as i
have identified several possible redundant tables and i
want to make sure that they never get accessed before i
remove them

>--Original Message--
>Hi,
>You can use a 3rd party tool as well. Tool is Entegra by
Lumigent
>(www.lumigent.com).
>Did you tried the Event clause -- TSQL -- Batch starting
option in Profiler
>? That gives you all the DML commands executed / duration
and
>user who executed.
>
>Thanks
>Hari
>MCDBA
>
>
>"Mat" <anonymous@.discussions.microsoft.com> wrote in
message
>news:175ec01c418a5$3164da20$a401280a@.phx.gbl...
but
>
>.
>

how do I determine what tables/views are being accesed

How can i determine what tables/views are being accesed,
when, how and by who. I have attempted using profiler but
with no joy as yet.Hi,
You can use a 3rd party tool as well. Tool is Entegra by Lumigent
(www.lumigent.com).
Did you tried the Event clause -- TSQL -- Batch starting option in Profiler
? That gives you all the DML commands executed / duration and
user who executed.
Thanks
Hari
MCDBA
"Mat" <anonymous@.discussions.microsoft.com> wrote in message
news:175ec01c418a5$3164da20$a401280a@.phx
.gbl...
> How can i determine what tables/views are being accesed,
> when, how and by who. I have attempted using profiler but
> with no joy as yet.|||That only gives me the sp executed, i need to know what
tables/views are used.
basicly i want to know when a table/view is accesed as i
have identified several possible redundant tables and i
want to make sure that they never get accessed before i
remove them

>--Original Message--
>Hi,
>You can use a 3rd party tool as well. Tool is Entegra by
Lumigent
>(www.lumigent.com).
>Did you tried the Event clause -- TSQL -- Batch starting
option in Profiler
>? That gives you all the DML commands executed / duration
and
>user who executed.
>
>Thanks
>Hari
>MCDBA
>
>
>"Mat" <anonymous@.discussions.microsoft.com> wrote in
message
> news:175ec01c418a5$3164da20$a401280a@.phx
.gbl...
but
>
>.
>