Who Dropped The Tables?

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

Secondly, after you make sure the default trace has been enabled, you need to check the trace file direcotry. Normally they are under SQL Server Log folder (C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG). Let’s assume that’s where our .trc files are saved.

Now, we shall start the investigation. There is my script.

SELECT

      SPID,

      OBJECTID,

      HOSTNAME,

      DATABASENAME,

      OBJECTNAME,

      STARTTIME,

      'EVENTCLASS' = CASE WHEN EVENTCLASS = 46 THEN 'CREATE'

                          WHEN EVENTCLASS = 47 THEN 'DROP'

                         WHEN EVENTCLASS = 164 THEN 'ALTER'

   END,

      OBJECTTYPE,

      SERVERNAME,

      LOGINNAME,

      APPLICATIONNAME

  
  FROM

      ::FN_TRACE_GETTABLE('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\LOG_857.TRC', 2 )

  WHERE 
    EVENTCLASS IN (47, 46, 164)  

AND EVENTSUBCLASS = 0

AND OBJECTNAME IS NOT NULL

AND DATABASEID <> 2 --tempdb is excluded and you can also specify the database you want to investigate only

AND STARTTIME BETWEEN '2012-01-25' AND '2012-01-26' --specify the investigation time

Finally, I caught who dropped the tables and when…although it wasn’t a joy to tell people off, the crime investigation was definitely enjoyable bit. :)