Monday, December 07, 2009

SQL Server Trace File may eat up more than 300GB hard drive space

I noticed that my SQL Server box ran out of space today. And the Microsoft SQL Server folder is as big as 350GB, however I know I have no my database files saved there. So what is using up more that 300GB space in this folder? Eventually I found tons of audittrace**.trc files in the folder below. After deleting them all, more than 300GB space came back. 

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

Now the question is how can we disable this auditing feature in SQL Server? I ran the following script but it did not help.

Exec sp_configure 'show advanced options', 1
GO
RECONFIGURE;
GO
Exec sp_configure 'default trace enabled',0
GO
RECONFIGURE;

Then I went to the Server property dialog ->Security Setting, noticed the Login Auditing has Both Failed & Success Login turned on, change it back to only audit failed logins. and unchecked Enable C2 Audit Tracing. They were both not on the default settings. And the above two changes finally helped.