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

Greetings,

This is regarding an issue I have with an SQL 2012 server.  I had obtained assistance from Microsoft on a performance issue several months ago, and they placed the server into some sort of debug or trace mode as part of their diagnostics.  I thought they turned off the trace mode after diagnostics were completed, but as it turns out they did not.  I found this out after noting that available disk space on the SQL server's C: drive was slowly being reduced over time.  There is a debug or trace mode active that is generating daily folders containing log files for apparently every single transaction being executed on the SQL server.  Folders are named by date, so today's log files folder is under a folder called LOGS, and is named 2018-04-18.  Here is a listing of the files in that folder:

- c. 25 files named all.XX.log where XX goes from 1 thru 22.  Files vary in size from c. 15K to c. 2.5MB
- a file called all.csv which is 31K in size
- 6 files called fatal.X.log where X goes fro 0 to 6.  Files are up to 10K in size
- 15 files named Info.XX.log where XX goes from 0 thru 14.  FIles are 1K to 6K in size.
- 22 files called Trace.XX.log, where XX goes from 1 thru 22.  Files are 22K to 2.2 MB in size.
- 8 files called Warn.XX.log, where XX goes fro 0 thru 7.  Files are 1K in size.

On a typical work day, from 25 to 40 MB worth of files are generated in each daily folder.  Currently I have to manually clean these out once a month to keep the accumulation under control.

I've attached 2 files with a section of the contents of the files called all.0.log and trace.1.log

sample_of_all.0.log
sample_of_trace.1.log

Note that after attaching the 2 log files, I clicked the Embed command in the bottom right of the screen.  I hope this doesn't interfere with my having uploaded the 2 files after attaching them.  If you're unable to access the log files, please let me know and I'll re-attach.

Hopefully these files will provide an idea of what mode the SQL Server is in.  

I'm requesting advise on how to turn off this debug mode and stop the continuous generation of these log files.  Please let me know if you need any more information to assess the situation.

Many thanks,

jkirman
jkirmanPrincipalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sarabandeCommented:
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

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
0
Michael PfisterCommented:
If you're more the T-SQL guy see http://zarez.net/?p=1593 how to check / configure via sp_configure
0
jkirmanPrincipalAuthor Commented:
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
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Vitor MontalvãoMSSQL Senior EngineerCommented:
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

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

Open in new window

0
jkirmanPrincipalAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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;  

Open in new window

0
jkirmanPrincipalAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. Can you post the last SQL Server error log so we can analyze it?
0
jkirmanPrincipalAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
jkirmanPrincipalAuthor Commented:
Thanks to all for your efforts, though unfortunately nothing has worked to date.

jkirman
0
jkirmanPrincipalAuthor Commented:
Thanks Scott look forwards to further suggestions.
0
Mark WillsTopic AdvisorCommented:
Best bet is to call the MS contact, and with a respectful (albeit frustrated) concern. Letting a bit of the frustration show about the MS invoked compromised workplace and the impact it is having. Especially the growing visibility amongst management levels. Needs to be resolved before more Senior levels of management becomes aware......

It does sometimes happen, through no individual fault per se, just an oversight and MS should be able to rectify. Or if a new fault, should be able to rectify.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jkirmanPrincipalAuthor Commented:
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
2
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.