If you've been noticing that you're not getting deadlock email alerts from Spotlight on SQL Server Enterprise even though alarms are popping up in Spotlight itself, then this workaround might be for you.
Before you move on though, I am assuming that you’ve an Alarm Action setup properly to send email alerts out and also you’ve configured the email server option on Spotlight to point to your mail server. For details on this, I will defer you to the Spotlight 'Help | Contents' or check out our KB links below:
Configure mail server - https://support.quest.com/spotlight-on-sql-server-enterprise/kb/88297
Configure Alarm Actions - https://support.quest.com/spotlight-on-sql-server-enterprise/kb/122528
So what maybe going on here. Well, Spotlight email alerts for deadlock includes an xdl attachment, which (you guessed it!) is zipped up. This presents a problem with most organizations that have restrictions zip attachments.
Here is a workaround to change the zip extension that may help you get around the issue:
- On the Diagnostic Server host, go to C:\Program Files\Dell\Diagnostic Server\Agent\conf\Rule\SQLServer_SpotLight-Deadlocks.xml and make a copy for safe keeping else where
- Open SQLServer_SpotLight-Deadlocks.xml with notepad but make sure word warp is turned off
Note: Windows Server may not allow you to edit and save the file directly in the install path so you may need to copy it on your desktop, edit/save it, and then copy it back with admin permission.
- Search (CTRL+F) for the word "eMailAttachmentName"
Sample block given below:
…<component additionalTextExpression="" alwaysEnabled="false class="com.quest.adk.configuration.component.RuleContentComponent" contextHelpKeyword="sosse:btnLocks.Deadlocks per second.alarm" defaultExecuteMode="ON_SEVERITY_MATCH" drilldownLocation="SoSSE.SQLActivityTabs\SoSSE.SQLActivity_Deadlock" eMailAttachmentExpression="DeadlockList.deadlock_graph" eMailAttachmentMIMEType="application/zip,Deadlock{0}.xdl" eMailAttachmentName="Deadlocks.zip" eventRule="true" internal="false" keyed="false" ruleType="THRESHOLD" snmpOID="15">…
- Change from eMailAttachmentName="Deadlocks.zip" to eMailAttachmentName="Deadlocks.<some_extension>" where <some_extension> is anything you want
Note: "Zaap" is what I usually like to change the zip extension too but that's just me.
- Restart the Diagnostic Server or else it won't know about the change
Once the email is received, the end user can save the attachment to their desktop, change the extension back to zip and extract the xdl file. I hope this has been helpful! Want to give Spotlight on SQL Server a try? Download a free trial. Until the next post, take care...
