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

Data Loss Expand / Collapse
Author
Message
Posted Tuesday, May 10, 2011 3:05 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 31, 2012 8:21 AM
Points: 21, Visits: 165
Hi guys,

Due to an unknown cause, some data in one table was deleted. There is no audit trail functionality for the application nor any timestamp field.

The recovery model of the database is set to FULL but the data loss happened days before since the backup yesterday was already affected.

Is there any measures that we can take to find the user that deleted the data?
Like query the last users that logged-in to the server and accessed the database? or
get the all the DML statements that were executed to the database? or any related diagnostic measures.

Thank you.
Post #1105944
Posted Tuesday, May 10, 2011 3:15 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 39,952, Visits: 36,307
Unless you had a trace running at the time of the delete, there's no history stored.


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 #1105954
Posted Wednesday, May 11, 2011 1:00 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 31, 2012 8:21 AM
Points: 21, Visits: 165
That's also what I fear.
Post #1106664
Posted Thursday, May 12, 2011 2:14 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 1,669, Visits: 4,769
richarddelcarmen (5/11/2011)
That's also what I fear.

You may already know this, but had you been running SQL Server 2005+, you could query T-SQL commands from the recent past from either the Default Trace (which by default is always running) or sys.db_exec_requests. If you're running SQL Server 2000, and there is a possibility for someone other than yourself to execute ad-hoc T-SQL statement, then I'd suggest keeping a minimal Profiler trace running, something that at least captures enough events and column to keep track of ad-hoc SQL commands, perhaps keep the output sent to a network folder rather than table to minimze performace hit. Also, take a look at what user are in dbo, db_datawriter, or sysadmin role.
Post #1108051
Posted Thursday, May 12, 2011 3:29 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 39,952, Visits: 36,307
Eric M Russell (5/12/2011)
You may already know this, but had you been running SQL Server 2005+, you could query T-SQL commands from the recent past from either the Default Trace (which by default is always running)


The default trace does not record any DML, so it would not have helped with tracing deletes.
sys.db_exec_requests only shows queries which are currently running, not historical ones.

What I said is pretty true on SQL 2005/2008 as well. Unless there was a trace/trigger/audit in place at the time the delete happened, tracking who ran it, from where, with what is not possible.



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 #1108093
Posted Thursday, May 12, 2011 4:33 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:29 PM
Points: 1,669, Visits: 4,769
GilaMonster (5/12/2011)
Eric M Russell (5/12/2011)
You may already know this, but had you been running SQL Server 2005+, you could query T-SQL commands from the recent past from either the Default Trace (which by default is always running)


The default trace does not record any DML, so it would not have helped with tracing deletes.
sys.db_exec_requests only shows queries which are currently running, not historical ones.

What I said is pretty true on SQL 2005/2008 as well. Unless there was a trace/trigger/audit in place at the time the delete happened, tracking who ran it, from where, with what is not possible.

You are correct about the Default Trace not logging DML statements. It will trace stored procedure calls and SQL select, even DDL, but not DML (go figure).
When I mentioned dm_exec_requests earlier, I was thinking about dm_exec_query_stats. It is possible to tease some information from SQL Server's plan cache by filtering on statement text and last execution time. Also sorting by number of writes and execution time can help narrow it down, if there are a large number of delete statement executions. Again, what I'm doing below is 2005+, but perhaps someone knows of a way to pull it off in SQL Server 2000.

select top 1000 creation_time, last_execution_time, (max_elapsed_time / 1000000.0) max_elapsed_seconds, max_logical_writes, st.text as statement_text
from sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
where last_execution_time > '2011-05-12'
and st.text like 'delete%'
order by max_logical_writes desc;

creation_time last_execution_time max_elapsed_seconds max_logical_writes statement_text
----------------------- ----------------------- ------------------- -------------------- ----------------------------------------
2011-05-12 17:13:40.140 2011-05-12 17:13:40.140 0.000000000 0 DELETE FROM Temp.MyTable;
Post #1108107
Posted Thursday, May 12, 2011 4:51 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:49 AM
Points: 39,952, Visits: 36,307
Eric M Russell (5/12/2011)
You are correct about the Default Trace not logging DML statements. It will trace stored procedure calls and SQL select, even DDL, but not DML (go figure).


It doesn't trace stored procedure calls or selects. Both are too high-volume and the default trace is a light-weight trace.
I've got servers where stored proc calls alone generate 250MB+ of trace data in 30 minutes. The default trace keeps up to a maximum of 5 files of 20MB each.

This blog post lists the events it traces. There are no T-SQL or Stored Procedure events.
http://blogs.technet.com/b/vipulshah/archive/2007/04/16/default-trace-in-sql-server-2005.aspx

When I mentioned dm_exec_requests earlier, I was thinking about dm_exec_query_stats. It is possible to tease some information from SQL Server's plan cache by filtering on statement text and last execution time.


Providing the plan is still in cache, so not aged out, no restart of SQL, no index rebuilds, statistics updates, etc. Won't tell you who ran it though.

That information is not available in SQL 2000.



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 #1108112
Posted Monday, May 16, 2011 11:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 31, 2012 8:21 AM
Points: 21, Visits: 165
Thank you everyone for your insights.

We'll be more prepared next time and set-up a trace to audit any changes in the database.
Post #1109963
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse