TempDb Monitor for Snapshot Data

Recently I enabled one of my databases to use SNAPSHOT ISOLATION LEVEL and today I wanted to create something to help me monitor the tempdb database. I was wishing to create an automated email that would alert me if things got out of hand. My new solution needed to alert me if tempdb grew more than five percent (5%) since the day before, or if the snapshot data was using more than fifty percent (50%) of the pages.

First, I created a table that would store the values in a table around noon every day. That way there would be a benchmark for comparison.

USE Maintenance;
GO

CREATE TABLE [dbo].[TempDbPageAudit](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	TotalPageCount [bigint] NULL,
	AllocatedExtentPageCount [bigint] NULL,
	UnallocatedExtentPageCount [bigint] NULL,
	VersionStoreReservedPageCount [bigint] NULL,
	PagePercentUsed DECIMAL(18,6),
	VersionStorePercentUsed DECIMAL(18,6),
	[ExecDateTime] [datetime] NULL,
	[DailyMarker] [bit] NULL,
	[AlertRecord] [bit] NULL,
	[AlertEmailSent] [bit] NULL,
	[Error] [bit] NULL,
 CONSTRAINT [PK_dbo.TempDbPageAudit] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Next, I needed a query to do all the calculations.

SELECT 
	SUM(total_page_count) AS TotalPageCount,
	SUM(allocated_extent_page_count) AS AllocatedExtentPageCount,
	SUM(unallocated_extent_page_count) AS UnallocatedExtentPageCount,
	SUM(version_store_reserved_page_count) AS VersionStoreReservedPageCount,
	(SUM(CONVERT(DECIMAL(18,6),allocated_extent_page_count)) / SUM(CONVERT(DECIMAL(18,6),total_page_count)))*100 AS PercentUsed,
	(SUM(CONVERT(DECIMAL(18,6),version_store_reserved_page_count)) / SUM(CONVERT(DECIMAL(18,6),total_page_count)))*100 AS VersionStorePercentUsed
FROM tempdb.sys.dm_db_file_space_usage;

I took the query and rolled it up into a stored procedure that would send me an alert if things were out of order.

CREATE OR ALTER PROCEDURE [dbo].[E_Temp_Db_Alert] 
@E_profile_name nvarchar(250),
@E_recipients nvarchar(2000),
@Threshold DECIMAL(18,6) = 80.00000,
@DateOffset INT = -5
AS
BEGIN

DECLARE @MainTitle VARCHAR(100) = 'Temp DB Alert';
DECLARE @ReportName VARCHAR(100) = 'dbo.E_Temp_Db_Alert';

DECLARE @ServerName VARCHAR(100);
-- Since this is an AWS RDS instance I will need to lookup my Server Name.
SELECT @ServerName = ServerResolvedName FROM ServerAnalysis.Analysis.Server WHERE ServerNm = @@SERVERNAME;
-- For non AWS-RDS use:  SELECT @ServerName = @@SERVERNAME;

DECLARE @YesterDayPageCount BIGINT;
DECLARE @curDate datetime;
DECLARE @isDailyMarker bit=0;
DECLARE @AlertMessages varchar(2500)='';
DECLARE @body1 nvarchar(max);
DECLARE @bodyFIN nvarchar(max);
DECLARE @StyleSheet VARCHAR(1000);
DECLARE @Header VARCHAR(400);
DECLARE @GreenTitle VARCHAR(250);
DECLARE @SubjectTxt   VARCHAR(50);
DECLARE @tSql varchar(8000);

SELECT @curDate = getutcdate();

IF DATEPART(hour,DATEADD(hour,@DateOffset,@curDate)) = 12
BEGIN
SELECT @isDailyMarker = 1;
END;

DECLARE @tRecord AS TABLE (
	TotalPageCount [bigint] NULL,
	AllocatedExtentPageCount [bigint] NULL,
	UnallocatedExtentPageCount [bigint] NULL,
	VersionStoreReservedPageCount [bigint] NULL,
	PagePercentUsed DECIMAL(18,6),
	VersionStorePercentUsed DECIMAL(18,6),
	[ExecDateTime] [datetime] NULL,
	[DailyMarker] [bit] NULL,
	[AlertRecord] [bit] NULL,
	[AlertEmailSent] [bit] NULL,
	[Error] [bit] NULL);

SELECT @StyleSheet = 
	'<style><!--
	@font-face
		{font-family:"Microsoft Sans Serif";
		panose-1:2 11 6 4 2 2 2 2 2 4;}
	p.MsoNormal, li.MsoNormal, div.MsoNormal
		{margin:0in;
		font-size:11.0pt;
		font-family:"Calibri",sans-serif;}
	--></style>',
	@Header = CONCAT('<p class="MsoNormal" style="background:white"><span style="font-size:16pt;color:#009A44;letter-spacing:-1.0pt">COVENT</span><span style="font-size:16pt;color:#0C2340">BRIDGE GROUP</span></p><BR /><span style="font-size:16pt;color:#0C2340"><p><span style="font-size:16pt;color:#0C2340">',@MainTitle,CONVERT(varchar(12),getdate()),' </span></p>'),
	@GreenTitle = '<p class="MsoNormal" style="background:white"><span style="font-size:16.0pt;font-family:&amp;quot;color:#009A44;letter-spacing:-1.0pt">'

INSERT INTO @tRecord
([TotalPageCount], [AllocatedExtentPageCount], [UnallocatedExtentPageCount], [VersionStoreReservedPageCount], [PagePercentUsed], [VersionStorePercentUsed], 
[ExecDateTime], [DailyMarker])
SELECT 
	SUM(total_page_count) AS TotalPageCount,
	SUM(allocated_extent_page_count) AS AllocatedExtentPageCount,
	SUM(unallocated_extent_page_count) AS UnallocatedExtentPageCount,
	SUM(version_store_reserved_page_count) AS VersionStoreReservedPageCount,
	(SUM(CONVERT(DECIMAL(18,6),allocated_extent_page_count)) / SUM(CONVERT(DECIMAL(18,6),total_page_count)))*100 AS PercentUsed,
	(SUM(CONVERT(DECIMAL(18,6),version_store_reserved_page_count)) / SUM(CONVERT(DECIMAL(18,6),total_page_count)))*100 AS VersionStorePercentUsed
	 ,@curDate,
	@isDailyMarker
FROM tempdb.sys.dm_db_file_space_usage;

-- If ALERT CONDTION create it.
	-- Alert If DB size has grown more than 5% since yesterday (or no previous record found)
	SELECT @YesterDayPageCount = ISNULL((SELECT TOP(1) AllocatedExtentPageCount FROM dbo.TempDbPageAudit ORDER BY ID DESC),0);
	IF (SELECT((CONVERT(DECIMAL(18,6),AllocatedExtentPageCount) - CONVERT(DECIMAL(18,6),@YesterDayPageCount))) /  (CONVERT(DECIMAL(18,6),AllocatedExtentPageCount)) FROM @tRecord) > 5.00
	BEGIN 
	SELECT @AlertMessages = CONCAT(@AlertMessages,' ||ALERT| TempDB has grown by by more than 5%. ');
	END;

-- Alert if available free space is under 20%
IF (SELECT PagePercentUsed FROM @tRecord) > @Threshold
BEGIN
SELECT @AlertMessages = CONCAT(@AlertMessages,' ||ALERT| TempDB PagePercentUsed is more than ',CONVERT(VARCHAR(24),@Threshold),' of tempDb allocated disk space. ');
END;

-- Alert if Versioned Percent is more than 75%
IF (SELECT VersionStorePercentUsed FROM @tRecord) > (@Threshold / 2)
BEGIN
SELECT @AlertMessages = CONCAT(@AlertMessages,' ||ALERT| Version Store Percent on TempDB has exceeded more than 50% of page use. ');
END;

IF @isDailyMarker = 1
BEGIN
INSERT INTO dbo.TempDbPageAudit 
([TotalPageCount], [AllocatedExtentPageCount], [UnallocatedExtentPageCount], [VersionStoreReservedPageCount], [PagePercentUsed], [VersionStorePercentUsed], [ExecDateTime], [DailyMarker], [AlertRecord], [AlertEmailSent], [Error])
SELECT [TotalPageCount], [AllocatedExtentPageCount], [UnallocatedExtentPageCount], [VersionStoreReservedPageCount], [PagePercentUsed], [VersionStorePercentUsed], [ExecDateTime], @isDailyMarker, 0, 0, 0
FROM @tRecord;
END;

If LEN(@AlertMessages) > 2
BEGIN
-- SEND EMAIL
SELECT @SubjectTxt   = CONCAT(@ServerName,' ',@MainTitle)
	,@BodyFin = CONCAT( @StyleSheet,@Header,'<BR />',@GreenTitle,@ServerName,' ',@MainTitle,'</span></p><BR />', @body1,'<BR /><BR />'
	,@GreenTitle,'Report generated by ',@ServerName,'.',DB_NAME(),'.',@ReportName,'.</span></p><BR />');
		
EXEC msdb.dbo.sp_send_dbmail
	@profile_name = @E_profile_name,
	@recipients = @E_recipients,
	@body = @BodyFin,
	@subject = @SubjectTxt,
	@body_format = 'HTML';

INSERT INTO dbo.TempDbPageAudit 
	(   [TotalPageCount], [AllocatedExtentPageCount], [UnallocatedExtentPageCount], [VersionStoreReservedPageCount], [PagePercentUsed], [VersionStorePercentUsed], [ExecDateTime], [DailyMarker], [AlertRecord], [AlertEmailSent], [Error])
SELECT  [TotalPageCount], [AllocatedExtentPageCount], [UnallocatedExtentPageCount], [VersionStoreReservedPageCount], [PagePercentUsed], [VersionStorePercentUsed], [ExecDateTime], 0, 1, 1, 1
FROM @tRecord;
END;

END
GO

And finally, I create a SQL Agent Job to run every hour between 6:30 AM and 10:30 PM.

Using the SQL AGENT I created a new job:

1. Job titled: E_Report – Hourly
2. Add one step: TempDb Check
3. Type: Transact-SQL script
4. Database: Maintenance
5. Enter command: EXEC dbo.E_Temp_Db_Alert ‘SqlEmailProfile‘,’david.speight@mydomain.com’
6. Click OK.
7. Then I set up the SCHEDULES tab to complete the task.

Run the SQL AGENT job and test it out.