Data Loss

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Data Corruption » Data Loss

Data Loss

Rate Topic

Display Mode

Topic Options

Author

Message

richarddelcarmen

Posted Tuesday, May 10, 2011 3:05 AM

Grasshopper

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

GilaMonster

Posted Tuesday, May 10, 2011 3:15 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 38,028, Visits: 30,334

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

richarddelcarmen

Posted Wednesday, May 11, 2011 1:00 AM

Grasshopper

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

Eric M Russell

Posted Thursday, May 12, 2011 2:14 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 1,184, Visits: 3,414

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.

"Winter Is Coming"

Post #1108051

GilaMonster

Posted Thursday, May 12, 2011 3:29 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 38,028, Visits: 30,334

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

Eric M Russell

Posted Thursday, May 12, 2011 4:33 PM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 7:38 AM
Points: 1,184, Visits: 3,414

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)

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;

"Winter Is Coming"

Post #1108107

GilaMonster

Posted Thursday, May 12, 2011 4:51 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:37 PM
Points: 38,028, Visits: 30,334

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

richarddelcarmen

Posted Monday, May 16, 2011 11:19 PM

Grasshopper

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 »

Permissions