When looking for an easy method to audit Index changes, one of the first technologies to try really should be Extended Events (xevents).
Easy Audit Index Changes
Tag: SQL Internals
Audit Index Changes
This article shows a method to audit for index changes. The weak link in a solution such as this really boils down to the requirement that the solution needs to be in place before the index change occurs.
Find Index Create Date
This article shows a quick script to help determine indexes that were created recently. This script will help you out of rough spot and help reduce the chance of rework.
Detail Job History – Back to Basics
With this script, I have the ability to quick show which step is failing, what the command is for that step, what kind of process is running on that step, any passwords (in the event of an SSIS password), and of course the failure frequency. This is golden information at the fingertips. There is no need to click through the GUI to gather this information. You can get it quickly and easily in one fell swoop.
Job History – Back to Basics
An important part of any DBAs job is to ensure database related jobs are running prim and proper. Sometimes that just doesn’t happen. When jobs are being overlooked, it is useful to be able to gather data related to consistency of job success or failure. This script will help you in your investigation efforts.
Public Role Permissions – Back to Basics
If you are being serious in your role, then the amount of times you grant permissions to the public role should either be a) never, b) when you want to have a data breach, or c) you are testing in a sandbox to improve your skills.
Implicit Conversion Insights with XE
Seldom does a DBA have the opportunity to get out in front of infrequent or random errors such as implicit conversions. More often than not, it is the privilege of the DBA to find out about the problem after the fact from a developer or, worse yet, an end-user.
Mass Backup All Sessions
Migrating Extended Event Sessions from one server to another should be a simple task. So simple, one would think there was no need to give it a second thought, right?
Event Log File Paths
How does one consistently find the correct path to the Extended Event Log file (XEL file)? This article shows a thorough method to capture that information for all sessions on the server.
Database Offline – Redux
A database can be offline at the time that you take over responsibility of the database. You will be asked when it was taken offline. This script will help you get a reasonable deduction for that offline event in the absence of better data.