Sql-server – How to disable background tracing in SQL 2005

sql serversql-server-2005

How do I use sp_configure on SQL 2005 (full edition) to disable these 200MB mulithreaded traces that are going on in the background and filling up my hard drive?

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\audittrace_20061029095619_4.trc

Thanks for the answer. Here was the fix:

c:\> osql -E
> sp_configure 'show advanced options', 1
> go
> RECONFIGURE
> go
> sp_configure 'c2 audit mode', 0
> go
> RECONFIGURE
> go
Then restart SQL server.

Best Answer

Have a read of Enabling and Disabling C2 Auditing.

However before you do turn this off, hadn't you better check as to why they are on in the first place? Do you have a business requirement for C2 Audit Traces to be turned on?

If you do have a requirement to keep them, you could write a script to move them every so often to a suitable secure location. By default they are written to the default data directory selected on install. If this is a problem you may be able to change the location of the default data directory but will probably require bouncing the services for it to take affect.

To change the default database file location, open SQL Server Management Studio and right click the Server object, select Properties and then Database Settings.

Related Topic