January 12, 2018 at 1:13 pm
We have a SQL 2008 R2 standard edition with a database installed. Data from some of the tables vanishes without trace.
Because of the edition issue, auditing won't work. I'd like to flag when delete statements are run against about 10 tables.
I want to know when the delete's happen, what table and what user. I'm a total rookie using extended events but after reading a few blogs
I believe this will work. here is an example I found for sql 2014/2016
CREATE EVENT SESSION [SQLDeleteAudit] ON SERVER
ADD EVENT sqlserver.sp_statement_completed(
ACTION(sqlos.task_time,sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,
sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.server_principal_sid,
sqlserver.session_nt_username,sqlserver.sql_text,sqlserver.username)
WHERE (([sqlserver].[equal_i_sql_unicode_string]([object_name],N'table_a') OR [sqlserver].[equal_i_sql_unicode_string]([object_name],N'table_b') OR
[sqlserver].[equal_i_sql_unicode_string]([object_name],N'table_c')) AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%delete%')))
ADD TARGET package0.event_file(SET filename=N'\\APSRVR3\Backups\APSRVR1.SQLAudit\APSRVR1.SQLDeleteAudit.xel',max_rollover_files=(25))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=ON,STARTUP_STATE=ON)
GO
Can anyone help?
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
January 15, 2018 at 7:43 am
I am a HUGE advocate for Extended Events.
I state that up front because, since you're in 2008, you'll be better off using Trace Events. It doesn't have the kind of filtering you're applying in your ExEvents (nice choice by the way). However, you'll get data that you can easily consume from Trace Events whereas the stuff in 2008 is pure XML and you'll have to parse it yourself. It's a pain that I don't wish on anyone.
"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
January 16, 2018 at 10:53 am
Can you recommend any reference material to help me get up to speed with this? Are these specific trace events that can be referenced when table rows are deleted?
Thanks.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
January 16, 2018 at 11:00 am
fizzleme - Tuesday, January 16, 2018 10:53 AMCan you recommend any reference material to help me get up to speed with this? Are these specific trace events that can be referenced when table rows are deleted?Thanks.
No. You're going to be looking for the same thing you would using extended events. You'll have to capture any rpc_completed or sql_batch_completed events. You can try filtering for the DELETE keyword and the tables you're interested in, but filtering in trace is pretty horrible. You'd be better off capturing the events and then filtering the results after the fact. For example, set it up so that the trace outputs to a file. Have that file roll over every fifteen minutes. You then schedule something that reads the old file every fifteen minutes, looks for this particular sets of strings, then delete that file. It's not as good as extended events, but it will work.
"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
January 16, 2018 at 11:10 am
Ok. Thanks for the advice. This sounds like a pain. However we need to determine why rows of data are disappearing from the database. It's either a batch or user activity or perhaps a user starting a batch. Happy Tuesday!
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
January 16, 2018 at 11:47 am
fizzleme - Tuesday, January 16, 2018 11:10 AMOk. Thanks for the advice. This sounds like a pain. However we need to determine why rows of data are disappearing from the database. It's either a batch or user activity or perhaps a user starting a batch. Happy Tuesday!
Why punish the whole server to capture something about just a couple of tables? Create an additional DELETE trigger on the table and a new table to capture the output like ORIGINAL_LOGIN() etc, etc.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 16, 2018 at 12:00 pm
I believe your idea has merit.
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
January 17, 2018 at 10:36 am
I ran a quick test. Using the triggers is a great way to capture the information I needed. thank you!
When the snows fall and the white winds blow,The lone wolf dies but the pack survives.
Once you've accepted your flaws, no one can use them against you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply