1. To check the available disk space, you may use the undocumented system extended stored procedure xp_fixeddrives, which can return the available free space (MB) for each fixed drive You can run it as follows.:
Exec Master.dbo.xp_fixeddrives
2. As for the database data file and transaction log space allocation and utilization, you may use the Following Query to query THIS:
1) You May Check The File Size (in MB) by Query The Sysfiles Table As Follows:
Select Name, Convert (FLOAT, SIZE) * (8192.0 / 1024.0) / 1024. from dbo.sysfiles
2) To check the utilization, you may use sp_spaceused:
EXEC SP_SPACEUSED
.
To Check the log file's utilization, you may use:
DBCC SQLPERF (Logspace)
For More Information REGARDING THE SYSFILES TABLE, DBCC SQLPERF (LOGSPACE) and sp_spaceused, please refer to SQL Server Books Online.
In fact, you can view the similar information in SQL Enterprise Manager: in Enterprise Manager, right click the database, click View -> Taskpad, then you can see the data and log files size and utilization at the right
