November 3, 2015 at 7:40 am
I found Jason Brimhalls' blog post about this but it uses the default trace to capture who made the change. We have quite a few servers where all the files rollover a few times a day so I was hoping someone has done some work with Extended Events in this area. Ideally, I'd like to capture the change event when it happens and have both a record posted to a table and an email notification to the DBA. If anyone can point me to a useful resource, it would be greatly appreciated. Just an FYI, I don't have a lot of experience with XE.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
November 3, 2015 at 9:18 am
This tool might be what you're looking for:
http://extendedtsqlcollector.codeplex.com/
It does the job and doesn't need in-depth knowledge of XE, other than defining the session and knowing which actions/fields are included.
There's a blog post here that describes how to use it:
-- Gianluca Sartori
November 3, 2015 at 9:38 am
Thanks but unfortunately I can't use this since it requires SSIS on the target. Our environment doesn't allow SSIS on the database servers. I can't even download it an play with it to see if it will do what I need 🙁
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
November 3, 2015 at 9:47 am
Then I guess that you'll have to code it yourself.
Are you comfortable with PowerShell?
I have some examples here: http://spaghettidba.com/2015/04/20/tracking-table-usage-and-identifying-unused-objects/
It's a totally different problem, but the powershell script does exactly that: hook to an extended events session, stream events and do something in response.
Your "something" could be writing to a table (which is in the script, just needs to be slightly modified) and firing an alert (sp_send_dbmail to the rescue!).
If you need further assistance, post the code here as you go and I will try to help.
-- Gianluca Sartori
November 3, 2015 at 11:32 am
Thanks again for the such a quick response. I was at lunch so it took a little while before I saw this. I am comfortable enough with Powershell to use it for most of my basic monitoring tasks and avoiding dynamic SQL (ugh!). we are slowly replacing our homegrown monitoring with Powershell.
I took a look at the Posh script. I think my biggest challenge now is figuring out how to capture the configuration change event. It's easy enough in the trace file. Just find "Configuration option" in the textdata and parse it.
Looking at your script, I think the $events is a continuous stream that is piped through a "filter". If that is the case, I would need to change the filter to what I need to capture from the stream but I still need to figure what that event name is or I am back to looking for "Configuration option".
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
November 4, 2015 at 4:16 am
You probably don't need to filter on the client side (powershell script). The filter on the session could be enough.
The event you have to capture is "errorlog_written" and filter for '%Configuration Option%'.
Example:
CREATE EVENT SESSION [test] ON SERVER
ADD EVENT sqlserver.errorlog_written(
WHERE ([sqlserver].[like_i_sql_unicode_string]([message],N'%Configuration option%'))
)
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=OFF,STARTUP_STATE=OFF)
GO
Hope this helps
Gianluca
-- Gianluca Sartori
November 4, 2015 at 6:45 am
Actually, that is what I finally whittled it down to but I used ADD EVENT sqlserver.error_reported. I ran a profiler trace at the same time the session was running and I found that there is an error number associated with this also, 15457. Having an error number turned on the proverbial light bulb so I created an Alert and am working on a stored procedure as a response to the alert.
Thank you so much for your help. XE can be very trying and I have learned a lot more about it going through this exercise.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
November 4, 2015 at 6:54 am
Great!
Thanks for the feedback
-- Gianluca Sartori
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply