Click here to monitor SSC
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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2073 Visits: 3197
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
SSC Veteran
SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)SSC Veteran (275 reputation)

Group: General Forum Members
Points: 275 Visits: 2397
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-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47196 Visits: 44364
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
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2073 Visits: 3197
..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
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6091 Visits: 6069
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
When a question, really isn't a question - Jeff Smith
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


Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2013 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
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4582 Visits: 9511
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