Scheduled Profiler

I need to get performance stats off a number of live servers. The stats need to cover the time from 07:00 to 19:00 every day.

I'm after things like CPU, Memory and IO, but also I want things like transactions and stored procedures.

Can profiler provide this kind of information, and can I schedule the jobs?

The systems are all running on Windows server 2003 and SQL Server 2005.

Thanks

Gary

Hi,

You should schedule a t-sql task with SQL Server Agent.

Use the sp_trace_create to create a trace, sp_trace_setevent to set up your events for monitoring and sp_trace_setstatus to start, stop or pause a trace.

I hope it helps.

Regards,

Janos

|||

Scheduling server traces is what you would want to do as Janos posted. Rather than trying to set up all of the statements and events yourself, it's much easier to just use Profiler to create the script file. Create the trace you want in Profiler and then go to the menu to File, Export and then select Script Trace Definition. This will create the server trace file that you can schedule in SQL Agent. You can then open these files in profiler, import them into a table for analysis, etc. It's the same file format that Profiler uses.

-Sue

|||

refer these links

http://vyaskn.tripod.com/server_side_tracing_in_sql_server.htm

http://msdn2.microsoft.com/en-us/library/ms191006.aspx

Madhu