SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How can I find who/what process dropped view/table from my sql server 2008 database ?


How can I find who/what process dropped view/table from my sql server 2008 database ?

Author
Message
Joy Smith San
Joy Smith San
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14436 Visits: 3208
Experts,

How can I find who/what process dropped view/table from my sql server 2008 database ?
Auditing is not implemented.

Thanks,
Smith
e4d4
e4d4
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2691 Visits: 2410
If the DB is in full recovery mode, then you could try use software that analyze transaction log eg. http://www.red-gate.com/products/dba/sql-log-rescue/
GilaMonster
GilaMonster
SSC Guru
SSC Guru (554K reputation)SSC Guru (554K reputation)SSC Guru (554K reputation)SSC Guru (554K reputation)SSC Guru (554K reputation)SSC Guru (554K reputation)SSC Guru (554K reputation)SSC Guru (554K reputation)

Group: General Forum Members
Points: 554268 Visits: 47746
You can check the default trace. It logs DDL along with the login. If enough time has passed that the event is not in the default trace, there's likely no way of telling unless you had some custom auditing in place.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Joy Smith San
Joy Smith San
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14436 Visits: 3208
..Thanks Gila... No it happened yesterday only. Can you please guide me how to go about it now.
Since it's very urgent, googling will take time.

Thanks again.
anthony.green
anthony.green
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63306 Visits: 8598
Default Trace - A Beginner's Guide - http://www.sqlservercentral.com/articles/SQL+Server+2005/64547/

If the event is no longer in the trace then and you dont have auditing like DDL triggers, extended events setup then your out of luck finding who dropped it.



How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger


Nils Gustav Stråbø
Nils Gustav Stråbø
SSCertifiable
SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)SSCertifiable (6.8K reputation)

Group: General Forum Members
Points: 6779 Visits: 3575
There is a built in report in SSMS that uses the default trace to show schema change history.

Right click on the database where the table was dropped from, then Reports -> Standard Reports -> Schema Changes History
Eric M Russell
Eric M Russell
SSC Guru
SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)SSC Guru (62K reputation)

Group: General Forum Members
Points: 62874 Visits: 12784
This is why service, application, and user accounts should not be members of the SYSADMIN or DBO role. That would help prevent mysteriously dropped objects and also narrow the list of suspects ...

The default trace would be your best bet. However, if it's not running or for whatever reason doesn't appear to return what looks like a DROP statement, then there are a couple of other stabs you can take at it.

The last statement sent by a connection to SQL Server hangs around in the input buffer. So, if the connection that executed the DROP is still active, and the last command it executed was the DROP, then the following may reveal it. The following will dump the last command executed for each active connection, and SYSPROCESSES will also identify each connection by username, hostname, domain name, etc.
select * into #sp from sys.sysprocesses;
declare @spid int = 0;
while @spid >= 0
begin;
select @spid = min(spid) from #sp where spid > @spid;
if @@rowcount >= 1
begin;
select * from #sp where spid = @spid;
dbcc inputbuffer( @spid );
end else break;
end;



This is even less hopeful. I'm not sure if a DROP statement would be included here, just like default trace it gets cycled out over time, and I'm not even sure how to join back to a specific connection. However, because it's looking at this from another angle, it might still reveal something useful. What it does is query cached statment plans looking for any reference to a DROP.
SELECT deqs.last_execution_time, dest.TEXT AS [Query], *
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
WHERE dest.TEXT like '%DROP%'
ORDER BY deqs.last_execution_time DESC;




"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search