Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2011
    Posts
    4

    Unanswered: Which configuration file I can disable traces

    When I restart the server that hosts the database engine SQL Server 2005 Standard Edition SP4, the trace gets up mytrace-5.trc with a size of 100 MB and id is 2, leaving the hard disk space, then the SQL Server stops execution of the trace due to lack of space. I do not know how to erase the trace, because I do not know where is it.

    The archive log looks like:
    SQL Trace ID 2 was started by login "sa"
    Trace ID '2' was stopped because of an error. Cause: 0x80070070(There is not enough space on the disk.). Restart the trace after correcting the problem.

    I only know how delete a trace, knowing id.
    EXEC sp_trace_setstatus 2, 0
    EXEC sp_trace_setstatus 2, 2

    Can you tell me in which configuration file I can disable all SQL Server traces.
    If I run select * from sys.traces, I just get the default trace:

    id = 1
    status = 1
    path= D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_32736.trc
    max_size = 20
    stop_time = NULL
    max_files = 5
    is_rowset = 0
    is_rollover = 1
    is_shutdown = 0
    is_default = 1
    buffer_count = 2
    buffer_size = 128
    file_position = 2359296
    reader_spid = NULL
    start_time = 2011-11-26 07:01:41.403
    last_event_time = 2011-11-29 22:12:28.160
    event_count = 12851
    dropped_event_count = NULL

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Do you have c2 auditing enabled? run this:
    Code:
    sp_configure 'cw audit mode'
    If the run_value is 1, then you will probably have to start in single user mode, reset c2 audit mode to 0, then restart in multi-user mode.

    Of course, if c2 auditing is enabled, there may be a very good reason for that, so be sure to check, before you set yourself up for a very uncomfortable meeting with the auditors.

  3. #3
    Join Date
    Oct 2011
    Posts
    4
    It is 0.

    I don't know why if it's 0, the trace by default is running and also why when I restarted the server another trace was activated. :-(

    For that, I asking if anyone knows a configuration file to disable all traces of SQL Server 2005.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You're in the proverbial "Rock and a hard place" at the moment.

    Start by searching all of the local drives and mount points on the machine that is running SQL Server looking for a file that starts with mytrace-5. This will be on a disk which is either nearly or completely full now, so it shouldn't be hard to find!

    If that doesn't get you underway, post more information here and we can try other approaches!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2011
    Posts
    4

    Thumbs up

    I found the solution.
    I found a stored procedure "auto_nic_aud_swap_trace" that was running when restarting the SQL Server and enabled a trace.

    I seted to 0: sp_configure 'scan for startup procs'

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •