June 12, 2009 at 9:28 am
Is there a DBCC command or similar command that will tell me what recent commands have been executed against a database?
I have never used a Trace or Profiler and would prefer not to here, since the database exists at a customer site. We are trying to ascertain what command may have been issued that caused data to be removed from the system.
Can anyone offer a suggestion here?
June 12, 2009 at 9:32 am
without the trace running, i dont think its possible to track deleted data. Other method is you can write a trigger on the base tables that logs dml information in another table..
June 12, 2009 at 9:35 am
you'd be looking for a DELETE or TRUNCATE command; there's nothing in place to look at where it happened.
the default trace would have information if the table was dropped, but not for anything that was deleted.
you'd need to create a trace specifically tracking DML events for anything occurring in the future.
even if you had a trace going, that would only be a pointer to who and when deleted the data, there would be no way to recover the data from a trace.
recovery of the data can only be accomplished with a backup, a third part log reader(IF the database was in FULL backup mode and has not been backed up yet), or with a suite of audit triggers that saves the data to a history table.
have any more information about the condition of the database? is it in FULL recovery mode, has it been backed up?
Lowell
June 12, 2009 at 9:50 am
There is this SQL I found on this website that uses default trace of ur SQL 2005, it will give u some information but not much about what could have happened to ur object and through which login, hostname. But this traces gets refreshed so information is limited:
declare
@TraceFileName nvarchar(256)
set
@TraceFileName = (select path from sys.traces where is_default = 1)
-- get general name for Default Trace (remove rollover number)
set
@TraceFileName =
substring
(@Tracefilename, 1, (charindex ('\log_', @tracefilename) + 3)) + '.trc'
-- sample query: get info about recently added, deleted and modified
-- stored procedures in a database
select
ev
.name
,
tr.StartTime
,
tr.DatabaseID
,
tr.DatabaseName
,
tr.ObjectID
,
tr.ObjectName as 'Trace ObjectName'
,
o.name as 'Sysobjects Name'
,
o.type_desc
,
tr.ObjectType
/* see 'ObjectType Trace Event Column' in BOL to get a translation of these type codes */
,
sv.subclass_name as 'ObjectAbbrv'
,
tr.LoginName
,
tr.HostName
,
tr.ApplicationName
from
fn_trace_gettable(@TraceFileName, default) tr
join
sys.trace_events ev
on
tr.eventclass = ev.trace_event_id
join
sys.trace_subclass_values sv
on
tr.eventclass = sv.trace_event_id
and
tr.ObjectType = sv.subclass_value
and
sv.trace_column_id = 28
left
join sys.objects o
on
tr.ObjectID = o.object_id
where
eventclass in (46, 47, 164) -- object created, deleted or altered
and
eventsubclass = 1 -- only committed act
June 12, 2009 at 10:35 am
Thank you all for the replies. We tried the script above and found that there were no entries for
Object:Deleted
on our server at the customer site. So it's back to the drawing board for us. We have missing data, and no proof of where it went, when, or why.
The trace step will work for going forward, as would the trigger. We just need to rule out a user intentionally deleting data (to sabotage the project).
June 12, 2009 at 10:41 am
Object:Deleted would be the dropping of a table, foe example. the default trace only tracks object level events: CREATE/ALTER/DROP of database objects (DDL)
you would need a DML trace(Data Manipulation)
I'm adding an example below, but like I mentioned, it's fgood for figuring out who and when for fingerpointing, but not for recover.
do you have a backup to restore the missing data to as close as possible before the delete?
anyway, this might help in the future: here is a proc that i use to create a trace to watch SQL statements occuring, as well as their performance., and a view to see the trace whenever it needs to be reviewed:
Create procedure sp_AddMyTrace
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON
BEGIN TRAN
declare @path nvarchar(256)
declare @traceidout int
declare @maxfilesize bigint
declare @maxRolloverFiles int
declare @on bit
set @on = 1
set @maxRolloverFiles = 2
set @maxfilesize = 50
--we want the current trace folder
--ie c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG SELECT @path = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) FROM sys.traces WHERE is_default = 1
SET @path = @path + N'MyDMLtrace' --system appends .trc automatically for the filename
--create the trace
exec sp_trace_create @traceidout output, @maxRolloverFiles, @path, @maxfilesize, NULL
--for the Event Every SQL statement completed, capture all 64 columns of accessible data
exec sp_trace_setevent @traceidout, 12, 1, @on --SQL:BatchCompleted,TextData
exec sp_trace_setevent @traceidout, 12, 2, @on --SQL:BatchCompleted,BinaryData
exec sp_trace_setevent @traceidout, 12, 3, @on --SQL:BatchCompleted,DatabaseID
exec sp_trace_setevent @traceidout, 12, 4, @on --SQL:BatchCompleted,TransactionID
exec sp_trace_setevent @traceidout, 12, 5, @on --SQL:BatchCompleted,LineNumber
exec sp_trace_setevent @traceidout, 12, 6, @on --SQL:BatchCompleted,NTUserName
exec sp_trace_setevent @traceidout, 12, 7, @on --SQL:BatchCompleted,NTDomainName
exec sp_trace_setevent @traceidout, 12, 8, @on --SQL:BatchCompleted,HostName
exec sp_trace_setevent @traceidout, 12, 9, @on --SQL:BatchCompleted, ClientProcessID
exec sp_trace_setevent @traceidout, 12,10, @on --SQL:BatchCompleted,ApplicationName
exec sp_trace_setevent @traceidout, 12,11, @on --SQL:BatchCompleted,LoginName
exec sp_trace_setevent @traceidout, 12,12, @on --SQL:BatchCompleted,SPID
exec sp_trace_setevent @traceidout, 12,13, @on --SQL:BatchCompleted,Duration
exec sp_trace_setevent @traceidout, 12,14, @on --SQL:BatchCompleted,StartTime
exec sp_trace_setevent @traceidout, 12,15, @on --SQL:BatchCompleted,EndTime
exec sp_trace_setevent @traceidout, 12,16, @on --SQL:BatchCompleted,Reads
exec sp_trace_setevent @traceidout, 12,17, @on --SQL:BatchCompleted,Writes
exec sp_trace_setevent @traceidout, 12,18, @on --SQL:BatchCompleted,CPU
exec sp_trace_setevent @traceidout, 12,19, @on --SQL:BatchCompleted,Permissions
exec sp_trace_setevent @traceidout, 12,20, @on --SQL:BatchCompleted,Severity
exec sp_trace_setevent @traceidout, 12,21, @on --SQL:BatchCompleted,EventSubClass
exec sp_trace_setevent @traceidout, 12,22, @on --SQL:BatchCompleted,ObjectID
exec sp_trace_setevent @traceidout, 12,23, @on --SQL:BatchCompleted,Success
exec sp_trace_setevent @traceidout, 12,24, @on --SQL:BatchCompleted,IndexID
exec sp_trace_setevent @traceidout, 12,25, @on --SQL:BatchCompleted,IntegerData
exec sp_trace_setevent @traceidout, 12,26, @on --SQL:BatchCompleted,ServerName
exec sp_trace_setevent @traceidout, 12,27, @on --SQL:BatchCompleted,EventClass
exec sp_trace_setevent @traceidout, 12,28, @on --SQL:BatchCompleted,ObjectType
exec sp_trace_setevent @traceidout, 12,29, @on --SQL:BatchCompleted,NestLevel
exec sp_trace_setevent @traceidout, 12,30, @on --SQL:BatchCompleted,State
exec sp_trace_setevent @traceidout, 12,31, @on --SQL:BatchCompleted,Error
exec sp_trace_setevent @traceidout, 12,32, @on --SQL:BatchCompleted,Mode
exec sp_trace_setevent @traceidout, 12,33, @on --SQL:BatchCompleted,Handle
exec sp_trace_setevent @traceidout, 12,34, @on --SQL:BatchCompleted,ObjectName
exec sp_trace_setevent @traceidout, 12,35, @on --SQL:BatchCompleted,DatabaseName
exec sp_trace_setevent @traceidout, 12,36, @on --SQL:BatchCompleted,FileName
exec sp_trace_setevent @traceidout, 12,37, @on --SQL:BatchCompleted,OwnerName
exec sp_trace_setevent @traceidout, 12,38, @on --SQL:BatchCompleted,RoleName
exec sp_trace_setevent @traceidout, 12,39, @on --SQL:BatchCompleted,TargetUserName
exec sp_trace_setevent @traceidout, 12,40, @on --SQL:BatchCompleted,DBUserName
exec sp_trace_setevent @traceidout, 12,41, @on --SQL:BatchCompleted,LoginSid
exec sp_trace_setevent @traceidout, 12,42, @on --SQL:BatchCompleted,TargetLoginName
exec sp_trace_setevent @traceidout, 12,43, @on --SQL:BatchCompleted,TargetLoginSid
exec sp_trace_setevent @traceidout, 12,44, @on --SQL:BatchCompleted,ColumnPermissions
exec sp_trace_setevent @traceidout, 12,45, @on --SQL:BatchCompleted,LinkedServerName
exec sp_trace_setevent @traceidout, 12,46, @on --SQL:BatchCompleted,ProviderName
exec sp_trace_setevent @traceidout, 12,47, @on --SQL:BatchCompleted,MethodName
exec sp_trace_setevent @traceidout, 12,48, @on --SQL:BatchCompleted,RowCounts
exec sp_trace_setevent @traceidout, 12,49, @on --SQL:BatchCompleted,RequestID
exec sp_trace_setevent @traceidout, 12,50, @on --SQL:BatchCompleted,XactSequence
exec sp_trace_setevent @traceidout, 12,51, @on --SQL:BatchCompleted,EventSequence
exec sp_trace_setevent @traceidout, 12,52, @on --SQL:BatchCompleted,BigintData1
exec sp_trace_setevent @traceidout, 12,53, @on --SQL:BatchCompleted,BigintData2
exec sp_trace_setevent @traceidout, 12,54, @on --SQL:BatchCompleted,GUID
exec sp_trace_setevent @traceidout, 12,55, @on --SQL:BatchCompleted,IntegerData2
exec sp_trace_setevent @traceidout, 12,56, @on --SQL:BatchCompleted,ObjectID2
exec sp_trace_setevent @traceidout, 12,57, @on --SQL:BatchCompleted,Type
exec sp_trace_setevent @traceidout, 12,58, @on --SQL:BatchCompleted,OwnerID
exec sp_trace_setevent @traceidout, 12,59, @on --SQL:BatchCompleted,ParentName
exec sp_trace_setevent @traceidout, 12,60, @on --SQL:BatchCompleted,IsSystem
exec sp_trace_setevent @traceidout, 12,61, @on --SQL:BatchCompleted,Offset
exec sp_trace_setevent @traceidout, 12,62, @on --SQL:BatchCompleted,SourceDatabaseID
exec sp_trace_setevent @traceidout, 12,63, @on --SQL:BatchCompleted,SqlHandle
exec sp_trace_setevent @traceidout, 12,64, @on --SQL:BatchCompleted,SessionLoginName
--turn on the trace
exec sp_trace_setstatus @traceidout, 1 ---start trace
--exec sp_trace_setstatus TRACEID, 0 ---stop trace, you must know the traceid to stop it
--exec sp_trace_setstatus TRACEID, 2 ---close trace you must know the traceid to delete it
COMMIT TRAN
END
Lowell
June 12, 2009 at 11:11 am
For recent commands that are likely still in cache, you can query cache directly through the dmv's
Running a query against sys.dm_exec_sql_text will let you search for delete statements or procedures.
Getting the parameters used (except what's visible in the ad hoc queries)... that's not really possible. But you'll be able to figure out who called the delete proc, when.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply