Display Database, File, Free and Total Disk Space

,

This script displays the size of each SQL Server database and database file plus the size of the next extension that will be added to each file. It also displays on each line the total and remaining free disk space for whichever disk each database file is stored on.

The output is nothing fancy, and could be manipulated in a number of different ways, for example to clearly show which files are not going to be able to extend, which disks are dangerously low on space.

The script can be run from any database, but requires the user to have permissions on a variety of stored procs, the complete list of these is containined in the script heading.

The script was written and tested on SQL Server 2000 on Win2K/WinNT; I don't have enough experience of other version/platform combinations to comment on whether this script will work with them.

/******************************************************************
*
* 		SQL Server Disk Space Check
* 
* This script displays database and file size, compared to total
* and free disk space. It also displays the number of Kb that each
* file will grow by on the next extend.
*
* The script displays sizes in Mb and Kb, so there are sometimes
* small rounding errors in the result set.
*
* Required Permissions:
*	CREATE TABLE (in current db)
*	EXEC ON master.dbo.xp_fixeddrives
*	EXEC ON master.dbo.sp_databases
*	EXEC ON sp_executesql
*	EXEC ON sp_helpfile (in all db's)
*	EXEC ON master.dbo.sp_OACreate
*	EXEC ON master.dbo.sp_OAMethod
*	EXEC ON master.dbo.sp_OADestroy
*	SELECT ON master.dbo.sysaltfiles
*	SELECT ON master.dbo.sysdatabases
*	
******************************************************************/

BEGIN

	/*****************************************
	* Create temp tables for disk space info
	*****************************************/
	CREATE TABLE #space (dletter varchar(2), fspace int, tspace BIGINT)
	CREATE TABLE #dbsize (dbname varchar(50), dbsize int, remarks varchar(255))
	CREATE TABLE #fdata ([name] VARCHAR(255), [filename] VARCHAR(255), 
				[filegroup] VARCHAR(10), [size] VARCHAR(50),
				[maxsize] VARCHAR(50), growth VARCHAR(20), usage VARCHAR(20))
	CREATE TABLE #growth (dbname VARCHAR(50), fname VARCHAR(255), next_exp INT, gtype VARCHAR(2))

	/*****************************************
	* populate temp tables
	*****************************************/
	INSERT INTO #space (dletter, fspace) EXEC master.dbo.xp_fixeddrives
	INSERT INTO #dbsize EXEC master.dbo.sp_databases
	
	-- Create cursor for files
	DECLARE c_files CURSOR FOR
		SELECT RTRIM(af.fileid), RTRIM(af.[name]), RTRIM(af.[filename]), 
			RTRIM(af.[size]), RTRIM(db.[name])
		FROM master.dbo.sysaltfiles af, master.dbo.sysdatabases db
		WHERE af.dbid = db.dbid AND db.version <> 0
	DECLARE @tfileid INT, @tname VARCHAR(255), @tfilename VARCHAR(255)
	DECLARE @tsize INT, @tdbname VARCHAR(50)
	DECLARE @SQL NVARCHAR(255)
	DECLARE @growth VARCHAR(20), @next_exp INT, @gtype VARCHAR(2)

	-- Open cursor
	OPEN c_files
	FETCH NEXT FROM c_files
	INTO @tfileid, @tname, @tfilename, @tsize, @tdbname

	-- Populate #growth table with file growth details
	WHILE @@fetch_status = 0
	BEGIN
		TRUNCATE TABLE #fdata
		-- Get file data
		SET @SQL = 'INSERT INTO #fdata EXEC '
		SET @SQL = @SQL + @tdbname + '.dbo.sp_helpfile ''' + @tname + ''''
		EXEC sp_executesql @SQL
		SELECT @growth = growth FROM #fdata
		-- Determine if growth is % or Mbytes
		IF RIGHT(@growth,1) = '%'
		BEGIN
			SET @next_exp = CAST(LEFT(@growth, LEN(@growth) - 1) AS INT)
			SET @next_exp = CAST(ROUND(((CAST(@tsize AS FLOAT) * 8) / 100) * @next_exp,0) AS INT)
			SET @gtype = '%'
		END
		ELSE
		BEGIN
			SET @next_exp = CAST(LEFT(@growth, CHARINDEX(' ',@growth)) AS INT)
			SET @gtype = 'MB'
		END
		-- Create record for file in #growth table
		INSERT INTO #growth VALUES (@tdbname, @tname, @next_exp, @gtype)
		FETCH NEXT FROM c_files
		INTO @tfileid, @tname, @tfilename, @tsize, @tdbname
	END

	-- Close cursor
	CLOSE c_files
	DEALLOCATE c_files

	/*****************************************
	* Update temp table info with total disk sizes
	*****************************************/
	-- Create cursor for disk space table
	DECLARE c_disks CURSOR FOR
		SELECT dletter, fspace, tspace FROM #space
		FOR UPDATE
	DECLARE @dletter VARCHAR(2), @fspace INT, @tspace BIGINT

	-- Create FileSystemObject
	DECLARE @oFSO INT, @oDrive INT, @drsize VARCHAR(255), @ret INT
	EXEC @ret = master.dbo.sp_OACreate 'scripting.FileSystemObject', @oFSO OUT
	
	-- Open cursor and fetch first row
	OPEN c_disks
	FETCH NEXT FROM c_disks
	INTO @dletter, @fspace, @tspace

	-- Loop through all records in the cursor
	WHILE @@fetch_status = 0
	BEGIN
		-- Get disk size
		SET @dletter = @dletter + ':\'
		EXEC @ret = master.dbo.sp_OAMethod @oFSO, 'GetDrive', @oDrive OUT, @dletter
		EXEC @ret = master.dbo.sp_OAMethod @oDrive, 'TotalSize', @drsize OUT
		-- Update table
		UPDATE #space
		SET tspace = CAST(@drsize AS BIGINT)
		WHERE CURRENT OF c_disks
		-- Destory oDrive
		EXEC master.dbo.sp_OADestroy @oDrive
		-- Fetch next row
		FETCH NEXT FROM c_disks
		INTO @dletter, @fspace, @tspace
	END

	-- Close cursor
	CLOSE c_disks
	DEALLOCATE c_disks

	-- Destroy FSO
	EXEC master.dbo.sp_OADestroy @oFSO

	/*****************************************
	* Return disk space info
	*****************************************/
	SELECT db.[name] as [Database Name], 
		af.[name] as [File Name],
		CAST(ROUND(CAST(ds.dbsize AS FLOAT) / 1024,0) AS INT) as [Database Size (Mb)],  
		CAST(ROUND((CAST(af.[size] AS FLOAT) * 8) / 1024,0) AS INT) as [File Size (Mb)], 
		s.fspace as [Free Disk Space (Mb)], 
		CAST(ROUND((CAST(s.tspace AS FLOAT) / 1024) /1024,0) AS INT) as [Total Disk Space (Mb)],
		STR(g.next_exp) + ' KB' as [Next Extension],
		--CAST(ROUND(CAST(g.next_exp AS FLOAT) / 1024,0) AS INT) as [Next Expansion],
		af.[filename] as [File Path]
	FROM master.dbo.sysaltfiles af, #space s, #dbsize ds, 
		master.dbo.sysdatabases db, #growth g
	WHERE s.dletter = LEFT(af.[filename],1)
	AND af.dbid = db.dbid
	AND db.[name] = ds.dbname
	AND g.dbname = db.[name]
	AND g.fname = af.[name]
	ORDER BY db.[name], af.[name]

	/*****************************************
	* Drop temporary tables
	*****************************************/
	DROP TABLE #space
	DROP TABLE #dbsize
	DROP TABLE #fdata
	DROP TABLE #growth

END

Rate

1 (1)

Share

Share

Rate

1 (1)