How to turn off debug / trace mode for SQL Server 2012

you might find a solution for your issue in the following article:

http://sql-articles.com/articles/general/enable-disable-trace-flags-in-sql-server/

the most likely place where the tracing was enabled is the startup parameters which you could edit with the SQL Server Configuration Manager at

SQL Server Services - SQL Server (InstanceName) - Advanced - Startup parameters

Select all Open in new window

the trace flags might be enabled there by a -Txxxxx option.

if so, save the current entry somewhere and remove the -Txxxxx .
Sara

If you're more the T-SQL guy see http://zarez.net/?p=1593 how to check / configure via sp_configure

Thanks for your responses.

Sarabande, I tried all 3 means of checking for the trace per the article you listed, but saw no signs of the trace present.

Michael, I looked up the article you listed and ran the following command to see what traces are in place:

SELECT * FROM :: fn_trace_getinfo(default)

I took a screen shot of the results and have pasted that into the attached file.  

The full section of the article you referred to is as follows:

SELECT * FROM :: fn_trace_getinfo(default)

This will give you a list of all of the traces that are running on the server.

The property of the trace as represented by the following integers:

1 – Trace Options (@options in sp_trace_create)
2 – FileName
3 – MaxSize
4 – StopTime
5 – Current Trace status (1 = On and 0 = Off)

In viewing the attached file and the resulting 5 lines of results, I see that line 5 shows that a Current Trace has a value of 1 = On.  But I still am not knowing a means of turning this off.  

sql_trace_query_Screenshot.pdf

Appreciate your further assist / direction on this.

Thanks.

Jkirman

You only queried the default trace but you can have more traces running. You can check that by querying the traces that are configured in the SQL Server instance:

SELECT * FROM sys.traces

Select all Open in new window

Also check if there's any database and/or audit active:

SELECT * FROM sys.database_audit_specifications
SELECT * FROM sys.server_audits

Select all Open in new window

Vitor,

Thank you for the suggestions.  The first query:

SELECT * FROM sys.traces

generated a result which I saved to an Excel spreadsheet and have attached.

SQL_trace_query.xlsx

The other 2 queries you provided did not generated blank / null results:

SELECT * FROM sys.database_audit_specifications
SELECT * FROM sys.server_audits

Please let me know if the attached file provides enough information to track down where the trace is coming from, or what next steps you'd recommend.

Thanks.

jkirman

So it's confirmed that you're only having the default trace running.

The other 2 queries you provided did not generated blank / null results:

Did not or did? Because you didn't post any result I will guess didn't return any rows, right?

You can always disable the default trace to see if the generation of files stops:

EXEC sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE;  
GO
EXEC sp_configure 'default trace enabled', 0
GO
EXEC sp_configure 'show advanced options',0
RECONFIGURE WITH OVERRIDE;  

Select all Open in new window

Vitor,

To clarify on my prior post, the 2 other queries I referred to above, and that you had recommended, did not return any rows, so your guess was correct.  Apologies for reporting the result as I did, as I didn't review it carefully before posting it, and it was definitely confusing.

Continuing, I ran the instructions per your last post exactly as you listed them, i.e.:

EXEC sp_configure 'show advanced options',1
RECONFIGURE WITH OVERRIDE;  
GO
EXEC sp_configure 'default trace enabled', 0
GO
EXEC sp_configure 'show advanced options',0
RECONFIGURE WITH OVERRIDE;  

The application log on the server reported the following events:

Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'default trace enabled' changed from 1 to 0. Run the RECONFIGURE statement to install.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
SQL Trace stopped. Trace ID = '1'. Login Name = 'NEIMARKLAW\administrator'.
Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

I then stopped and started all SQL and related services (Agent, Full-text index, VSS Writer, Reporting & Integration services), and tested the SQL application.  Unfortunately the logging continues as it did before, with c. 100K worth of logs generated from a few steps in the SQL application.

Appreciate any additional thoughts on this.

Thanks.

jkirman

Ok. Can you post the last SQL Server error log so we can analyze it?

Vitor,

I've attached the SQL Server error logs concatenated for the last 3 months.  Let me know if this is what you were looking for or if there are other diagnostics you need me to upload.

I also attached the latest SQL Server log, which references an Audit action.  Don't know if that's impacting in any way but I figured you might want to see that as well.

SQL_Server_error_log_2018_02_thru_04.log
SQL_Server_log_2018_04_22.log

Thanks.

jkirman

I couldn't find anything special in those logs, meaning that what's activating those logs doesn't seem to be starting from the SQL Server instance.

Thanks to all for your efforts, though unfortunately nothing has worked to date.

jkirman

Thanks Scott look forwards to further suggestions.

Greetings,

The big mystery on this turned out to be a nothing-burger.  I sent the logs to the application developers, who had previously told me to check with Microsoft on removing the SQL server-based debug / trace action.  Turns out... this is the application's standard operation (!!).  It creates these log files to enable debugging in the event that there is a data handling issue.  I told the vendor engineer that this has to be an issue for every administrator, since if left untended, the logs will eventually fill up the disk.  He said that administrators are used to doing the cleanup and that he's not gotten any complaints.... wow.

So... there is no solution on this.  My apologies for involving all parties in what turned out to be a dead end situation.  Thanks very much again to everyone for your assistance and wishing you a good weekend.

jkirman