SQL Server – Alerts – “The SQL Server performance counter ‘Data File(s) Size (KB)’ (instance ‘tempdb’) of object ‘Databases’ is now above the threshold”

Background

Getting quite a bit of email alerts from one of our SQL Server Instance.

Alert Notification

Image

Textual


DATE/TIME:
1/29/2018 9:17:09 AM

DESCRIPTION:
The SQL Server performance counter 'Data File(s) Size (KB)' (instance 'tempdb') of object 'Databases' is now above the threshold of 20000000.00 (the current value is 24428544.00).

Explanation

The message reads that our threshold is 20000000.00 and our current value is 24428544.00.

 

Interpretation

We will see later that the threshold and current current value are represented in KB.

Using Google we can convert KB to MB or convert to GB

Here we go:

  1. Convert to MB
    • Google Link
    • Threshold
      • 20000000.00 ( KB )
      • 20000 MB
    • Current
      • 24428544.00 ( KB )
      • 24428.544 MB
  2. Convert to GB
    • Google Link
    • Threshold
      • 20000000.00 ( KB )
      • 20 GB
    • Current
      • 24428544.00 ( KB )
      • 24.428544 GB

 

 

Alert Definition

GUI

TempDB Alert Properties

TempDB Alert Properties – Tab – General

Image

Explanation
  1. The alert states that if tempdb grows over 20000000 ( KB) or 20 GB an alert should be generated

 

TempDB Alert Properties – Tab – History

 

Metadata

Let us quickly review our current tempdb utilizations.

sp_helpdb

Let us use the most accessible built-in function, sp_helpdb

Code


exec sp_helpdb [tempdb]

Output

 

File utilization – Detail

List tempdb file utilization

Code


select 
          [fileID] 
            = tblSMF.[file_id]

        , [fileType]
            = tblSMF.[type_desc]

        , [fileSymbolicName] 
            = tblSMF.[name]

        , [filePhysicalName] 
            = tblSMF.[physical_name]

        , [fileState] 
            = tblSMF.[state_desc]

        , [isReadOnly]
            = case
				when (tblSMF.[is_read_only] = 1) then 'Yes'
				when (tblSMF.[is_read_only] = 0) then 'No'
				else 'unknown'
			  end


        /*

            , [fileSize] 
                = tblSMF.[size]

        */

        , [fileSizeIn8KPages] 
            = tblSMF.[size]

        , [fileSizeInMB] 
            = ( tblSMF.[size] * 8) / 1000

        , [fileSizeInGB] 
            = ( tblSMF.[size] * 8) 
                    / ( 1000 * 1000) 

        , [growth]
            = 
            (
                CASE is_percent_growth
                    WHEN 1 
                        THEN CONVERT(NVARCHAR(15), growth) + N'%'
                    ELSE 
                        CONVERT
                        (
                              VARCHAR(15)
                            , CONVERT
                                (
                                    BIGINT
                                    , ( tblSMF.[growth] * 8 )
                                        / 1000
                                )
                        ) 
                        + ' MB'
                END
            )

        --, tblSMF.*

from   sys.master_files tblSMF

where  (

            ( tblSMF.database_id = db_id('tempdb') )

       )

order by
          tblSMF.[type] asc
        , tblSMF.[name] asc

Output

 

 

File Utilization – Summary

Summarize tempdb file utilization

Code



;with cteFile
(
	  [fileTypeID]
	, [fileType]
	, [fileSize] 
)
as
(
	select 

			  [fileTypeID]
				= tblSMF.[type]

			,  [fileType]
				= tblSMF.[type_desc]

			, [fileSize] 
				= sum(tblSMF.[size])


	from   sys.master_files tblSMF

	where  (

				( tblSMF.database_id = db_id('tempdb') )

		   )

	group by
		  tblSMF.[type]
		, tblSMF.[type_desc]

)
, cteFileSummary
(
	[fileSize] 
)
as
(
	select 
		 [fileSize] 
				= sum(tblSMF.[size])

	from   sys.master_files tblSMF

	where  (

				( tblSMF.database_id = db_id('tempdb') )

		   )

)

select 
		  cteF.fileType

		, [fileSizeIn8KPages] 
			= cteF.fileSize

		, [fileSizeInGB] 
			= cast
				(
					( cteF.fileSize * 8 * 1.000) 
						/ ( 1000 * 1000) 

					as decimal(20, 2)
				)


		, [fileSizeTotalIn8KPages]
			= cteFS.fileSize

		, [fileSizeTotalInGB] 
			= cast
				(
					( cteFS.fileSize * 8 * 1.000) 
						/ ( 1000 * 1000) 

					as decimal(20, 2)
				)

		, [%]
			= cast
				(
					(
						(cteF.fileSize * 100.000 )
						/  cteFS.fileSize
					)
					as decimal(20, 2)
				)
				

from   cteFile cteF

cross apply cteFileSummary cteFS

order by
		cteF.fileTypeID




Output

Explanation

  1. File Utilization in tempdb
    • Data :- 19.92 GB
    • Log :- 1.05 GB
    • Total :- 20.97 GB

 

Summary

The alerts are valid, tempdb has grown to a little under 21 GB.

Our threshold is at 20 Gb.

Next in line, determine what is driving tempdb usage.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s