Post reply

2 SQL Tracefiles on startup

  • March 14, 2016 at 3:26 pm

    #324947

    Hi Guys

    I have 2 trace jobs running on start up

    1 is the defailt trace file, the other tracefile I cannot see how it starts up

    No SQL scheduled jobs on startup

    No Windows Jobs in Task manager that could start it

    This is SQL2012

    Any suggestions

    Thanks

  • March 14, 2016 at 3:55 pm

    #1865079

    It could be a Stored Procedure set to start running on SQL Server Startup. Try the following query to list all Stored Procedures set to start on SQL Start up.

    Select name, create_date

    From sys.procedures

    Where OBJECTPROPERTY(OBJECT_ID, 'ExecIsStartup')=1;

    GO

    -Regards

  • March 14, 2016 at 4:06 pm

    #1865083

    Nope nothing returned using that query

    The file is called audittrace....

  • March 14, 2016 at 4:30 pm

    #1865092

    Do you have C2 auditing switched on?

  • March 14, 2016 at 6:49 pm

    #1865099

    To check if C2 Audit mode is enables, use the following query:

    Select value

    From sys.configurations

    Where name='c2 audit mode';

    GO

    --Value '1' indicates C2 Audit mode is turned on and '0' indicates it's turned off.

    --Regards

  • March 14, 2016 at 7:28 pm

    #1865102

    Thanks Journey Man it was C2 Auditing.

    🙂

  • March 14, 2016 at 7:29 pm

    #1865103

    You guys wouldn't happen to know if turning it off requires a SQL restart

  • March 14, 2016 at 8:28 pm

    #1865111

    Restart is not required to turn off C2 auditing. To stop C2 audit tracing, set 'c2 audit mode' to 0.

    -Regards

  • March 15, 2016 at 2:53 am

    #1865137

    alno (3/14/2016)

    You guys wouldn't happen to know if turning it off requires a SQL restart

    Before you turn it off, make sure it isn't required by your company's audit or security policies.

    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

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

  • March 15, 2016 at 9:58 am

    #1865317

    GilaMonster (3/15/2016)

    alno (3/14/2016)

    You guys wouldn't happen to know if turning it off requires a SQL restart

    Before you turn it off, make sure it isn't required by your company's audit or security policies.

    And if someone turned it on and it isn't causing issues, why turn it off?

    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

  • March 15, 2016 at 3:34 pm

    #1865479

    Drive will run out of Disk Space - which is what happened hence the topic.

    http://qa.sqlservercentral.com/Forums/Post.aspx?SessionID=v3es5t45llaie155aorgdb55

    Audit Log File

    C2 audit mode data is saved in a file in the default data directory of the instance. If the audit log file reaches its size limit of 200 megabytes (MB), SQL Server will create a new file, close the old file, and write all new audit records to the new file. This process will continue until the audit data directory fills up or auditing is turned off. To determine the status of a C2 trace, query the sys.traces catalog view.

    Important note Important

    C2 audit mode saves a large amount of event information to the log file, which can grow quickly. If the data directory in which logs are being saved runs out of space, SQL Server will shut itself down. If auditing is set to start automatically, you must either restart the instance with the -f flag (which bypasses auditing), or free up additional disk space for the audit log.

  • March 15, 2016 at 3:34 pm

    #1865480

    Sorry I pasted the wrong Link

    https://msdn.microsoft.com/en-nz/library/ms187634%28v=sql.110%29.aspx

  • March 23, 2016 at 2:00 pm

    #1867233

    Guys

    I turned off C2 Auditing and restarted sql server

    running the script below shows that C2 auditing is turned off - but it is still creating trace files??

    Select value

    From sys.configurations

    Where name='c2 audit mode';

    GO

    Any suggestions

    Thanks

  • March 23, 2016 at 5:54 pm

    #1867278

    Could you give us the results of this query please

    SELECT *

    FROM sys.configurations

    WHERE name = ‘c2 audit mode’

    It sounds like it didn't get switched off, and this should tell us.

  • March 23, 2016 at 6:04 pm

    #1867281

    name value minimum maximum value_in_use description is_dynamic is_advanced

    c2 audit mode 0 0 1 0 c2 audit mode 0 1

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply