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)
--***************************************************************************************