All about space Details

--- To Get Drive Space
--use master
--go
--CREATE PROCEDURE sp_diskspace
--AS
--SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576
CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)
INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive
OPEN dcur
FETCH NEXT FROM dcur INTO @drive
WHILE @@FETCH_STATUS=0
BEGIN
EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive
UPDATE #drives
SET TotalSize=@TotalSize/@MB
WHERE drive=@drive
FETCH NEXT FROM dcur INTO @drive
END
CLOSE dcur
DEALLOCATE dcur
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso
SELECT
(SELECT SERVERPROPERTY ('MachineName')) As ServerName,
drive,
TotalSize as 'Total(MB)',
(TotalSize - FreeSpace) As 'Utilized(MB)',
--Utilized= TotalSize - FreeSpace ,
FreeSpace as 'Free(MB)',
FreeSpace/1024 as 'Free(GB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)',
CONVERT(VARCHAR(12),getdate(),113) As 'As ON'
FROM #drives
ORDER BY drive
DROP TABLE #drives
RETURN
--go

---

---To Get MDF Size
use master;
go
select
(SELECT SERVERPROPERTY ('MachineName')) As ServerName,
--db.[dbid] as 'DB ID'
db.[name] as 'Database Name'
,af.[name] as 'Logical Name'
,af.[filename] as 'Physical Name'
--,af.[size] as 'File Size (in 8-kilobyte (KB) pages)'
,(((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) as 'File Size (MB)'
--,((((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) /1024) as 'File Size (GB)'
from sys.sysdatabases db
inner join sys.sysaltfiles af
on db.dbid = af.dbid
where [fileid] in (1)
order by db.[name]

---

---To Get NDF Size
use master;
go
select
--db.[dbid] as 'DB ID'
db.[name] as 'Database Name'
,af.[name] as 'Logical Name'
,af.[filename] as 'Physical Name'
--,af.[size] as 'File Size (in 8-kilobyte (KB) pages)'
,(((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) as 'File Size (MB)'
--,((((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) /1024) as 'File Size (GB)'
from sys.sysdatabases db
inner join sys.sysaltfiles af
on db.dbid = af.dbid
where [fileid] not in (1,2)
order by db.[name]

---

---To Get LDF Size
use master;
go
select
--db.[dbid] as 'DB ID',
db.[name] as 'Database Name'
,af.[name] as 'Logical Name'
,af.[filename] as 'Physical Name'
--,af.[size] as 'File Size (in 8-kilobyte (KB) pages)'
,(((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) as 'File Size (MB)'
--,((((CAST(af.[size] as DECIMAL(18,4)) * 8192) /1024) /1024) /1024) as 'File Size (GB)'
from sys.sysdatabases db
inner join sys.sysaltfiles af
on db.dbid = af.dbid
where [fileid] in (2)
order by db.[name]