CODEWITHSUNDEEP
sql-servertrace
pirmas naujas
pirmas naujas

Reputation: 300

how to trace SQL table drop create events

I have some mysterious problem where every day one table in DB (SQL Server 2016) is being recreated (I suppose dropped and created) with old data. I checked various options to try to find what process is doing this, however was unable to do that.

Scheduled Tasks - nothing
SQL Agent Jobs - nothing

How to trace what user/application/anythingelse is doing this ?

I tried launching SQL Profiler and starting manual trace, but after some time (half a day or so) it just stopped.

Upvotes: 1

Views: 951

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46203

The default trace captures schema changes. Review the Schema Change History report or run the query below to retrieve the info in T-SQL. Note that the default trace rollover files are limited to 5 files of up to 20MB each so older events may have rolled off.

--select object created and deleted information from default trace
SELECT 
      trace_table.StartTime
    , te.name
    , trace_table.ObjectName
    , trace_table.ApplicationName
    , trace_table.LoginName
FROM (
    SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)) , 255)) + 'log.trc'
    FROM sys.traces
    WHERE
        is_default = 1
    ) AS trace(path)
CROSS APPLY sys.fn_trace_gettable(trace.path, DEFAULT) AS trace_table
JOIN sys.trace_events AS te ON
    te.trace_event_id = trace_table.EventClass
WHERE 
    EventSubClass = 0
    AND name IN('Object:Created', 'Object:Deleted')
ORDER BY StartTime;

Upvotes: 1

lptr
lptr

Reputation: 6788

create a database trigger and log the create/drop table events:

create table dbo.traceTabledropcreate(EventDataXML xml, LogDatetime datetime default(getdate()));
go
 
create or alter trigger dbtrigger_traceTabledropcreate 
on database
with execute as 'dbo'  
for CREATE_TABLE, DROP_TABLE
as
begin
    set nocount on;
    --insert into dbo.traceTabledropcreate(EventDataXML)
    --values (EVENTDATA());

    declare @sessionxml xml = 
    (
        select EVENTDATA(),
        (
        select *
        from sys.dm_exec_sessions
        where session_id = @@spid
        for xml path('sessioninfo'), type
        )
       for xml path('')
    );

    insert into dbo.traceTabledropcreate(EventDataXML)
    values (@sessionxml);

end
go

---..... and wait....

--..test
create table dbo.testtable(id int)
go
select *
from dbo.traceTabledropcreate
go

drop table dbo.testtable
go
select *
from dbo.traceTabledropcreate
go

Upvotes: 1

Related Questions