Re: Logging

From: David Gugick (davidg-nospam_at_imceda.com)
Date: 12/08/04 

Date: Wed, 8 Dec 2004 12:25:28 -0500

stoney wrote:
> Thanks David,
>
> Can you explain a little more what you mean about TextData Column
>
> "David Gugick" wrote:
>
>> stoney wrote:
>>> Is there a way I can turn logging on SQL 2000 for all users that
>>> have usernames that begin with 'ab%' on what activity they have
>>> performed on the db to a sql table?
>>>
>>> Any other suggestions that I can try?
>>>
>>> Thanks,
>>> Stoney
>>
>> You could run Profiler using a filter for the users and one on the
>> TextData column to pull out the table name. Better to run a
>> server-side trace once everything looks right in Profiler. You can
>> script a Profiler trace using the File - Script Trace option.
>>
>> --
>> David Gugick
>> Imceda Software
>> www.imceda.com

You'll need to read up on Profiler in BOL. It's just one of the many
columns of data you can capture from a trace. It contains executed SQL
statements (among other things).

Trap the following events: SQL:StmtStarting, RPC:Starting,
SP:StmtStarting
Add a filter for the users on the LoginName (make sure the filter works
first by examining how the LoginName data looks in Profiler) - May need
to be something like %\ab% for NT Authentication
Add a filter on TextData for the table name. Something like %table_name%

Add just the basic columns: EventClass, StartDate, TextData,
Application, LoginName, SPID and see if the output looks like what you
want. You can leave Profiler running from the client (not ideal) or
change some of the Profiler parameters ("Let server run trace" and set
the Rollover and save to a file) and then script the trace. Use the
trace script and create a job in SQL Server Agent that runs the trace
(never save a trace file over the network. always to a local server
drive with sufficient space).

You can start and stop the job from SQL EM and query the trace using
fn_trace_gettable system function. 

-- 
David Gugick
Imceda Software
www.imceda.com

Relevant Pages

  • Re: SQL Server 2005 - Server side trace script
    ... When I read the .trc file using the fn_trace_gettable function, I DO see the TextData column!! ... If I open the same file using Profiler, ... To make things consistent I usually just include the textdata column and exclude the binary when I create the trace. ... declare @TraceID int ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Server 2005 - Server side trace script
    ... TextData column in the output, but instead see the BinaryData column. ... By default Profiler will not have the TextData column checked for the RPC events since it can get that information from the binary data column which is usually more efficient. ... To make things consistent I usually just include the textdata column and exclude the binary when I create the trace. ... declare @TraceID int ...
    (microsoft.public.sqlserver.tools)
  • Re: SQL Server 2005 - Server side trace script
    ... Did you ensure that the TextData column is checked off as one of the columns in Profiler in the first place? ... To make things consistent I usually just include the textdata column and exclude the binary when I create the trace. ... declare @TraceID int ...
    (microsoft.public.sqlserver.tools)
  • Re: Profiler
    ... Profiler limits amount of data displayed in TextData column in the grid, ... Excellent write-up on server side trace. ...
    (microsoft.public.sqlserver.tools)
  • Re: Problem using SP_TRACE_SETFILTER - it doesnt seem to apply th
    ... I was intimating that you open profiler and set the filter ... I don't dispute that your trace runs fine. ...
    (microsoft.public.sqlserver.server)
Loading