I want to write a query/view against this table with calculated columns:
- [Size Difference from Prior Snapshot - Bytes]
- [Size Difference from Prior Snapshot - Percent]
The Prior Snapshot is the prior record when the list is ordered by LogDateTime (you could order by LogId, LogDateTime or FileName, and get the same result)
This is the table (in SQL Server 2017 / 14.0.2037.2):
| LogId | LogDateTime | FileName | Size | |
|---|---|---|---|---|
| 1 | 1 | 2023-06-23 16:41:39.310 | 2023_06_23_05_40_01_Snapshot_Main_Production.zip | 128302768 |
| 2 | 2 | 2023-06-24 05:40:17.903 | 2023_06_24_05_40_01_Snapshot_Main_Production.zip | 90519637 |
| 3 | 3 | 2023-06-25 05:40:21.690 | 2023_06_25_05_40_01_Snapshot_Main_Production.zip | 110933251 |
| 4 | 4 | 2023-06-26 05:40:20.273 | 2023_06_26_05_40_01_Snapshot_Main_Production.zip | 91654658 |
| 5 | 5 | 2023-06-27 05:40:25.453 | 2023_06_27_05_40_01_Snapshot_Main_Production.zip | 110334977 |
| 6 | 6 | 2023-06-28 05:40:22.883 | 2023_06_28_05_40_01_Snapshot_Main_Production.zip | 109150472 |
| 7 | 7 | 2023-06-29 05:40:22.717 | 2023_06_29_05_40_01_Snapshot_Main_Production.zip | 109364919 |
| 8 | 8 | 2023-06-30 05:40:22.153 | 2023_06_30_05_40_01_Snapshot_Main_Production.zip | 109968814 |
And the DDL to create that table and insert records:
CREATE TABLE [tbl_SnapshotSize](
[LogId] [int] IDENTITY(1,1) NOT NULL,
[LogDateTime] [datetime] NOT NULL,
[FileName] [nvarchar](1000) NULL,
[Size] [bigint] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [tbl_SnapshotSize] ON
GO
INSERT [tbl_SnapshotSize] ([LogId], [LogDateTime], [FileName], [Size]) VALUES (1, CAST(N'2023-06-23T16:41:39.310' AS DateTime), N'2023_06_23_05_40_01_Snapshot_Main_Production.zip', 128302768)
GO
INSERT [tbl_SnapshotSize] ([LogId], [LogDateTime], [FileName], [Size]) VALUES (2, CAST(N'2023-06-24T05:40:17.903' AS DateTime), N'2023_06_24_05_40_01_Snapshot_Main_Production.zip', 90519637)
GO
INSERT [tbl_SnapshotSize] ([LogId], [LogDateTime], [FileName], [Size]) VALUES (3, CAST(N'2023-06-25T05:40:21.690' AS DateTime), N'2023_06_25_05_40_01_Snapshot_Main_Production.zip', 110933251)
GO
INSERT [tbl_SnapshotSize] ([LogId], [LogDateTime], [FileName], [Size]) VALUES (4, CAST(N'2023-06-26T05:40:20.273' AS DateTime), N'2023_06_26_05_40_01_Snapshot_Main_Production.zip', 91654658)
GO
INSERT [tbl_SnapshotSize] ([LogId], [LogDateTime], [FileName], [Size]) VALUES (5, CAST(N'2023-06-27T05:40:25.453' AS DateTime), N'2023_06_27_05_40_01_Snapshot_Main_Production.zip', 110334977)
GO
INSERT [tbl_SnapshotSize] ([LogId], [LogDateTime], [FileName], [Size]) VALUES (6, CAST(N'2023-06-28T05:40:22.883' AS DateTime), N'2023_06_28_05_40_01_Snapshot_Main_Production.zip', 109150472)
GO
INSERT [tbl_SnapshotSize] ([LogId], [LogDateTime], [FileName], [Size]) VALUES (7, CAST(N'2023-06-29T05:40:22.717' AS DateTime), N'2023_06_29_05_40_01_Snapshot_Main_Production.zip', 109364919)
GO
INSERT [tbl_SnapshotSize] ([LogId], [LogDateTime], [FileName], [Size]) VALUES (8, CAST(N'2023-06-30T05:40:22.153' AS DateTime), N'2023_06_30_05_40_01_Snapshot_Main_Production.zip', 109968814)
GO
SET IDENTITY_INSERT [tbl_SnapshotSize] OFF
GO
ALTER TABLE [tbl_SnapshotSize] ADD CONSTRAINT [DF_tbl_SnapShot_LogDateTime] DEFAULT (getdate()) FOR [LogDateTime]
GO
Full background: These records are inserted by a PowerShell script that downloads cloud app backups to our LAN. We need to make sure those backup sizes change daily (the backup process fails sometimes, and doesn't notify or alert anyone, and the PowerShell keeps downloading the same stale backup file again and again until someone notices, usually at the worst time).
I plan to query this view, and if the "size difference - percent" value is zero, that means the backup failed, so an email alert will be sent to the service desk and app administrators.
I also plan to send alerts if the "size difference - percent" is greater than ABS(.2), which could also be a red flag for different reasons.