April 16, 2009 at 4:38 pm
Hello,
This has been a busy DBA week for me. Whew!! Come on Friday. Well, I can't say that anymore with this job, it is seven days a week.
Anyway, some records were deleted last night about 10:00 p.m. This morning, our regularly scheduled transaction log backup occurred.
At about 2:00 p.m., management notices records are there and wants to know how the records were deleted.
One DTS package failed at that time. I checked the DTS package and there is no reference to the table that was deleted.
I have no idea how to script something that shows me the jobs that ran and at what time.
I have no idea who or what deleted the records. However, sure enough they are gone. Once again, I am asking the DBAs wiser than me for help. Is there a way to see who or what deleted records in a table at this point. I don't think profiler was running.
I've disabled the DTS package that failed at the same time for now.
Thanks,
Tony
Things will work out. Get back up, change some parameters and recode.
April 16, 2009 at 4:45 pm
Execute this command and tell us the results:
SELECT *
FROM fn_trace_getinfo(default);
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 16, 2009 at 5:08 pm
Hello,
I'm getting:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'fn_trace_getinfo'.
Tony
Things will work out. Get back up, change some parameters and recode.
April 16, 2009 at 5:49 pm
Hello,
I found this query and ran it. It returned 0 rows.
SELECT * FROM ::fn_trace_getinfo(default)
Thanks.
Tony
Things will work out. Get back up, change some parameters and recode.
April 16, 2009 at 7:52 pm
... ?
You are on SQL Server 2005 aren't you?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 16, 2009 at 8:27 pm
Yes, Sir. SQL Server 2005.
Tony
Things will work out. Get back up, change some parameters and recode.
April 16, 2009 at 9:08 pm
Hmm, well that's too bad. Looks like your default trace is turned off. That would have helped a lot...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 17, 2009 at 1:42 am
What does this return?
exec sp_configure 'default trace enabled'
I don't think that the default trace keeps record of deletes. It doesn't have all that many events in it.
If you didn't have some trace or auditing in place, there's not way to go back and see what happened.
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
April 17, 2009 at 4:07 am
Gail,
On the messages tab, I got:
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 79
The configuration option 'default trace enabled' does not exist, or it may be an advanced option.
Valid configuration options are:
On the results tab, I got 37 rows of general configuration stuff:
recovery interval (min) 0 32767
allow updates 0 1
user connections 0 32767
locks 5000 2147483647
open objects 0 2147483647
fill factor (%) 0 100
media retention 0 365
nested triggers 0 1
remote access 0 1
two digit year cutoff 1753 9999
Thanks for the help. I just wanted to make sure there wasn't something that I could or should be do to find out what deleted the rows.
Thanks again everyone.
Things will work out. Get back up, change some parameters and recode.
April 17, 2009 at 4:15 am
WebTechie38 (4/17/2009)
Gail,On the messages tab, I got:
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 79
The configuration option 'default trace enabled' does not exist, or it may be an advanced option.
Valid configuration options are:
On the results tab, I got 37 rows of general configuration stuff:
recovery interval (min) 0 32767
allow updates 0 1
user connections 0 32767
locks 5000 2147483647
open objects 0 2147483647
fill factor (%) 0 100
media retention 0 365
nested triggers 0 1
remote access 0 1
two digit year cutoff 1753 9999
Thanks for the help. I just wanted to make sure there wasn't something that I could or should be do to find out what deleted the rows.
Thanks again everyone.
use the following code:
exec sp_configure 'show advanced options', 1
go
RECONFIGURE
go
exec sp_configure
April 17, 2009 at 6:43 am
There's a chance, just a chance, that the queries could still be in the cache. Try querying sys.dm_exec_sql_text to see if you can find the query. From there you can might be able to find out who ran it looking at sys.dm_exec_requests.
But if it's already aged out of cache, this won't help.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 17, 2009 at 11:18 am
In his situation what is a good way to address this? I'm inclined to suggest that he restores from a backup containing the missing data. Once that step is done, implement a short term trigger to capture delete statements.
Is there any out-of-the-box tool that can do this in simpler steps?
April 17, 2009 at 11:23 am
Not a trigger for monitoring, use a server-side trace.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
April 17, 2009 at 11:26 am
Grant,
Good point.
I tried your query suggestion.
What's a good way to tie the cached query with the user? I can see the time it was executed and other statistics information related to it. Is there any dm function that can do this?
SELECT total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text, *
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;
April 17, 2009 at 11:43 am
You can try joing to dm_exec_plan_attributes and see if the user_id is available for the plans you're interested in. It might not be.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply