July 16, 2012 at 12:54 am
Hi Guys,
Am new to profiler.
For example, I have this set up: (client side tracing)
Host A: SQL Server 2005 Production Database
Host B: Profiler GUI started and running to capture required information from Host A and write to a trace file on local disk.
Whenever Host B restarted, profiling will stop and the GUI profiler will be closed until someone manual open the profiler program. Is there anyway to auto startup profiler after server restart for client-side tracing?
Let's say if i decide to use server side trace? In my example here, i supposed the job must be scheduled over at the production db end? and the trace file location will be a mapped drive since i will be writing over to a remote server local disk?
thanks
July 16, 2012 at 11:46 am
chewychewy (7/16/2012)
Hi Guys,Am new to profiler.
For example, I have this set up: (client side tracing)
Host A: SQL Server 2005 Production Database
Host B: Profiler GUI started and running to capture required information from Host A and write to a trace file on local disk.
Whenever Host B restarted, profiling will stop and the GUI profiler will be closed until someone manual open the profiler program. Is there anyway to auto startup profiler after server restart for client-side tracing?
Let's say if i decide to use server side trace? In my example here, i supposed the job must be scheduled over at the production db end? and the trace file location will be a mapped drive since i will be writing over to a remote server local disk?
thanks
Ideally you should setup a server-side trace to write to a local file. If you want the trace running continuously setup a sensible file size and number of rollover files and pull the files off the server periodically to Host B where you can open them in Profiler and analyze them.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 17, 2012 at 7:36 am
chewychewy (7/16/2012)
Hi Guys,Am new to profiler.
For example, I have this set up: (client side tracing)
Host A: SQL Server 2005 Production Database
Host B: Profiler GUI started and running to capture required information from Host A and write to a trace file on local disk.
Whenever Host B restarted, profiling will stop and the GUI profiler will be closed until someone manual open the profiler program. Is there anyway to auto startup profiler after server restart for client-side tracing?
Let's say if i decide to use server side trace? In my example here, i supposed the job must be scheduled over at the production db end? and the trace file location will be a mapped drive since i will be writing over to a remote server local disk?
thanks
As opc.three said, you should write to a local disk because a trace can affect performance so you want it to write to as a fasta disk as possible. If you have to go remote use a UNC path not a mapped drive as mapped drives are not dependable and I know there are issues because trace is running using the SQL Server service account so the drive is not mapped because that account is not logged into the console.
In order to get the trace to continue after a reboot or service restart you need to create a script that creates the trace and then do 1 of 2 things:
1. Create a stored procedure in master that creates and starts the trace. Mark the procedure for startup so it runs when the SQL Server starts.
2. Put the script or call a stored procedure with the script to create and start the trace in SQL Server Agent job that is set to run at service start.
Traces are not persisted across service restarts so you need to have something that creates and starts the trace. When you upgrade to 2008+ you should look at Extended Events as XEvents sessions are persisted across service restarts and can be marked to start automatically.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply