Database Properties Module : MinionWare

For each database, the Database Properties module collects a significant amount of information on databases, including:

the database name and database id
the database owner
date of the last full, log, or diff backup
which databases have auto-shrink enabled
database collations
case sensitivity
database size on disk
and, more

This allows you to track databases as they come and go, grow, shrink, and change names, for the entire enterprise. To see the most recent collection of database properties, query Collector.DBPropertiesCurrent.

Tables

Collector.DBProperties

Stores the collections of database property data.

Definitions for the majority of the Collector.DBProperties columns can be found in the TechNet article on SqlServer.Management.Smo Database Properties: https://technet.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database_properties(v=sql.110).aspx

Column	Type	Description
ID	bigint	Primary key row identifier.
ExecutionDateTime	datetime	The execution date and time, common to the run of a collection for a single instance.
InstanceID	bigint	The instance ID of the instance in question, as defined in the table dbo.Servers.
DBid	bigint	The database ID (as defined in sys.databases).
DBName	varchar	Database name.
DBOwner	Varchar	Login of the database owner.
LastBackup	datetime2	Date of the last full backup.
LastLogBackup	datetime2	Date of the last log backup.
LastDiffBackup	datetime2	Date of the last differential backup.
LastCheckDB	datetime2	The date that CheckDB was last performed on this database.
SizeInKB	decimal	See the Database Properties TechNet article (Size property).
DataSpaceUsageInKB	Decimal	See the Database Properties TechNet article (DataSpaceUsage property).
IndexSpaceUsageInKB	Decimal	See the Database Properties TechNet article (IndexSpaceUsage property).
SpaceAvailableInKB	decimal	See the Database Properties TechNet article (SpaceAvailable property).
AutoClose	bit	See the Database Properties TechNet article.
AutoShrink	bit	See the Database Properties TechNet article.
DBReadOnly	bit	See the Database Properties TechNet article.
Collation	varchar	See the Database Properties TechNet article.
CompatLevel	varchar	See the Database Properties TechNet article.
DefaultSchema	Varchar	See the Database Properties TechNet article.
RecoveryModel	Varchar	See the Database Properties TechNet article.
CreateDate	datetime	See the Database Properties TechNet article.
CaseSensitive	bit	See the Database Properties TechNet article.
Status	varchar	See the Database Properties TechNet article.
ANSINullDefault	bit	See the Database Properties TechNet article.
ANSINullsEnabled	bit	See the Database Properties TechNet article.
ANSIPaddingEnabled	bit	See the Database Properties TechNet article.
ANSIWarningsEnabled	bit	See the Database Properties TechNet article.
ArithAbortEnabled	bit	See the Database Properties TechNet article.
AutoCreateIncrementalStatisticsEnabled	bit	See the Database Properties TechNet article.
AutoCreateStatisticsEnabled	bit	See the Database Properties TechNet article.
AutoUpdateStatisticsAsync	bit	See the Database Properties TechNet article.
AutoUpdateStatisticsEnabled	bit	See the Database Properties TechNet article.
ReplicaID	uniqueidentifier	The ID of the local Always On Availability Group replica that the database is in.
GroupDatabaseID	uniqueidentifier	Database ID within an Always On availability group.
ResourcePoolID	int	The resource pool ID that is mapped to this database.
AvailabilityDatabaseSynchronizationState	varchar	See the Database Properties TechNet article.
AvailabilityGroupName	varchar	See the Database Properties TechNet article.
BrokerEnabled	bit	See the Database Properties TechNet article.
ChangeTrackingAutoCleanUp	bit	See the Database Properties TechNet article.
ChangeTrackingEnabled	bit	See the Database Properties TechNet article.
ChangeTrackingRetentionPeriod	int	See the Database Properties TechNet article.
ChangeTrackingRetentionPeriodUnits	varchar	See the Database Properties TechNet article.
CloseCursorsOnCommitEnabled	bit	See the Database Properties TechNet article.
ConcatenateNullYieldsNull	bit	See the Database Properties TechNet article.
Containment	tinyint	See the Database Properties TechNet article. 0 indicates database containment is off.
ContainmentDesc	varchar	Description of database containment. Example values: NONE, PARTIAL
DatabaseGuid	uniqueidentifier	See the Database Properties TechNet article.
DatabaseOwnershipChaining	bit	See the Database Properties TechNet article.
DatabaseSnapshotBaseName	varchar	See the Database Properties TechNet article.
DateCorrelationOptimization	bit	See the Database Properties TechNet article.
DefaultFileGroup	varchar	See the Database Properties TechNet article.
DefaultFileStreamFileGroup	varchar	See the Database Properties TechNet article.
DefaultFullTextCatalog	varchar	See the Database Properties TechNet article.
DefaultFullTextLanguage	varchar	See the Database Properties TechNet article.
DefaultLanguage	varchar	See the Database Properties TechNet article.
DelayedDurability	varchar	See the Database Properties TechNet article.
EncryptionEnabled	bit	See the Database Properties TechNet article.
FilestreamDirectoryName	varchar	See the Database Properties TechNet article.
FilestreamNonTransactedAccess	varchar	See the Database Properties TechNet article.
HasFileInCloud	bit	See the Database Properties TechNet article.
HasMemoryOptimizedObjects	bit	See the Database Properties TechNet article.
HonorBrokerPriority	bit	See the Database Properties TechNet article.
IsAccessible	bit	See the Database Properties TechNet article.
IsDatabaseSnapshot	bit	See the Database Properties TechNet article.
IsDatabaseSnapshotBase	bit	See the Database Properties TechNet article.
IsFederationMember	bit	See the Database Properties TechNet article.
IsFullTextEnabled	bit	See the Database Properties TechNet article.
IsMailHost	bit	See the Database Properties TechNet article.
IsManagementDataWarehouse	bit	See the Database Properties TechNet article.
IsParameterizationForced	bit	See the Database Properties TechNet article.
IsReadCommittedSnapshotOn	bit	See the Database Properties TechNet article.
IsSystemObject	bit	See the Database Properties TechNet article.
IsUpdateable	bit	See the Database Properties TechNet article.
IsVarDecimalStorageFormat Enabled	bit	See the Database Properties TechNet article.
LocalCursorsDefault	bit	See the Database Properties TechNet article.
LogReuseWaitStatus	varchar	See the Database Properties TechNet article.
MemoryAllocatedToMemory OptimizedObjectsInKB	bigint	See the Database Properties TechNet article.
MemoryUsedByMemory OptimizedObjectsInKB	bigint	See the Database Properties TechNet article.
IsMirroringEnabled	bit	See the Database Properties TechNet article.
MirroringFailoverLogSequence Number	decimal	See the Database Properties TechNet article.
MirroringID	uniqueidentifier	See the Database Properties TechNet article.
MirroringRole	tinyint	The database’s current role (if any) in the database mirroring session.
MirroringRoleDesc	varchar	Description of the database mirroring role. Example values: Principal, Mirror, NULL.
MirroringPartner	sysname	See the Database Properties TechNet article.
MirroringPartnerInstance	sysname	See the Database Properties TechNet article.
MirroringRedoQueueMaxSize	bigint	See the Database Properties TechNet article.
MirroringRoleSequence	int	See the Database Properties TechNet article.
MirroringSafetyLevel	varchar	Mirroring safety setting for updates on the mirror database.
MirroringSavetyLevelDesc	varchar	Description of the mirroring safety level. Example values: UNKNOWN, OFF, FULL, NULL
MirroringSafetySequence	int	See the Database Properties TechNet article.
MirroringStatus	varchar	State of the mirror database and mirroring session.
MirroringStatusDesc	varchar	Description of the mirroring status. Example values: DISCONNECTED, SYNCHRONIZED, SYNCHRONIZING, PENDING_FAILOVER, SUSPENDED, UNSYNCHRONIZED, SYNCHRONIZED, NULL
MirroringTimeout	int	See the Database Properties TechNet article.
MirroringWitness	sysname	See the Database Properties TechNet article.
MirroringWitnessStatus	varchar	State of the witness in the database mirroring session.
MirroringWitnessStatusDesc	varchar	Description of mirroring witness status. Example values: UNKNOWN, CONNECTED, DISCONNECTED, NULL
NestedTriggersEnabled	bit	See the Database Properties TechNet article.
NumericRoundAbortEnabled	bit	See the Database Properties TechNet article.
PageVerify	varchar	Setting of the page_verify option.
PageVerifyDesc	varchar	Description of page verify. Example values: NONE, TORN_PAGE_DETECTION, CHECKSUM.
PrimaryFilePath	varchar	See the Database Properties TechNet article.
QuotedIdentifiersEnabled	bit	See the Database Properties TechNet article.
RecoveryForkGuid	uniqueidentifier	See the Database Properties TechNet article.
RecursiveTriggersEnabled	bit	See the Database Properties TechNet article.
ServiceBrokerGuid	uniqueidentifier	See the Database Properties TechNet article.
SnapshotIsolationState	varchar	See the Database Properties TechNet article.
State	tinyint	Database state.
StateDesc	nvarchar	Description of database state. Example values: ONLINE, RESTORING, RECOVERING, RECOVERY_PENDING, SUSPECT, EMERGENCY, OFFLINE, COPYING, OFFLINE_SECONDARY.
TargetRecoveryTime	int	See the Database Properties TechNet article.
TransformNoiseWords	bit	See the Database Properties TechNet article.
Trustworthy	bit	See the Database Properties TechNet article.
TwoDigitYearCutoff	smallint	See the Database Properties TechNet article.
ReplicationOptions	int	Replication options. Defined as a summation of any of these three options: 1 = published 2 = subscribed 4 = merge published
IsQueryStoreOn	bit	Denotes whether query store is enabled for this database.
IsRemoteDataArchiveEnabled	bit	Denotes whether the database is a stretch database.
IsMixedPageAllocationOn	bit	Denotes whether tables and indexes in the database can allocate initial pages from mixed extents.

