Wednesday, July 29, 2015

[Eventlog_Tracking] Send a report out when more than 100 messages in a minute

I'm trying to curb the excess eventlog messages in my environment.  Now that I'm using WQL/WMI/PoSH to capture it (writing the presentation now - tentative name is "WMI for the DB Guy/WQL for the DB Gal"), and filtering it as it comes in, I need a way to *AHEM* encourage people to suggest filters, and get rid of error messages. 

So, they're about to get emails with details when it's over 100 in a minute.  With the most frequent errors listed.  It's got a @threshold parameter so you can turn it down if you don't have too many errors.

Monday, July 27, 2015

[SSIS 2012] SQL Sentry - using custom code to send detailed SSISDB failure emails (part 2)

Since, going back through my old post (, I found that it was hard to figure out where you add the condition to SQL Sentry event manager. I need to add this to SQLSentry's just-announced condition repository.

Step 1: grab code from below, and put it in a database of your choosing.

Step 2: index!  You probably need something like this index.  Mind the blocking during creation

CREATE NONCLUSTERED INDEX ncidx__internal_event_messages__event_message_id
ON [internal].[event_messages] ([package_name])
INCLUDE ([event_message_id])

Step 3: open SQL Sentry, go to the SSISDB computer in your list of servers, then expand.  Click on the main subgroup, which has the DB icon, the server name , the SQL Server Version, then "(EM/PA)" if you're running both.

Step 4: expand "Conditions".  View->Conditions, or it's already a tab.

Step 5: Under "Explicit", there's an "Add" button.  Click

Step 6: In "Actions Selector: General Conditions", on the right side of the window find "SQL Server Agent Job", then "SQL Server Agent Job: Failure", then select "Execute SQL".

Step 7: Under server, choose the same server (you could run the code on a different box, but the data we need is on that box).

Step 8: For T-SQL Command Text, enter (I use a different name than in the original post, since I use both variants, depending on the server)

exec dba_utils.dbo.ssis_job_failure_info_fromjobname_SqlSentry @job_name = '<%ObjectName%>'

Step 9: Click in some other server or window, so that the details are saved. 

Step 10: Test! 

Tuesday, July 7, 2015

[Presenting] 24 Hours of PASS 2015 - how I did

Had to share.  From my presentation, here were the results of the survey (if you stuck around until the very end).  It included the comments as well, which were quite complimentary.  I smirked/winced at the comment about chatting me up at PASS, since my presentation wasn't selected and so the odds of me going are pretty damn slim.

But, like I said, I'm very pleased with this.  I know I had 237 people at one point, so it probably peaked at 250-275.  126 responses is better than I'd hoped for.  And Obviously I need to work on my presentation skills some more!  : )