June 24, 2008 at 7:17 am
Hi All
Does anybody know if and how I can get SQL Profiler to start automatically on a test server but to log the events of a production server ?
From the test server, I created & scripted the trace, connecting to the production server. Then placed the scripted trace into a stored proc on the test server and created a SQL job to run the stored proc. The only problem is that it logs the activity of the test server.
Thanks in advance.
Preet
June 24, 2008 at 7:22 am
Put the trace into a job on the prod server. It's not a heavy load providing you're writing to a drive that's separate from the data or log files.
The server-side trace has to be run on the server that it's tracing.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 24, 2008 at 10:05 am
Thanks for the reply.
I would run it on the live server but we cant risk any performance problems and we dont have a spare drive in any case. There are many warnings against doing this such as in BOL (Auditing SQL Server Activity). i.e. where the recommendation is to use a non-production server.
I dont necessarily want to run a server side trace, rather to automate SQL Profiler running on a test server but logging activity on a production server. Of course it seems that it can only be automated using a server side trace.
Also there is a suggestion that client sided traces utilise considerable b/width ; http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx
So it seems that there will be a significant performance hit in our case with either method.
June 24, 2008 at 11:48 am
There are many warnings about running profiler on a production server, but those warnings are for the profiler GUI. The recommendation for tracing a production system is to use the server-side trace scripts (like you're doing) and to write the trace to a file on a fast drive on the production system. They have to run on the server that is been traced.
I've often done these kind of traces. The impact is minimal, much less than tracing using the profiler front end.
What drives do you have in your server?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2008 at 10:04 am
We have ;
c: Op Sys
d: Prog Files & SQL log files
e: SQL Data Files
August 8, 2012 at 5:49 am
Extended Events are better than SQL Profiler in terms of performance,scalability. Xevents are introduced in 2008. In SQL Server 2012 Extended events more features are available.
Using SQL Profiler to trace is not supported on production systems.
August 10, 2012 at 5:20 am
kranthi1.aprdc (8/8/2012)
Using SQL Profiler to trace is not supported on production systems.
It's completely supported. It's not recommended on busier servers, but it's fully supported.
p.s. 4 year old thread about SQL 2000 (which does not have extended events).
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply