Db Snapshot Tracking

[aachaemenes]

Can we have DBADASH Track Snapshot creation? They can be a drag on storage and performance.

[DavidWiseman]

Hi,

The data is already captured by DBA Dash. It's possible to create a custom report for database snapshots. The script below will create a summary report showing a count of snapshots by instance and a detail report showing the snapshots created on each instance. The summary report has drill downs to the detail report. Just run this in the repository DB and restart the GUI (Or select Filter/Clear All to refresh).

/*
	Database Snapshot Detail
	

	Custom report for DBA Dash.
	http://dbadash.com
	Generated: 2024-09-19 20:58:07 
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER   PROC [UserReport].[DatabaseSnapshotDetail](
	@InstanceIDs IDs READONLY,
	@InstanceID INT
)
AS
SELECT I.InstanceDisplayName AS Instance,
		D.name,
		SDB.name as source_database,
		D.create_date,
		STUFF((SELECT DISTINCT ',' + LEFT(F.physical_name,3) 
		FROM dbo.DBFiles F
		WHERE F.DatabaseID = D.DatabaseID 
		FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,1,'') CreatedOnVolumes
FROM dbo.Instances I 
JOIN dbo.Databases D ON D.InstanceID = I.InstanceID
LEFT JOIN dbo.Databases SDB ON D.source_database_id = SDB.database_id AND SDB.InstanceID = D.InstanceID AND SDB.IsActive=1
WHERE D.source_database_id IS NOT NULL
AND I.IsActive = 1
AND D.IsActive=1
AND EXISTS(SELECT 1 
			FROM @InstanceIDs I 
			WHERE I.ID = D.InstanceID
			)
AND (I.InstanceID = @InstanceID OR @InstanceID IS NULL)
OPTION(RECOMPILE)
GO

/* Report customizations in GUI */
DELETE dbo.CustomReport
WHERE SchemaName = 'UserReport'
AND ProcedureName = 'DatabaseSnapshotDetail'

INSERT INTO dbo.CustomReport(SchemaName,ProcedureName,MetaData)
VALUES('UserReport','DatabaseSnapshotDetail','{
  "ReportName": "Database Snapshot Detail",
  "TriggerCollectionTypes": [
    "Databases"
  ],
  "CustomReportResults": {
    "0": {
      "ColumnAlias": {
        "name": "Database",
        "source_database": "Source Database",
        "create_date": "Created Date",
        "CreatedOnVolumes": "Created On Volume(s)"
      },
      "CellFormatString": {},
      "DoNotConvertToLocalTimeZone": [],
      "ColumnLayout": [],
      "ResultName": "Result0",
      "LinkColumns": {},
      "CellHighlightingRules": {}
    }
  }
}')
GO
/*
	Database Snapshot Summary
	

	Custom report for DBA Dash.
	http://dbadash.com
	Generated: 2024-09-19 20:58:20 
*/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE OR ALTER     PROC [UserReport].[DatabaseSnapshotSummary](
	@InstanceIDs IDs READONLY
)
AS
SELECT I.InstanceID,
		I.InstanceDisplayName AS Instance,
		COUNT(*) AS DatabaseSnapshots,
		MAX(D.create_date) MostRecent,
		MIN(D.create_date) Oldest
FROM dbo.Instances I 
JOIN dbo.Databases D ON D.InstanceID = I.InstanceID
WHERE D.source_database_id IS NOT NULL
AND I.IsActive = 1
AND D.IsActive=1
AND EXISTS(SELECT 1 
			FROM @InstanceIDs I 
			WHERE I.ID = D.InstanceID
			)
GROUP BY I.InstanceDisplayName ,
		I.InstanceID

GO

/* Report customizations in GUI */
DELETE dbo.CustomReport
WHERE SchemaName = 'UserReport'
AND ProcedureName = 'DatabaseSnapshotSummary'

INSERT INTO dbo.CustomReport(SchemaName,ProcedureName,MetaData)
VALUES('UserReport','DatabaseSnapshotSummary','{
  "ReportName": "Database Snapshot Summary",
  "TriggerCollectionTypes": [
    "Databases"
  ],
  "CustomReportResults": {
    "0": {
      "ColumnAlias": {
        "DatabaseSnapshots": "Database Snapshots",
        "MostRecent": "Most Recent"
      },
      "CellFormatString": {},
      "DoNotConvertToLocalTimeZone": [],
      "ColumnLayout": [
        {
          "Key": "InstanceID",
          "Value": {
            "Width": 107,
            "DisplayIndex": -1
          }
        },
        {
          "Key": "Instance",
          "Value": {
            "Width": 305,
            "Visible": true,
            "DisplayIndex": 1
          }
        },
        {
          "Key": "DatabaseSnapshots",
          "Value": {
            "Width": 168,
            "Visible": true,
            "DisplayIndex": 2
          }
        },
        {
          "Key": "MostRecent",
          "Value": {
            "Width": 143,
            "Visible": true,
            "DisplayIndex": 3
          }
        },
        {
          "Key": "Oldest",
          "Value": {
            "Width": 155,
            "Visible": true,
            "DisplayIndex": 4
          }
        }
      ],
      "ResultName": "Result0",
      "LinkColumns": {
        "InstanceID": {
          "$type": "DrillDownLinkColumnInfo",
          "ReportProcedureName": "DatabaseSnapshotDetail",
          "ColumnToParameterMap": {
            "@InstanceID": "InstanceID"
          }
        },
        "Instance": {
          "$type": "DrillDownLinkColumnInfo",
          "ReportProcedureName": "DatabaseSnapshotDetail",
          "ColumnToParameterMap": {
            "@InstanceID": "InstanceID"
          }
        },
        "DatabaseSnapshots": {
          "$type": "DrillDownLinkColumnInfo",
          "ReportProcedureName": "DatabaseSnapshotDetail",
          "ColumnToParameterMap": {
            "@InstanceID": "InstanceID"
          }
        }
      },
      "CellHighlightingRules": {}
    }
  }
}')

Hope this helps,

David