TEMPDB Monitoring

Reference of the below script : http://technet.microsoft.com/en-us/library/ms175527(v=sql.105).aspx

We had a requirement in our environment to monitor TEMPDB growth, which required us to send an alert mail out to our inbox at every one hour listing the information of the TEMPDB growth and its size.

I modified this script according to our environment needs and configured this over the SQL server agent to run at every one hour. And this worked fine.

T-SQL SCRIPT

EXEC msdb.dbo.sp_send_dbmail

@profile_name='CIS_DBA',

@recipients=' _2cd407@bose.com',

@subject='TEMPDB monitoring',

@query= 'SELECT

name AS FileName,

size*1.0/128 AS FileSizeinMB,

CASE max_size

WHEN 0 THEN "Autogrowth is off."

WHEN -1 THEN "Autogrowth is on."

ELSE "Log file will grow to a maximum size of 2 TB."

END AutogrowthStatus,

growth AS "GrowthValue",

"GrowthIncrement" =

CASE

WHEN growth = 0 THEN "Size is fixed and will not grow."

WHEN growth > 0

AND is_percent_growth = 0

THEN "Growth value is in 8-KB pages."

ELSE "Growth value is a percentage."

END

FROM tempdb.sys.database_files;'

Go

If you want to execute this script just by your self modify the double quotes in the above script to single quotes as below and you may try executing over a query window .

SELECT
name AS FileName,
size*1.0/128 AS FileSizeinMB,
CASE max_size
WHEN 0 THEN 'Autogrowth is off.'
WHEN -1 THEN 'Autogrowth is on.'
ELSE 'Log file will grow to a maximum size of 2 TB.'
END AutogrowthStatus,
growth AS 'GrowthValue',
'GrowthIncrement' =
CASE
WHEN growth = 0 THEN 'Size is fixed and will not grow.'
WHEN growth > 0
AND is_percent_growth = 0
THEN 'Growth value is in 8-KB pages.'
ELSE 'Growth value is a percentage.'
END
FROM tempdb.sys.database_files;
Go