How know when one Table was droped? HELP-ME

19 May 2006 8:18 PM

Retf

Hi all,

I have one Data Base,

Today one strange thing occur, one table of my data base disappear , I didnt
drop it, then i would like know if have way to determine when this occur;

The last week same thing occur whit one store procedure.

Any one knows, how to help-me.

I use SQL Server 2005 SP1

Thanks to all

20 May 2006 5:05 AM

Omnibuzz

you can have a DDL trigger defined for DROP objects.. Don't know the exact
syntax..
you can check the BOL..
In this DDL trigger you can select the event details and insert it to a
table.

20 May 2006 5:21 AM

hongju

You can DDL trigger for logging and Event Notification in S2K5.

"Retf"ë‹˜ì´ ìž‘ì„±í•œ ë‚´ìš©:

Show quote

> Hi all,
>
> I have one Data Base,
>
> Today one strange thing occur, one table of my data base disappear , I didnt
> drop it, then i would like know if have way to determine when this occur;
>
> The last week same thing occur whit one store procedure.
>
>
> Any one knows, how to help-me.
>
> I use SQL Server 2005 SP1
>
> Thanks to all
>
>
>

22 May 2006 6:39 PM

ivan.penkov

SQL Server 2005 has an always running trace - called "default trace". All object drop/alter/creation is audited (among other things). It keeps the history in up to 5 separate trace files with a limit of 20 MB per file. Since on every SQL server restart a new file is created the history is limited also by the last 5 SQL Server restarts. Here is how to get the information (assuming you have the default file layout):

SELECT * FROM fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default)

If you have lots of trace files under your LOG folder, find the last bunch of continuous file numbers and use the first file number. For instance if you see log_154.trc, log_155.trc, log_156.trc, log_157.trc, use:

SELECT * FROM fn_trace_gettable
('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_154.trc', default)

Thanks,
-Ivan

-----Original Message-----
From: hongjujung
Posted At: Friday, May 19, 2006 10:21 PM
Posted To: microsoft.public.sqlserver.programming
Conversation: How know when one Table was droped? HELP-ME
Subject: RE: How know when one Table was droped? HELP-ME

You can DDL trigger for logging and Event Notification in S2K5.

"Retf"ë‹˜ì´ ìž‘ì„±í•œ ë‚´ìš©:

Show quote

> Hi all,
>
> I have one Data Base,
>
> Today one strange thing occur, one table of my data base disappear , I
> didnt drop it, then i would like know if have way to determine when
> this occur;
>
> The last week same thing occur whit one store procedure.
>
>
> Any one knows, how to help-me.
>
> I use SQL Server 2005 SP1
>
> Thanks to all
>
>
>