Recently, when I did daily checks on ETL processes, and then found one of ETLs failed on a few tables. Later on I realised those tables had been dropped somehow. However no one knew who did it, when did it and why did it. To investigate the crime, SQL Server default trace became the most useful tool, I found.
Firstly, you need to check if ‘default trace enabled’ through the following two ways.
1. Run sp_configure
-- 'default trace enabled' only shows when 'show advanced options' is enabled
sp_configure 'show advanced options', 1
reconfigure
GO
-- check if 'default trace enabled'has 1 for both 'config_value' and 'run value'
sp_configure
2. Or run the following script to check the setting
SELECT* FROM sys.configurations WHERE configuration_id = 1568
