February 9, 2016 at 1:40 pm
We are using SQL Server 2008R2 standard edition
I created event session TestEvent
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.transaction_id, sqlserver.database_id, sqlserver.username)
WHERE sqlserver.database_id = 6
)
When I read the data from XML I can't able to see when it was happened.
So I want to modify the event to include the time when the transaction happened. How to do that?
I stored in the xml format. It might keep growing so want to know how the data will be purged without grow
February 9, 2016 at 1:48 pm
There should be a time in the XML, it'll be an attribute of the event tag.
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
February 9, 2016 at 2:06 pm
Yes.
Also I see the tasktime under the actions.
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.transaction_id, sqlserver.database_id, sqlserver.username))
but I want ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.transaction_id, sqlserver.database_id, sqlserver.username, tasktime))
To aleter this event do we need to drop and add the event again or is it possible to alter?
February 10, 2016 at 4:56 am
You can use ALTER EVENT SESSION to add stuff in, but you will need to stop and restart the session.
"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
February 11, 2016 at 10:24 am
Thank you.
I am looking to create extended events on the whole db. It might increase the size of the files.
Where we can give the option if the event is older than 2 months delete the data?
February 11, 2016 at 11:22 am
I'm pretty sure you have to manage that outside of the extended events session. It doesn't manage data files. It just collects them.
"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
February 11, 2016 at 2:05 pm
So you have to put the limit of the file size and depends upon the date appended you manually delete them?
February 11, 2016 at 2:54 pm
Yes, there's not built-in file expiration. You can set the size of your files and limit the number of rollover files, but you can't have them automatically remove themselves. You can automate the removal yourself using a PowerShell script or something similar.
"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
February 11, 2016 at 3:24 pm
Thank you.
I am creating extended events like this. I want to capture like only drop, delete, truncate statements. How to add that predicate in addition to the db_id=5?
Create event session New_Audit on server
ADD EVENT sqlserver.sp_statement_completed(
ACTION(sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.database_id,sqlserver.sql_text,sqlserver.username,sqlserver.task_time)
WHERE sqlserver.database_id = 5),
ADD EVENT sqlserver.sql_statement_completed(
ACTION(sqlserver.client_hostname,sqlserver.client_app_name,sqlserver.database_id,sqlserver.sql_text,sqlserver.username,sqlserver.task_time)
WHERE sqlserver.database_id = 5 )
ADD TARGET package0.event_file(SET filename = N'C:\AuditTesting.xel',
max_file_size=(50),max_rollover_files=(5))
alter event session New_Audit on server
state=start
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply