Detect When Your tempdb Files Have Grown   Leave a comment

One goal of managing your tempdb database files should be to size them appropriately in order to avoid auto-growth. Here is a handy script that works on all versions of SQL Server (2000 and above) to show whether your tempdb data and log files have grown beyond their initial sizes (the sizes they will be upon restarting of the SQL Server services). In other words, the script will tell you if auto-growth has occurred.

In most cases, if auto-growth has occurred, you will want to increase the initial size of an individual file so that this will not occur. Changing the initial size can be done easily through the SSMS GUI by expanding Databases –> System Databases and right-clicking on tempdb –> Properties. Then in the Properties window, go to the Files tab and adjust the size.

The script displays the initial size for each tempdb file, the current file size for each file, and growth beyond the initial size that has occurred, in MB, if any. As well, it displays the logical and physical file names and paths, current used and free space within the files, and auto-growth settings (e.g., “Autogrowth: By 10 percent, unrestricted growth”).

A suggested way to use this script, which I have implemented but have not demonstrated here, is to schedule it to run against each of your SQL instances daily, and if auto-growth has occurred, notify you via email.

Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.

--tempdb - Show files info with initial tempdb size integrated

USE [master]

IF OBJECT_ID('tempdb..#data') IS NULL
	CREATE TABLE #data(
	dbname VARCHAR(300),
	dbcompat VARCHAR(10),
	Fileid int NOT NULL,
	[FileGroup] int NOT NULL,
	TotalExtents int NOT NULL,
	UsedExtents int NOT NULL,
	[Name] sysname NOT NULL,
	[FileName] varchar(300) NOT NULL,
	FilePath VARCHAR(500) NULL,
	Autogrowth	 VARCHAR(500) NULL
)

IF OBJECT_ID('tempdb..#log') IS NULL
CREATE TABLE #log(
	id int identity(1,1) not null,
	groupid INT DEFAULT 0 NULL,
	dbname sysname NOT NULL,
	dbcompat VARCHAR(10),
	LogSize numeric(38,7) NOT NULL,
	LogUsed numeric(38,6) NOT NULL,
	Status int NOT NULL,
	[Name] VARCHAR(300) NULL,
	FilePath VARCHAR(500) NULL,
	Autogrowth	 VARCHAR(500) NULL
)

USE [tempdb] 

INSERT #data (Fileid, FileGroup, TotalExtents, UsedExtents, Name, FileName)
EXEC('DBCC showfilestats with no_infomsgs') 

UPDATE #data SET dbname = 'tempdb'
WHERE dbname IS NULL 

UPDATE #data
SET [FilePath] = s.filename,
Autogrowth = 'Autogrowth: '
+
CASE
	WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00)
		OR s.growth = 0 THEN 'None'
	WHEN s.status & 0x100000 = 0 THEN 'By ' + CONVERT(VARCHAR,CEILING((s.growth * 8192.0)/(1024.0*1024.0))) + ' MB'
	ELSE 'By ' + CONVERT(VARCHAR, s.growth) + ' percent' END
+
CASE
	WHEN (s.status & 0x100000 = 0 AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00)
		OR s.growth = 0 THEN ''
	WHEN CAST([maxsize]*8.0/1024 AS DEC(20,2)) <= 0.00 THEN ', unrestricted growth'
	ELSE ', restricted growth to ' + CAST(CAST([maxsize]*8.0/1024 AS DEC(20)) AS VARCHAR) + ' MB'
END
FROM #data d
INNER JOIN dbo.sysfiles s
ON d.FileGroup = s.groupid AND d.Fileid = s.fileid
WHERE d.dbname = 'tempdb'

USE [tempdb];
INSERT #log
(
	dbname,
	LogSize,
	LogUsed,
	Status
)
EXEC('DBCC sqlperf(logspace) with no_infomsgs');
INSERT #log
(
	dbname,
	[groupid],
	LogSize,
	LogUsed,
	Status,
	[Name]
)
SELECT 
	'tempdb',
	[s].[groupid],
	s.[size] * 0.0078125 AS [size],
	0,
	0,
	[name]
FROM dbo.sysfiles s
WHERE [groupid] = 0;

USE [tempdb];

UPDATE #log
SET [#log].[LogUsed] = b.[LogUsed]
FROM [#log] t
INNER JOIN
 ( SELECT 
		[l].[dbname],
		[LogUsed]
	FROM #log l
	LEFT JOIN
		( SELECT dbname,
		Name,
		MIN(id) AS id
		FROM #log
		GROUP BY dbname,
		Name
		) k
	ON l.dbname = k.dbname
	AND l.Name = k.Name
	AND l.id = k.id
	WHERE k.id IS NULL
	)
	AS b
	ON [t].[dbname] = [b].[dbname];

DELETE #log
WHERE Name IS NULL;

USE [master];
UPDATE #log
SET [Name] = s.name,
[FilePath] = s.filename,
Autogrowth = 'Autogrowth: ' +
CASE
	WHEN
	(
	s.status & 0x100000 = 0
	AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00
	)
	OR s.growth = 0
	THEN 'None'
	WHEN s.status & 0x100000 = 0
	THEN 'By ' + CONVERT(VARCHAR,CEILING((s.growth * 8192.0)/(1024.0*1024.0))) + ' MB'
	ELSE 'By ' + CONVERT(VARCHAR, s.growth) + ' percent'
END +
CASE
	WHEN
	(
	s.status & 0x100000 = 0
	AND CEILING((s.growth * 8192.0)/(1024.0*1024.0)) = 0.00
	)
	OR s.growth = 0
	THEN ''
	WHEN CAST([maxsize]*8.0/1024 AS DEC(38,7)) <= 0.00
	THEN ', unrestricted growth'
	ELSE ', restricted growth to ' + CAST(CAST([maxsize]*8.0/1024 AS DEC(38,7)) AS VARCHAR) + ' MB'
END
FROM #log l
INNER JOIN [tempdb].dbo.sysfiles s
	ON l.groupid = s.groupid
	AND l.[Name] COLLATE DATABASE_DEFAULT = s.[name] COLLATE DATABASE_DEFAULT
WHERE l.dbname = 'tempdb';

--SELECT * FROM [#data]
--SELECT * FROM [#log] WHERE dbname = 'tempdb'

SELECT
DB_Name,
[Type],
[Name],
[FilePath],
init_size_MB,
[CurrTotalMB],
tempdb_growth_MB =
CASE
	WHEN [CurrTotalMB] - init_size_MB < 0 THEN 0
	ELSE [CurrTotalMB] - init_size_MB
END,
UsedMB,
FreeMB,
Autogrowth
FROM
(
SELECT
	dbname AS [DB_Name],
	'Data File' AS [Type],
	[Name] AS [Name],
	[FilePath],
	init_size_MB = a.Initial_Size_MB,
	(TotalExtents*64)/1024.0 AS [CurrTotalMB],
	(UsedExtents*64)/1024.0 AS [UsedMB],
	(TotalExtents*64)/1024.0 - (UsedExtents*64)/1024.0 AS [FreeMB],
	[Autogrowth]
FROM #data d
	INNER JOIN
	(
		SELECT
			[DB_Name] = 'tempdb',
			f.[name] AS logical_file_Name,
			(CAST(f.[size] AS DECIMAL) * 8192) / 1024 /1024 AS Initial_Size_MB
		FROM master..sysaltfiles f
			INNER JOIN master..sysdatabases d
			ON f.dbid = d.dbid
		WHERE d.name = 'tempdb') AS a
	ON d.dbname = a.[DB_Name]
	AND d.[Name] = a.logical_file_Name
WHERE [dbname] = 'tempdb'
UNION
SELECT
	dbname AS [DB_Name],
	'TLog File',
	[Name] AS [NAME],
	[FilePath],
	init_size_MB = a.Initial_Size_MB,
	LogSize,
	((LogUsed/100)*LogSize),
	LogSize - ((LogUsed/100)*LogSize),
	[Autogrowth]
FROM #log l
	INNER JOIN
	(
		SELECT
			[DB_Name] = 'tempdb',
			f.[name] AS logical_file_Name,
			(CAST(f.[size] AS DECIMAL) * 8192) / 1024 /1024 AS Initial_Size_MB
		FROM master..sysaltfiles f
			INNER JOIN master..sysdatabases d
			ON f.dbid = d.dbid
		WHERE d.name = 'tempdb') AS a
	ON l.dbname = a.[DB_Name]
	AND l.[Name] = a.logical_file_Name
WHERE [dbname] = 'tempdb'
) AS r
ORDER BY 1, [Type], [Name]

IF OBJECT_ID('tempdb..#data') IS NOT NULL
DROP TABLE [#data]
IF OBJECT_ID('tempdb..#log') IS NOT NULL
DROP TABLE [#log]

Posted January 1, 2011 by Norm Enger in Microsoft SQL Server

Advertisements

Posted January 1, 2011 by Norm Enger in Microsoft SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: