Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server Profiler Expand / Collapse
Author
Message
Posted Tuesday, June 24, 2008 7:17 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:45 AM
Points: 236, Visits: 239
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
Post #522523
Posted Tuesday, June 24, 2008 7:22 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #522531
Posted Tuesday, June 24, 2008 10:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:45 AM
Points: 236, Visits: 239
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.
Post #522680
Posted Tuesday, June 24, 2008 11:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #522781
Posted Wednesday, June 25, 2008 10:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 20, 2013 4:45 AM
Points: 236, Visits: 239
We have ;

c: Op Sys
d: Prog Files & SQL log files
e: SQL Data Files

Post #523517
Posted Wednesday, August 8, 2012 5:49 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 10:40 PM
Points: 327, Visits: 28
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.
Post #1341807
Posted Friday, August 10, 2012 5:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:25 AM
Points: 40,385, Visits: 36,827
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1343299
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse