Profiler Question on Continuous Monitoring 1

[wdellin]

I'm new to sql server and have the task of setting up events checks for invalid login, new login. I'm not even at the point of logging on but was told that you have to be logged in to the database for the event trace to occur. I was told if I log off the trace stops. Is this true, even for the login trace? If this is true is there a way to automate the trace?

 

[SQLBill]

That's only true if you start the trace under your login.

Here's my procedure.

1. Create a trace and test it.
2. Script the trace, there's a menu option for this.
3. Create a stored procedure for the trace (add CREATE PROCEDURE myprocname AS to the beginning of the trace script).
4. Run the procedure.
5. Create a job that calls (runs) the procedure.
6. Create a second job that ends the trace.
The first trace creates TraceID of 1
You need to stop a trace (sp_trace_setstatus 1,0)
and then clear the trace (sp_trace_setstatus 1,2)
Clearing the trace DOES NOT delete it. You need to stop and clear the trace to allow it to be replayed. The 1 in the above commands is the TraceID number.

Refer to the BOL for information on sp_trace_setstatus.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine