SQL 2000 and 2005 Taskpad View Replacement using SQL 2005 RS

Originally published Saturday, December 29, 2007 at http://www.biblereadingschedule.com/nucleus?itemid=76&catid=6

As promised, here are a stored procedure and a SQL Server 2005 Reporting Services report that give you a view similar to the Taskpad view in the old SQL 2000 Enterprise Manager. This report is not meant to emulate every feature in the Taskpad view. Rather I created it as a handy tool to help manage file sizes and other aspects of SQL servers. The stored procedure and report will work accurately against both SQL 2000 and 2005 instances.

The output includes logical and physical file names and sizes, file locations, used vs. free space in each file, database compatibility level of the database, database recovery model (full, simple, or bulk logged), and Autogrowth settings. Additionally, the report includes sections for free disk space, SQL edition and version, and configuration settings.

First the stored procedure, which you will need to deploy in the master database of every SQL Instance you wish to report on.

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.

USE [myDatabaseName]

USE [master]
GO

IF OBJECT_ID('dba_rpt_SQL_Files_Report') IS NOT NULL
	DROP PROCEDURE dba_rpt_SQL_Files_Report
GO

CREATE PROCEDURE [dbo].[dba_rpt_SQL_Files_Report]

AS

/*

	12/29/2007 by Norm Enger, http://thesqlguy.wordpress.com/2010/11/15/sql-2000-and-2005-taskpad-view-replacement-using-sql-2005-rs/

	Reports on the file info for all dbs on a SQL instance, SQL 2000 and above.

	EXEC dba_rpt_SQL_Files_Report
	
	8/29/2007 - NE - Added db compatibility level 
	(cmptlevel from sysdatabases) as part of results.
	
	12/21/2007 - NE - Added Autogrowth.
	
	1/5/2009 - NE - Added tempdb initial file sizes 
	(sizes they would be upon a server restart) and 
	growth.
	
	5/6/2010 - NE - Changed DEC(* casts to DEC(38,7).

	12/27/2011 - NE - Fixed bug that did not handle 
	multiple tlog files. If there is more than one tlog 
	file, each will now be displayed, and the used 
	space of each is calculated based on logused 
	(percentage) value returned from "DBCC sqlperf(logspace) 
	with no_infomsgs" applied against each tlog file.

*/

SET NOCOUNT ON

DECLARE @cmd1 VARCHAR(8000)
DECLARE @cmd2 VARCHAR(8000)
DECLARE @cmd3 VARCHAR(8000)

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,
	init_size_MB DECIMAL (38,7) NULL,
	tempdb_growth_MB DECIMAL (38,7) 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(15,7) NOT NULL,
	LogSize      numeric(38,7) NOT NULL,
--	LogUsed      numeric(9,5) NOT NULL,
	LogUsed      numeric(38,7) NOT NULL,
	Status       int NOT NULL,
	[Name]   VARCHAR(300) NULL,
	FilePath     VARCHAR(500) NULL,
	Autogrowth	 VARCHAR(500) NULL,
	init_size_MB DECIMAL (38,7) NULL,
	tempdb_growth_MB DECIMAL (38,7) NULL
)

DECLARE @DBs table (name sysname, status varchar(100), dbcompat varchar(10))
DECLARE @CurrDB varchar(500)
DECLARE @CurrCmptlevel varchar(10)

INSERT INTO @DBs 
(name, dbcompat)
SELECT name, cmptlevel
FROM sysdatabases 
WHERE category IN ('0', '1','16') 
AND CAST(DATABASEPROPERTYEX (name, 'status') AS VARCHAR(500)) = 'ONLINE'
ORDER BY name

SELECT @CurrDB = MIN(name) FROM @DBs
SELECT @CurrCmptlevel = dbcompat FROM @DBs WHERE name = @CurrDB

