Drive Space Usage/Free Monitoring Script

BEGIN

SET NOCOUNT ON

IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DBFileInfo'))
BEGIN
DROP TABLE #DBFileInfo
END


IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#LogSizeStats'))
BEGIN
DROP TABLE #LogSizeStats
END

IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DataFileStats'))
BEGIN
DROP TABLE #DataFileStats
END

IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#FixedDrives'))
BEGIN
DROP TABLE #FixedDrives
END

CREATE TABLE #FixedDrives 
(DriveLetterVARCHAR(10), 
MB_FreeDEC(20,2))


CREATE TABLE #DataFileStats 
(DBNameVARCHAR(255), 
DBIdINT,
FileId TINYINT, 
[FileGroup] TINYINT, 
TotalExtents DEC(20,2),
UsedExtents DEC(20,2),
[Name] VARCHAR(255), 
[FileName] VARCHAR(400))


CREATE TABLE #LogSizeStats 
(DBNameVARCHAR(255) NOT NULL PRIMARY KEY CLUSTERED,
DBIdINT,
LogFileREAL, 
LogFileUsedREAL,
StatusBIT) 

CREATE TABLE #DBFileInfo
([ServerName]VARCHAR(255),
[DBName]VARCHAR(65),
[LogicalFileName]VARCHAR(400),
[UsageType]VARCHAR (30),
[Size_MB]DEC(20,2), 
[SpaceUsed_MB]DEC(20,2),
[MaxSize_MB]DEC(20,2),
[NextAllocation_MB]DEC(20,2), 
[GrowthType]VARCHAR(65),
[FileId]SMALLINT,
[GroupId]SMALLINT,
[PhysicalFileName]VARCHAR(400),
[DateChecked]DATETIME) 


DECLARE@SQLString VARCHAR(3000)
DECLARE@MinIdINT
DECLARE @MaxIdINT
DECLARE @DBNameVARCHAR(255)

DECLARE@tblDBNameTABLE
(RowIdINT IDENTITY(1,1),
DBNameVARCHAR(255),
DBIdINT)

INSERTINTO @tblDBName (DBName,DBId)
SELECT[Name],DBId FROM Master..sysdatabases WHERE (Status & 512) = 0 /*NOT IN (536,528,540,2584,1536,512,4194841)*/ ORDER BY [Name]


INSERT INTO #LogSizeStats (DBName,LogFile,LogFileUsed,Status)
EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS')

UPDATE#LogSizeStats 
SET DBId = DB_ID(DBName)

INSERTINTO #FixedDrives EXEC Master..XP_FixedDrives


SELECT@MinId = MIN(RowId),
@MaxId = MAX(RowId)
FROM@tblDBName

WHILE (@MinId <= @MaxId)
BEGIN
SELECT@DBName = [DBName]
FROM@tblDBName
WHERERowId = @MinId

SELECT@SQLString=
'SELECTServerName = @@SERVERNAME,'+
'DBName = '''+@DBName+''','+
'LogicalFileName= [name],'+
'UsageType= CASE WHEN (64&[status])=64 THEN ''Log'' ELSE ''Data'' END,'+
' Size_MB= [size]*8/1024.00,'+
' SpaceUsed_MB= NULL,'+
'       MaxSize_MB= CASE [maxsize] WHEN -1 THEN -1 WHEN 0 THEN [size]*8/1024.00 ELSE maxsize*8/1024.00 END,'+
'       NextExtent_MB= CASE WHEN (1048576&[status])=1048576 THEN ([growth]/100.00)*([size]*8/1024.00) WHEN [growth]=0 THEN 0 ELSE [growth]*8/1024.00 END,'+
'GrowthType= CASE WHEN (1048576&[status])=1048576 THEN ''%'' ELSE ''Pages'' END,'+
'       FileId= [fileid],'+
'    GroupId= [groupid],'+
'      PhysicalFileName= [filename],'+
'CurTimeStamp= GETDATE()'+
'FROM '+@DBName+'..sysfiles' 

PRINT @SQLString
INSERT INTO #DBFileInfo
EXEC (@SQLString)

UPDATE#DBFileInfo
SETSpaceUsed_MB = (SELECT LogFileUsed FROM #LogSizeStats WHERE DBName = @DBName)
WHEREUsageType = 'Log'
ANDDBName= @DBName 


SELECT@SQLString = 'USE ' + @DBName + ' DBCC SHOWFILESTATS WITH NO_INFOMSGS'

INSERT #DataFileStats (FileId,[FileGroup],TotalExtents,UsedExtents,[Name],[FileName])
EXECUTE(@SQLString)

UPDATE #DBFileInfo
SET [SpaceUsed_MB] = S.[UsedExtents]*64/1024.00
FROM #DBFileInfo AS F
INNER JOIN #DataFileStats AS S
ON F.[FileId]  = S.[FileId]
AND F.[GroupId] = S.[FileGroup]
AND F.[DBName]  = @DBName

TRUNCATE TABLE #DataFileStats


SELECT @MinId = @MInId + 1
END

SELECT[ServerName],
[DBName],
[LogicalFileName],
[UsageType] AS SegmentName,
B.MB_Free AS FreeSpaceInDrive,
[Size_MB],
[SpaceUsed_MB],
[MaxSize_MB],
[NextAllocation_MB],
CASE MaxSize_MB WHEN -1 THEN CAST(CAST(([NextAllocation_MB]/[Size_MB])*100 AS INT) AS VARCHAR(10))+' %' ELSE 'Pages' END AS  [GrowthType],
[FileId],
[GroupId],
[PhysicalFileName],
[DateChecked]
FROM #DBFileInfo AS A
LEFT JOIN #FixedDrives AS B
ON SUBSTRING(A.PhysicalFileName,1,1) = B.DriveLetter
ORDER BY DBName,GroupId,FileId

IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DBFileInfo'))
BEGIN
DROP TABLE #DBFileInfo
END


IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#LogSizeStats'))
BEGIN
DROP TABLE #LogSizeStats
END

IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#DataFileStats'))
BEGIN
DROP TABLE #DataFileStats
END

IF EXISTS (SELECT 1 FROM Tempdb..Sysobjects WHERE [Id] = OBJECT_ID('Tempdb..#FixedDrives'))
BEGIN
DROP TABLE #FixedDrives
END

END
GO