Database,Drive,Data file growth monitor

/*******************************
Place in job to do a nightly dump into table for monitoring purposes.
**********************************/

USE ServerControl
GO

DECLARE @DBName varchar(50), @SQL nvarchar(250)

DECLARE  tmpDBRetrieval CURSOR FOR
	SELECT [name] from master.dbo.sysdatabases
	WHERE DBID NOT IN (1,2,3,4)  AND status NOT IN (16,32,64,128,256,512,520)
	ORDER BY [name]
	OPEN tmpDBRetrieval
	FETCH NEXT FROM tmpDBRetrieval 
	INTO @DBName

	WHILE @@FETCH_STATUS = 0
	BEGIN
			SET @SQL = N'exec ServerControl.dbo.SpaceInfo ''' + @DBName + ''''
			EXEC (@SQL)			
			--print @sql
			IF @@ERROR <> 0
			BEGIN
				GOTO IFERROR
			END
IFERROR:
FETCH NEXT FROM tmpDBRetrieval 
INTO @DBName

END 
CLOSE tmpDBRetrieval
DEALLOCATE tmpDBRetrieval

--********************************************************
-- CODE FOR STORED PROCEDURE THAT GETS THE INFORMATION
--********************************************************

/*
*************************************
The purpose of this is to go and check each file in a particular DB/Filegroup and calculate how much space is available.
In addition to that I have included the following columns to help with determining whether space should be added or not.
** this should help in taking a pro-active approach to space issues

columns:
Group_Name  = file group name that file is a member of
[File_Name] = file name  ,
Drive = drive the file reside on,
Allocated_Space = amount of space that has been allocated for file,
Actual_Size = actual amount of space that the file has used,
Space_Available = the remaining allocated space ,
Total_FREE_Drive_Space = amount of drive space available ,
MAX_GROWTH = displays maxium size the file can grow if it is set. 0 if it can grow until it runs out of space,
File_Growth = how much the file will grow by: displayed in either MB or %

the calculations where simply taken directly from the table. the sys... tables store values that represent the number of pages.
knowing this, it is easy to take that value multiply it by 8 (8k in every page) and then divide that by 1024 (amt of bytes in MB)
** ALL VALUES RETURNED ARE IN MB

BOL has how to convert the status column to more meaningful information:
--------------------------------------------------------------------
0x1 = Default device.
0x2 = Disk file.
0x40 = Log device.
0x80 = File has been written to since last backup.
0x4000 = Device created implicitly by the CREATE DATABASE statement.
0x8000 = Device created during database creation.
0x100000 = Growth is in percentage, not pages. 

--Chad Tucker 2/20/2006

*************************************
*/



alter procedure SpaceInfo @dbname varchar(100)
as
			-- declare table variable
DECLARE @SQLCode varchar(5000)
--SET @DBName = 'Amps'

SET @SQLCode =
			'USE [' + @DBName +']		
			DECLARE @SpaceSeeker TABLE ([Group_Name] varchar(50), [File_Name] varchar(100), [Drive] char(3), [Allocated_Space] decimal(15,4), 
					[Actual_Size] decimal(15,4), [Space_Available] decimal(15,4), [Total_FREE_Drive_Space] decimal(15,2), [Max_Growth] char(16),[File_Growth] varchar(15))
			-- insert calculations into table
			INSERT INTO @SpaceSeeker ([Group_Name] , [File_Name] ,[Drive], [Allocated_Space] , [Actual_Size], [Total_FREE_Drive_Space] , [Max_Growth] ,[File_Growth])
			SELECT
			Group_Name = sfg.groupname,
			[File_Name] = sf.[name],
			Drive = LEFT([filename], 3),
			Allocated_space = sf.[size] * 8 /1024,
			Actual_Size  = FILEPROPERTY(sf.[name], ''SPACEUSED'') * 8 /1024,
			Total_Disk_Space = 0.0,
			MAX_GROWTH = CASE 
						WHEN sf.[maxsize] = 0 THEN ''NO GROWTH SET''
						WHEN sf.[maxsize] = -1 THEN ''NO MAX LIMIT SET''
 						ELSE STR((sf.[maxsize] * 8) /1024)+ '' MB''
						  END,
			File_Growth=CASE 	
					WHEN (sf.status&0x100000) > 0 THEN STR(sf.growth)+'' %''
					ELSE STR((sf.growth * 8) /1024)+'' MB'' 
						  END
			-- join sysfilegroup just to get filegroup name
			FROM sysfiles sf (NOLOCK) LEFT OUTER JOIN sysfilegroups sfg (NOLOCK)
			ON sf.groupid = sfg.groupid
			ORDER BY 1
			-- make a second pass to update table 
			-- this will store the actual space available in the file
			UPDATE @SpaceSeeker
			SET Space_Available = ((Allocated_Space) - (Actual_Size ))
			--*****************************************************
			-- this is to update total drive space. it uses xp_fixeddrives and temp table
			-- reason for temp table is that exec is not support when doing an insert into a table variable
			-- this statement is a little different in the job
			Create TABLE  #DRIVESPACE  ([Drive] char(1), [MB_Free] varchar(15))
			INSERT #DriveSpace
			exec (''master..xp_fixeddrives'') 
			update @SpaceSeeker
			SET Total_FREE_Drive_Space = (MB_FREE)/1024
			from #DriveSpace a JOIN @SpaceSeeker b ON a.Drive =LEFT(b.Drive,1)
			drop table #DriveSpace
			--*******************************************************
			IF NOT EXISTS (SELECT 1 FROM ServerControl.DBO.sysobjects WHERE NAME = ''DBA_DBSize_Growth'' AND type = ''U'')
			BEGIN
						CREATE TABLE SERVERCONTROL.DBO.DBA_DBSize_Growth
							(
									[Database_Name] varchar(50), [Group_Name] varchar(50), [File_Name] varchar(50), [Drive] char(3), 
									[Allocated_Space] decimal(15,4), [Actual_Size] decimal(15,4), [Space_Available] decimal(15,4), 
									[Total_FREE_Drive_Space_GB] decimal(15,4), [Max_Growth] char(16),[File_Growth] varchar(15), 
									[Size_Date] smalldatetime DEFAULT (GETDATE())
							) 
			END
			--INSERT INTO SERVERCONTROL
			INSERT INTO SERVERCONTROL.DBO.DBA_DBSize_Growth
			SELECT ''' + @DBName + ''',  
			Group_Name ,
			File_Name  ,
			Drive,
			Total_Allocated_Space_MB = Allocated_Space ,
			Actual_File_Size_MB = Actual_Size,
			Space_Available_MB = Space_Available ,
			Total_FREE_Drive_Space,
			MAX_GROWTH_MB = MAX_GROWTH ,
			File_Growth,
			Size_Date= GETDATE()
			FROM @SpaceSeeker
		GO'

--print @SQLCode
EXEC (@SQLCode)
--***************************************************************************************