SQL Space Monitor - Version 1:
Gets Database Data and Log File Space plus Disk Space.
The below T-SQL script will output the Database Data and Log File(s) sizes plus the calculated internal free space per file. It also outputs the Disk Size plus the calculated free space per disk. We recommend you setup monitoring, logging & alerting on these metrics. Simply add this as a stored procedure and schedule inside a SQL Agent Job at least once a day or more frequent if you have a busy database system. A common implementation is to have a Warning Notification Threshold and an Alert Notification Threshold. If the Warning Metric is passed, notify Operations regular email. If the Alert Metric is passed, notify Operations 24/7 email.
T-SQL:
Create Table #TMP
(
[DB] sysname,
[LogicalFileName] nvarchar(100),
[PhysicalFileName] nvarchar(500),
[FileID] int,
[FileSizeMB] decimal (18,2),
[FileFreeSpaceMB] decimal (18,2),
[FileFreeSpace%] decimal (18,2),
[DiskMount] nvarchar(5),
[DiskSizeGB] decimal (18,2),
[DiskFreeSpaceGB] decimal (18,2),
[DiskFreeSpace%] decimal (18,2),
)
Exec sp_msforeachdb '
Use [?];
INSERT INTO #TMP (DB, LogicalFileName, PhysicalFileName, FileID, FileSizeMB, FileFreeSpaceMB, [FileFreeSpace%], [DiskMount], DiskSizeGB, DiskFreeSpaceGB, [DiskFreeSpace%])
SELECT DB_NAME() AS [DatabaseName], f.Name, f.physical_name, f.[file_id] As [FileID],
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),f.size) * 8.0/1024.0,2)) As FileSizeMB,
CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),CONVERT(DECIMAL(18,2),f.size) * 8.0/1024.0,2) -
CONVERT(DECIMAL(18,2),FILEPROPERTY(f.name, ''SpaceUsed'') * 8.0/1024.0)) As FileFreeSpaceMB,
CONVERT(DECIMAL(18,2),CAST(f.size/128.0 - CAST(FILEPROPERTY(f.name, ''SpaceUsed'') AS int)/128.0 AS DECIMAL(18,2)) / CAST((f.size/128.0) AS DECIMAL(18,3)) * 100) As [File Free Space(%)],
volume_mount_point [Disk Mount],
CONVERT(DECIMAL(18,2),total_bytes/1024.00/1024.00/1024.00) As [Disk Size(GB)],
CONVERT(DECIMAL(18,2),available_bytes/1024.00/1024.00/1024.00) AS [Disk Space Available(GB)],
CAST(CAST(available_bytes AS DECIMAL(18,2)) / CAST(total_bytes AS DECIMAL(18,2)) AS DECIMAL(18,3)) * 100 As [Disk Free Space %]
FROM sys.database_files f
INNER JOIN sys.master_files m on f.file_id = m.file_id
LEFT OUTER JOIN sys.data_spaces As ds WITH (NOLOCK) ON f.data_space_id = ds.data_space_id
CROSS APPLY sys.dm_os_volume_stats(database_id, m.file_id)
WHERE m.database_id = db_id()
'
Select * From #TMP order by [DB] asc, [FileID] asc
Drop Table #TMP
|
|
|
|
|
|