Hi

I 've SQL Server 2000(client/server) and I want to keep and save all
activities happening in my databases life,and I found SQL Profiler
which trace each steps and show me these activities,
but how can I do this everyday?should I physically come and
run a new trace in SQL Profiler ? and you know that it can
save the result at most in 9999rows table,so when it became to
full it's time to define another one,
another question:to prevent creating different tables for trace
result can we append second and 3th and... result at the end of
first one? totally,is it a correct way to save DB activities for SQLAdmin
and refer to them in several times?
I'd be thankful for your help.
--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

You can automate the profiler via it's system stored
procedure APIs. These stored procs changed dramatically
between versions 7.0 & 2000 though:

For SQL 2000, use: sp_trace_create
For SQL 7.0, use: xp_trace_addnewqueue

Both of these are documented in the respective version's
Books Online.

Do follow Ray's advice - it's usually a bad idea to log
profiler activity to db tables because you're actually
impacting the performance of the db itself whilst doing
the logging! This can sometimes be the straw that breaks
the camel's back if the server starts approaching resource
saturation as the profiler will be consuming resources
that the database server really needs to survive.

Log profiler activity to a file via the abovementioned
APIs (depending on which SQL version you're using) and
then log to an offline db table or load into the profiler
GUI for offline analysis, away from the production server.

HTH

Regards,
Greg Linwood
SQL Server MVP

M

I have answered this in .server.
Please do not post the same questions in multiple newsgroups so people don't
waste time answering questions that have already been answered.

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


"M" <mary2001 (AT) canada (DOT) com> wrote

Thanks,I studied the help about sp_trace_create
but it was just about creating the trace definition,
now how can I start or run it?

On Tue, 1 Jul 2003 04:30:07 -0700, Greg Linwood <g_linwood (AT) hotmail (DOT) com>
wrote:



--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

I tried following:

sp_trace_create -- @traceid = ce_id OUTPUT ,
@options = 2
, @tracefile = N'f:\ttt.trc' , @maxfilesize = 5
, @stoptime = '9:15'

but there was no success,by commenting each row
a new error would be appearred related to datatype of each parameters like
below:

***********
Server: Msg 214, Level 16, State 3, Procedure sp_trace_create, Line 1
Procedure expects parameter '@maxfilesize' of type 'bigint'.

Procedure expects parameter '@tracefile' of type 'nvarchar(128)'.

what's wrong?
any help would be thankful







On Tue, 1 Jul 2003 04:30:07 -0700, Greg Linwood <g_linwood (AT) hotmail (DOT) com>
wrote:



--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

All the parameters are required. What error did you get when you didn't
comment any parameters?

Since @traceid is an output parameter, you must define a variable and use
that as the input parameter.

Make sure that the @tracefile value is a location accessible from the
server, and is not a share name defined on the client.

The @stoptime needs to be a valid datetime value. Please read about datetime
datatype in the SQL Server Books Online. It must include both a date and a
time, and must be later than the current date and time.

To start the trace once you have defined it, use sp_trace_setstatus; see
the description in the Books Online.

--
HTH
----------------
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com


"M" <mary2001 (AT) canada (DOT) com> wrote

now I successed to define a trace with following code
now how can I start it?and how can I make as an autostart
statement when SQL Server will need to autostart?

-----------------it returned 12---------
declare @rc int, @TraceID int
declare @maxfilesize bigint

set @maxfilesize = 5
exec @rc = sp_trace_create @TraceID output, 2,
N'e:\esttrace', @maxfilesize, NULL
print @rc
-----------------

On Tue, 1 Jul 2003 22:18:27 -0700, Kalen Delaney
<replies (AT) public_newsgroups (DOT) com> wrote:





--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

You use sp_trace_setstatus for this. The sp_trace.. family of stored
procedures is documented in SQL Server Books Online - you really should read
these through so you've got a good grasp on what you can do with server-side
tracing..

Regards,
Greg Linwood
SQL Server MVP

"M" <mary2001 (AT) canada (DOT) com> wrote

A good way to configure a trace is to use SQL Profiler itself:
First create a trace in GUI and pick whatever events and columns you want.
Run it manually to verify that it returns what you expect. Once you are
happy with it select File|Script Trace|For SQL 2000 menu option. It will
create a SQL script that configures and starts a trace that you have just
ran.

A side note: Please remember that tracing puts a significant burden on a
server. There may be other ways of accomplishing what you need. If you
really want to go that route, configure your trace so it requests only
events and columns that you really need.

Hope that helps.

Regards,
Maciek Sarnowicz

-----------------------------
This posting is provided "AS IS" with no warranties, and confers no rights.

"M" <mary2001 (AT) canada (DOT) com> wrote

Thanks,there were so many good points,
but is there other way to follow users' activities with low burden on
server?
would anybody please recommend a way to do this?

On Thu, 3 Jul 2003 13:10:35 -0700, Maciek Sarnowicz [MSFT]
<macies (AT) online (DOT) microsoft.com> wrote:



--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/