Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Loss


Data Loss

Author
Message
richarddelcarmen
richarddelcarmen
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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.
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: 47415 Visits: 44405
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

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


richarddelcarmen
richarddelcarmen
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 165
That's also what I fear.
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4657 Visits: 9579
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.


"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."
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: 47415 Visits: 44405
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

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


Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4657 Visits: 9579
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;


"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."
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: 47415 Visits: 44405
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

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


richarddelcarmen
richarddelcarmen
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 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.
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