0

I want to give our IT a hand regarding the creation of a DefaultJob, that executes himself every server restart or start (Windows SQL Server 2012)

There is a trace that always activates himself after the server restarts or starts (after a hard shutdown or updates).

Basically I want to execute the following query (deactivate the Trace with the ID 1) automatically over a DefaultJob (a Job in the SQL Server Agent):

exec sp_trace_setstatus 1,0

Before executing it, I want to check if the trace with the ID 1 is active, if yes, then execute the query, if not, do nothing.

Any help would be much appreciated.

If you guys have questions, just ask... :-)

Thanks a lot in advance. Cheers!

jarlh
  • 42,561
  • 8
  • 45
  • 63
A_ben
  • 29
  • 6
  • You could create a SQL Server Agent job with a schedule type of "Start automatically when SQL Server Agent starts". But if your intent simply to disable the default trace, a better option would be to set the "default trace enabled" system configuration option to 0. Not sure why you would want to disable it though as the default trace does sometime provide useful forensic info with minimal overhead. – Dan Guzman Apr 30 '21 at 15:08
  • 1
    Another option would be to simply create a procedure in master and set it to run on startup – Stu Apr 30 '21 at 15:23
  • 1
    wrt. Stu's comment, see [sp_procoption (Transact-SQL)](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-procoption-transact-sql). – AlwaysLearning Apr 30 '21 at 15:24
  • Hey guys, thanks for your Input. I ended up building a procedure and I'm running it on SQL-Startup (I'll post the whole thing bellow). However I'm not just setting the trace status to 0, instead I'm disabling the whole trace. Thanks @DanGuzman. The reason why I want to disable it, is because the performance is horrendous when this trace is on... the IT needs a new Server right away... :-) – A_ben May 01 '21 at 08:12
  • **--STEP_1 --> Enable Scan for Startup Proc** EXEC sys.sp_configure N'scan for startup procs', N'1' GO RECONFIGURE WITH OVERRIDE GO **--STEP_2 --> check** SELECT VALUE, VALUE_IN_USE, DESCRIPTION FROM SYS.CONFIGURATIONS WHERE NAME = 'scan for startup procs' – A_ben May 01 '21 at 08:26
  • **--STEP_3 --> create procedure** USE [master] GO create procedure [dbo].[sp_DISABLETRACE_SP] AS BEGIN EXEC sp_configure 'default trace enabled', 1 RECONFIGURE END GO **--STEP_5 --> Configure SP To run at Startup** USE master GO EXEC SP_PROCOPTION sp_DISABLETRACE_SP, 'STARTUP', 'ON' GO **--STEP_5 --> Restart SQL to TEST** – A_ben May 01 '21 at 08:27
  • @A_ben, I'm more than a little surprised you see significant impact with the default trace running. Out of curiosity, do you have extremely high frequency events in your workload captured by the trace? – Dan Guzman May 01 '21 at 11:50
  • @DanGuzman, the impact is more than significant... just an example: I have a big query in a view which takes 3 to 4 minutes to execute when the trace is ON, when the trace is OFF it takes no more than 5 seconds. Like I said, I'm giving the IT a hand because of some infrastructure knowledge I have, but I'm no specialist in the area, my position is another one... :-) to answer your question: we had a look in the past and yes, high frequency events where visible after running the trace. It's important to say that the server tech is not the best as well... (See the next post) – A_ben May 01 '21 at 22:56
  • ... it's an old Server and I'm pretty sure the configuration is not optimal, besides there is far to much stuff running on the same server (tomcats, other web services, ...), the Database is huge... BUT, the IT is migrating to the Cloud soon and they have consultancy now, so I'm pretty sure this problem it's close to an end. – A_ben May 01 '21 at 23:01
  • @A_ben, glad to see you're putting the old server out of it's misery. Having never seen, or even heard of, the default trace have such a negative performance impact since it was introduced in SQL 2005, I was curious as to the root cause. The problem `SELECT` query might generate warnings for spills and missing stats but those are not high frequency. – Dan Guzman May 02 '21 at 09:56
  • @DanGuzman, I'm more than glad as well... and relieved, I must say... :-) Anyways, thanks for the nice chat and take care! – A_ben May 03 '21 at 11:39

0 Answers0