dbo.DatabaseList

Stores the “master list” of databases. This table is populated, updated, and used by the “New/Retired Database Alert”.

Column	Type	Description
ID	bigint	Primary key row identifier.
InstanceID	bigint	The instance ID of the instance in question, as defined in the table dbo.Servers.
DBName	varchar	Database name.
IsSnapshot	bit	Whether the DB is a snapshot of another DB.
DatabaseSnapshotBaseName	nvarchar	The DB the snapshot is based on.

Views

Collector.DBPropertiesCurrent

Provides the most recent collection of database property data.

Each “Current” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:

ServerName
ServiceLevel
Version
Edition
Descr

Collector.DBPropertiesPrevious

Provides the next-to-most recent collection of database property data.

Each “Previous” view associated with a Collector table contains all (or nearly all) of the columns from the base table, plus a “ViewDesc” description column, and columns from dbo.Servers data:

ServerName
ServiceLevel
Version
Edition
Descr

Stored procedures

Alert.DbsNewRetired

This procedure alerts and reports on databases that have appeared or disappeared (as compred to the previous collection). Alert.DbsNewRetired updates the dbo.DatabaseList table with new and

By default, this alert is scheduled for Gold and Silver servers.

Column	Type	Description
@EmailProfile	varchar	The name of the database mail profile, to be used for emailing the report.
@Servicelevel	varchar	A simple label for the level of the server’s importance, which determines the level of service that Minion Enterprise provides. Valid values: Gold, Silver, Bronze.

