May 10, 2011 at 3:05 am
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.
May 10, 2011 at 3:15 am
Unless you had a trace running at the time of the delete, there's no history stored.
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
May 11, 2011 at 1:00 am
That's also what I fear.
May 12, 2011 at 2:14 pm
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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 12, 2011 at 3:29 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2011 at 4:33 pm
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;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 12, 2011 at 4:51 pm
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 11:19 pm
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.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply