On a dev-test WINTEL server, our C: drive has almost run out of disk space due to a SQL.LOG file that is 133 GB in size. I used SysInternals Handle.exe tool to determine which process was writing this SQL.LOG file and found that the culprit is sqlservr.exe.

enter image description here

As this SQL.LOG file does not appear to be any of the standard SQL Server log/error log files (they are all configured to write elsewhere), my question is: how can I query this SQL Server 2012 Enterprise instance to determine which job, stored procedure, assembly, etc is writing to this mysterious C:\SQL.LOG file..?

Note: the file itself doesn't contain any clues. It is too large to open in Wordpad but if I query for the first few lines in Powershell, nothing helpful appears...

enter image description here

So I need to find a way of asking SQL Server which bit of itself is writing this C:\SQL.Log file. I guess I need a way of searching within SQL Server for either configuration information (somewhere this filename and path must be set) or for details of file output operations (other than IO operations on database data/log files). Can anyone help me with drafting such a T-SQL query?

  • 2
    Has someone set up ODBC Tracing? – Martin Smith Oct 31 '16 at 17:10
  • SELECT * FROM sys.master_files WHERE physical_name = 'C:\SQL.LOG' could be used to see if there is a database which could be using that file. – Nic Oct 31 '16 at 17:28
  • Could be an audit specification, extended events, etc. I've never seen this on any of my systems so this is most certainly something created manually on your end. – Aaron Bertrand Oct 31 '16 at 18:47

Thank to the comment from Martin Smith above, I was able to determine that the C:\SQL.LOG file was being created by ODBC Tracing that had been started and forgotten by a developer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.