Report.DBNamesLatestGet

Returns a list of all database names from the current collection, for a given InstanceID.

This may be used as needed, and as a way to populate database drop-down menus in SSRS reports.

Column	Type	Description
@InstanceID	int	The instance ID of the instance in question, as defined in the table dbo.Servers.

Example execution:

DECLARE @ID INT;
 
SELECT  @ID = InstanceID
FROM    dbo.Servers
WHERE   ServerName = 'Server1';
 
EXEC Minion.Report.DBNamesLatestGet @InstanceID = @ID;

Jobs

CollectorDBPropertiesGet-GOLD

Calls the executable DBPropertiesGET.exe for all servers with ServiceLevel = ‘Gold’, to retrieve database property information.

CollectorDBPropertiesGet-SILVER

Calls the executable DBPropertiesGET.exe for all servers with ServiceLevel = ‘Silver’, to retrieve database property information.

CollectorDBPropertiesGet-BRONZE

Calls the executable DBPropertiesGET.exe for all servers with ServiceLevel = ‘Bronze’, to retrieve database property information.

AlertDBsNewRetired-GOLD

Calls the stored procedure Alert.DbsNewRetired to alert on new and retired databases (as compared to the previous collection), for all servers with ServiceLevel = ‘Gold’.

AlertDBsNewRetired-SILVER

Calls the stored procedure Alert.DbsNewRetired to alert on new and retired databases (as compared to the previous collection), for all servers with ServiceLevel = ‘Silver’.

Executables

DBPropertiesGET.exe

Perform the database property data collection for the servers in the specified service level. Log the results to Collector.DBProperties via bulk insert.

Input parameters:

$Query – This parameter refers to the service level that the collector should operate on. Examples: Gold, Silver, Bronze. For more information on this topic, see the article “Executables and Service Levels”.

Example execution:

C:\MinionByMidnightDBA\Collector\DBPropertiesGET.exe Gold

New/Retired Databases Alert

The Database Properties module provides for a unique bit of functionality: an alert on new databases, and on retired (deleted) databases.

The stored procedure (Alert.DBsNewRetired) that comprises the alert maintains a master database list in dbo.DatabaseList. This table does nothing but hold the InstanceID and DBName. The alert can then compare the last run of the Databases collection (in Collector.DBProperties) with the dbo.DatabaseList table. It then sends a report based on its findings.

IMPORTANT: The first run of this alert may be quite large, because there are no rows in the DatabaseList table. So this alert should insert all of those rows for you. From then on, it will maintain the list and any alerts you receive will be much more reasonable.

There is nothing for you to do when you get this alert. It is merely informational so you’ll know when databases come and go from your systems.

https://minionware.desk.com/customer/portal/articles/2378828-database-properties-module
http://www.MinionWare.net

Database Properties Module Print

Tables

Collector.DBProperties

dbo.DatabaseList

Views

Collector.DBPropertiesCurrent

Collector.DBPropertiesPrevious

Stored procedures

Alert.DbsNewRetired

Report.DBNamesLatestGet

Jobs

CollectorDBPropertiesGet-GOLD

CollectorDBPropertiesGet-SILVER

CollectorDBPropertiesGet-BRONZE

AlertDBsNewRetired-GOLD

AlertDBsNewRetired-SILVER

Executables

DBPropertiesGET.exe

New/Retired Databases Alert

Related Articles