June 30, 2011 at 12:43 am
I'm tired and ready for bed, so bear with me on the post here...
I'm having all kinds of problem tonight with getting it work, I think...
I set up three trace files. I kicked them off in the Profiler GUI and all was well. I closed Profiler and noticed they stopped collecting data.
1st question: IS there a way to get the trace files to collect data even while Profiler is not up and running? ]
Once I reopened Profiler, I opened the trace files. They all say "DONE" in the bottom left hand corner of the window. When I go File > Run Trace, Run Trace is greyed out.
2nd Question: How do I "restart" a trace after I close it if Run Trace is greyed out?
3rd Question: Can I run these trace files 24/7 through a SQL Server Agent Job?
Kind of like a set it and forget it. I need to capture data all day, every day, so I can use it for analysis and budget planning. I would love a T-SQL to kick all three of these off (in a single job or three separate jobs, it doesn't matter), have them run all all th time, and not have to mess with Profiler.
June 30, 2011 at 1:10 am
First don't use the GUI instead of use server side trace http://www.simple-talk.com/sql/performance/finding-the-causes-of-poor-performance-in-sql-server,-part-1/
As i told use the server side trace and collect the data in any one of your drive and use the fn_trace_gettable collect it.
If it is production server don't run it 24/7. the trace will decrease the over all server performance.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
June 30, 2011 at 1:25 am
1st question: IS there a way to get the trace files to collect data even while Profiler is not up and running? ]
trace and collect data without running profiler GUI
You may refer to Stairway to Server-side Tracing in sqlservercentral.com
http://www.sqlservercentral.com/stairway/72363/
2nd Question: How do I "restart" a trace after I close it if Run Trace is greyed out?
When you setup you trace property, you can save you trace template before run it.
From file->Save as ->Trace template->put a name
when you open new Profiler-> you can choose the template you save from
Trace properties ->use the template-> and choose the name you save early
3rd Question: Can I run these trace files 24/7 through a SQL Server Agent Job?
Normally we don't run trace files 24/7 because it may affect the disk IO (depend where you run and save the trace file) and performance
We only run when we troubleshoot for performance issue
For analysis and budget planning,
example for capacity planning, we can run to capture statistics one per day to capture the trend
for not efficiency query, missing index, we run dmv to get the query instead run the profiler
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply