Re: Setting up Trace in SQL
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 11/22/03
- Next message: Anirban: "stored procedure"
- Previous message: Hal Fliegelman: "Service Pack 3"
- In reply to: Audrey L: "Re: Setting up Trace in SQL"
- Next in thread: Audrey L: "Re: Setting up Trace in SQL"
- Reply: Audrey L: "Re: Setting up Trace in SQL"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Date: Sat, 22 Nov 2003 13:18:08 -0500
You might find a tool such as Entegra from www.lumigent.com a much better
alternative than running profiler traces. While it does cost some money
it's features and ease of use are unsurpassed. To answer your question
though you can do something like this:
DECLARE @File NVARCHAR(100)
SET @File = N'D:\mssql\audit\AuditFile' + CONVERT(NVARCHAR(8),GETDATE(),112)
exec sp_trace_create @TraceID output, 2, @File
-- Andrew J. Kelly SQL Server MVP "Audrey L" <robwhitz@yahoo.com> wrote in message news:006d01c3b114$f2051ef0$a101280a@phx.gbl... > Hi Andrew, > > The company has a policy and states that we have to audit > our databases. That's why I have created a stored > procedure to handle this. > > This is the command that executes the audit at every > startup. > **exec sp_procoption N'usp_SQLAudit', N'startup', N'true'** > > Within the same SP, it will output the audit info to a > file. It will rollover each time it reaches 5MB, and the > new filename will be AuditFile_1, AuditFile_2 and so forth. > **exec sp_trace_create @TraceID output, 2, > N'D:\mssql\audit\AuditFile'** > > With the above command, how can I specify a unique > filename? It would be pretty cool if I can prefix with > yyyymmdd_AuditFile. > > Thanks, > Audrey > > >-----Original Message----- > >Although I don't usually set up traces to start when the > db restarts I don't > >know why the presence of an existing file would be an > issue. How exactly > >are you starting the trace? Can you simply specify a > name for the file > >that would be unique each time it runs? > > > >-- > > > >Andrew J. Kelly > >SQL Server MVP > > > > > ><anonymous@discussions.microsoft.com> wrote in message > >news:057a01c3b0ba$16ffbbd0$a401280a@phx.gbl... > >> I agree. But supposed if I were to re-start the DB, the > >> audit does not kick off at all coz the trace files still > >> exist in the system. > >> > >> In that case, I would need to move the trace files out > >> from the designated directory. Where and how can I do > >> this? Ot typically, how does the other DBA achieve > this? > >> > >> >-----Original Message----- > >> >There is no way to append it that I know of. What > >> benefit would this give > >> >you? > >> > > >> >-- > >> > > >> >Andrew J. Kelly > >> >SQL Server MVP > >> > > >> > > >> >"Audrey L" <robwhitz@yahoo.com> wrote in message > >> >news:07c601c3afe8$20a4ca90$a101280a@phx.gbl... > >> >> Hi, > >> >> > >> >> I have configured SQL Server to activate a trace at > >> every > >> >> server startup. The main objective is to audit all > >> >> critical events on the DB. > >> >> > >> >> As the maxsize of the trace file is 5MB, I managed to > >> >> configure it to perform a rollover whenever the > >> threshold > >> >> is reached. > >> >> > >> >> One thing that I would like to clarify is: - Suppose > the > >> >> trace file 'TraceA.trc' already exists in the > directory. > >> >> The DB was re-started. New records are not appended > to > >> >> the trace file at all. Is there a way to activate > this? > >> >> Or is there any workaround? > >> >> > >> >> Would appreciate advice. Thanks ... > >> > > >> > > >> >. > >> > > > > > > >. > >
- Next message: Anirban: "stored procedure"
- Previous message: Hal Fliegelman: "Service Pack 3"
- In reply to: Audrey L: "Re: Setting up Trace in SQL"
- Next in thread: Audrey L: "Re: Setting up Trace in SQL"
- Reply: Audrey L: "Re: Setting up Trace in SQL"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
