It has been long overdue but finally V1.2 is now available on GitHub.
V1.2 brings a lot of changes, We put together a sample report for you to play with but here is an overview of what has been added.
Enhancements:
#9 Long running transactions module added – A new setting has been added to [Inspector].[Settings].
This setting is the threshold in Seconds, if a transaction exceeds this duration at the time of collection the Report will show a Yellow advisory condition and show some details of the transaction.
#12 Added Instance start time and up time in days
#20 Added instance version and edition
#35 Added Physical server name (Useful for traditional failover clustered instances)
#16 Added server settings and warnings for Default cost threshold or MAXDOP set to default values
#17 Changed Backup comparison to Log_Date not Getdate() –
When the comparison for the backups check takes place when executing the SQLundercoverInspectorReport procedure , comparisons against last backup date for FULL, DIFF and LOG were done against Getdate() , this was ok if the report was ran within a short period following the collection however if this period was extended then it is possible to exceed thresholds and therefore receive invalid warnings.
MAX(Log_Date) is now determined per database per AG or Server if non ag, then MAX(Log_Date) is compared against the last backup DATETIME and then finally evaluated against the threshold values.
#18 Added suspect pages check – We added a new module to check for suspect pages, if any pages are found a Red warning is shown at the top of the report and details contained in a table like below:
#19 Added an exclusion to the backups check to exclude Diff backup threshold checking – You can now exclude Diff backups from backup checks by setting the threshold value in [Inspector].[Settings] to NULL.
#55 Include AG backup preference in the backup check and show only preferred servers for backups
AG Backup Pref will show the Automated backup preference at the time of collection for the given AG (N/A for non AG) and the preferred servers column will show only the relevant server/s which are able to take transaction log backups.
#21 Added AG Databases check to warn on databases not joined to an AG
If you are using Availability groups and you have this new module enabled the Inspector will assume that all databases should be joined to an AG, every database name for the instance is inserted into a new table called [Inspector].[AGDatabases] and the Is_AG flag is set to a 1 , if databases are joined to an AG then the Is_AGJoined column is set to a 1 therefore no Advisory will be shown on the report. If a database is marked as Is_AG then it will continue to warn if not joined to an AG on the Inspector reports, if you wish to exclude a given database from the advisory condition simply update Is_AG to a 0 .
Instances that are not Hadr enabled with at least one AG will automatically have Is_AG set to 0 and will be excluded from the checking even if the module is enabled.
#33 Added a view for the DriveSpace table to show aggregated data
[Inspector].[DriveSpaceInfo] – Average growth details for Daily, Monthly and Yearly and also the Minimum growth there has been for a single day over the period and the Maximum growth for a single day within the period.
#51 Add a view for the DatabaseGrowthHistory table to show aggregated data – Information at your fingertips , now you can get aggregated information on growths for reporting or projections.
[Inspector].[DatabaseGrowthInfo] – Aggregated database file growth information for recorded growths within the Inspector, shows actual size growth and some Average based predictions for Daily, Monthly and Yearly growth sizes.
#34 Moved RAISERROR commands in Collection stored proc – We just put them in the correct order , before the action rather than after.
#36 Always insert the HTML report into ReportData table – The html report data is now stored automatically in [Inspector].[ReportData] when you run the Inspector report, a cleanup threshold in days has also been added to [Inspector].[Settings] (ReportDataRetention) this defaults to 30 days. There is now a new column in the ReportData table too called Summary which shows the total Red and Yellow warning totals for each report.
#37 Improve setup script to validate @InitialSetup parameter value – We wanted to make it as easy as possible to setup so we have checks in place now to ensure that if its the initial setup but @InitialSetup is set to zero then @Initial setup will be switched to one during execution and you will be advised in the messages tab.
#47 Add supporting Procs/Views for Powershell collection – The powershell script will be available soon, we have been working hard getting all the prerequisites in place to eliminate the need for linked servers when logging data for multiple servers.
#50 Show disabled modules as N/A for PeriodicBackupsCheck Moduleconfig – We didn’t want to see a whacking great list of disabled modules for this ModuleConfig as this one is dedicated to the backupscheck module only – so we cleaned it up in the report.
#53 Convert setup script to a stored procedure – This allows you to run the installation script on the reporting database which will create the InspectorSetup stored procedure only, you can then choose to upgrade when you like by Executing the stored procedure. You can find a guide here for the new installer Proc.
#54 Add new table [Inspector].[InspectorUpgradeHistory] – We thought it would be a good idea to track Installs/Upgrades so we added a new table to track this info , we also include the parameter values used when calling the procedure.
Bugs:
#48 Next Growth column not updated when growth rates changed , this bug has been addressed and the next growth column now updates when growth rates are changes between collections.
#56 Divide by zero error when executing DatabaseGrowthsInsert – If auto growth is disabled or a percentage based growth is active on very small databases where the percentage equates to less than 1MB this was throwing a divide by zero error.
