SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Inspector V1.2 now available

Inspector1_2

 

It has been long overdue but finally V1.2 is now available on GitHub.

V1.2 brings a lot of changes, 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].

2018-10-01 23_00_32

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.

 

2018-10-02 03_18_20

 

#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) 

2018-10-03 20_52_11

 

 

#16 Added server settings and warnings for Default cost threshold or MAXDOP set to default values

2018-10-03 20_53_23

2018-10-03 20_55_16

 

#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:

2018-10-03 20_56_37

2018-10-01 23_26_01

 

#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 

2018-10-08 16_09_13
Non AG

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.

 

2018-10-08 20_06_54-SQLUndercover Inspector
AG’s including their Automated backup preference and Preferred Servers

 

#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.

2018-10-08 19_37_26-DC01 on CATACLYSM - Virtual Machine Connection

2018-10-08 19_40_31-SQLUndercover Inspector

 

 

 

 

#33 Added a view for the DriveSpace table to show aggregated data 

2018-10-08 15_23_42

[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.

2018-10-08 16_05_00

[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.

2018-10-08 19_38_39-DC01 on CATACLYSM - Virtual Machine Connection

 

#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.

SQL Undercover

David Fowler and Adrian Buckman, two database nerds who love nothing more than to spend their time, reading about, researching and sharing all things SQL Server. David is a DBA with over 15 years production experience of SQL Server, from version 6.5 through to 2016. He has worked in a number of different settings and is currently the technical lead at one of the largest software companies in the UK. After working in the motor trade for over 11 years Adrian decided to give it all up to persue a dream of working in I.T. Adrian has over 3 years of experience working with SQL server and loves all things SQL, Adrian currently works as a Database Administrator for one of the UK’s Largest Software Companies.

Comments

Leave a comment on the original post [sqlundercover.com, opens in a new window]

Loading comments...