--Data files
WHILE @CurrDB IS NOT NULL
BEGIN
	SET @cmd1 = 'USE [' + @CurrDB + ']; INSERT #data (Fileid, FileGroup, TotalExtents, UsedExtents, Name, FileName) EXEC(''DBCC showfilestats with no_infomsgs''); UPDATE #data SET dbname = ''' + @CurrDB + ''', dbcompat = ''' + CAST(@CurrCmptlevel AS VARCHAR(10)) + ''' 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(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 #data d inner join dbo.sysfiles s on d.FileGroup = s.groupid and d.Fileid = s.fileid where d.dbname = ''' + @CurrDB + ''''
	   
	EXEC (@cmd1)

	SELECT @CurrDB = MIN(name) FROM @DBs WHERE name > @CurrDB
	SELECT @CurrCmptlevel = dbcompat FROM @DBs WHERE name = @CurrDB

END

SELECT @CurrDB = MIN(name) FROM @DBs
SELECT @CurrCmptlevel = dbcompat FROM @DBs WHERE name = @CurrDB

--Log files
WHILE @CurrDB IS NOT NULL
BEGIN

	--Update log file logical names
	SET @cmd3 = 'USE [' + @CurrDB + ']; INSERT #log (dbname, LogSize, LogUsed, Status) EXEC(''DBCC sqlperf(logspace) with no_infomsgs''); INSERT #log (dbname, [groupid], LogSize, LogUsed, Status, [Name]) SELECT ''' + @CurrDB + ''' , [s].[groupid] , s.[size] * 0.0078125 as [size], 0, 0, [name] FROM dbo.sysfiles s WHERE [groupid] = 0; USE [' + @CurrDB + ']; 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 [' + @CurrDB + '].dbo.sysfiles s on l.groupid = s.groupid AND l.[Name] COLLATE DATABASE_DEFAULT = s.[name] COLLATE DATABASE_DEFAULT where l.dbname = ''' + @CurrDB + '''; USE [' + @CurrDB + ']; update #log set dbcompat = ''' + @CurrCmptlevel + ''' where dbname = ''' + @CurrDB + ''''

	EXEC (@cmd3)

	SELECT @CurrDB = MIN(name) FROM @DBs WHERE name > @CurrDB
	SELECT @CurrCmptlevel = dbcompat FROM @DBs WHERE name = @CurrDB

END

--Get tempdb initial file sizes - update tempdb data files init sizes
UPDATE [#data]
SET [init_size_MB] = a.Initial_Size_MB
FROM #data d
INNER JOIN 
(
	SELECT 
		[DB_Name] = 'tempdb',
		f.[name] AS logical_file_Name,
		(CAST(f.[size] AS DECIMAL(38,7)) * 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

--Get tempdb initial file sizes - update tempdb log file init sizes
UPDATE [#log]
SET [init_size_MB] = a.Initial_Size_MB
FROM #log l
INNER JOIN 
(
	SELECT 
		[DB_Name] = 'tempdb',
		f.[name] AS logical_file_Name,
		(CAST(f.[size] AS DECIMAL(38,7)) * 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

--Calculate tempdb files growth
UPDATE [#data] 
SET [tempdb_growth_MB] =
CASE 
	WHEN ((TotalExtents*64)/1024.0) - init_size_MB < 0.00 THEN 0.00
	ELSE ((TotalExtents*64)/1024.0) - init_size_MB
END

UPDATE [#log]
SET [tempdb_growth_MB] =
CASE 
	WHEN LogSize - init_size_MB < 0.00 THEN 0.00
	ELSE LogSize - init_size_MB
END

--Return results for all dbs
SELECT
	dbname + ' (' + CAST(DATABASEPROPERTYEX (dbname, 'recovery') AS VARCHAR(500)) + ') (' + dbcompat + ')'  AS [DB_NAME],
	dbname AS DB_NAME_2,
	'Data File' AS [Type],
	[Name] AS [NAME],
	[FilePath],
	init_size_MB,
	(TotalExtents*64)/1024.0 AS [TotalMB],
	tempdb_growth_MB,
	(UsedExtents*64)/1024.0 AS [UsedMB],
	(TotalExtents*64)/1024.0 - (UsedExtents*64)/1024.0 AS [FreeMB],
	[Autogrowth]
FROM #data 
UNION
SELECT
	dbname + ' (' + CAST(DATABASEPROPERTYEX (dbname, 'recovery') AS VARCHAR(500)) + ') (' + dbcompat + ')' AS [DB_NAME],
	dbname AS DB_NAME_2,	
	'TLog File',
	[Name] AS [NAME],
	[FilePath],
	init_size_MB,
	LogSize,
	tempdb_growth_MB,
	((LogUsed/100)*LogSize),
	LogSize - ((LogUsed/100)*LogSize),
	[Autogrowth]
FROM #log 
ORDER BY 1, [Type], [Name]

DROP TABLE #data
DROP TABLE #log

 

Now for the report. HERE is what the final report will look like (exported to .pdf).

And HERE is a zip file with the report .rdl file, which you can use as a template to use with your own list of servers. You will need to manually type your own list of SQL instances in the Report Parameters section of Business Intelligence Design Studio (BIDS).

An idea for further modification that I have implemented but not shared here is populating the list of SQL Instances dynamically from a SQL table, instead of typing them in manually in the Report Parameters section of BIDS. Another idea I have implemented is to create a weekly export of the file for each SQL instance, using the data-driven subscription functionality in SQL Enterprise Ed. Reporting Services, and saved to an archive directory, for historical purposes.

Happy reporting!

Posted November 15, 2010 by Norm Enger in Microsoft SQL Server