Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How can I find who/what process dropped view/table from my sql server 2008 database ? Expand / Collapse
Author
Message
Posted Wednesday, January 23, 2013 12:31 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:25 AM
Points: 1,519, Visits: 2,550
Experts,

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

Thanks,
Smith
Post #1410374
Posted Wednesday, January 23, 2013 12:43 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, August 18, 2014 2:26 PM
Points: 223, Visits: 1,719
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/
Post #1410377
Posted Wednesday, January 23, 2013 12:48 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 6:30 AM
Points: 42,764, Visits: 35,859
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 2008, MVP
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

Post #1410379
Posted Wednesday, January 23, 2013 1:00 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, August 18, 2014 8:25 AM
Points: 1,519, Visits: 2,550
..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.
Post #1410382
Posted Wednesday, January 23, 2013 1:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:20 AM
Points: 5,218, Visits: 5,067
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.




Want an answer fast? Try here
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
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1410395
Posted Wednesday, January 23, 2013 7:15 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:45 AM
Points: 1,853, Visits: 3,449
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
Post #1410562
Posted Thursday, January 24, 2013 8:40 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:40 PM
Points: 1,644, Visits: 4,684
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;

Post #1411175
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse