EXEC MASTER..xp_fixeddrives
GO
-- SQL Server 2005 only.
--
-- Author: Damon T. Wilson
-- Creation Date: 13-DEC-2006
--
-- Usage:
-- Display the Database ID, Database Name, Logical File Name,
-- MB Size on Disk, GB Size on Disk and Physical File Name
-- for all databases in this instance.
use master;
go
select
db.[dbid] as 'DB ID'
,db.[name] as 'Database Name'
,af.[name] as 'Logical 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)'
,af.[filename] as 'Physical Name'
from sys.sysdatabases db
inner join sys.sysaltfiles af
on db.dbid = af.dbid
where [fileid] in (1,2)
order by 2;
exec sp_msforeachdb 'use ? exec sp_spaceused'
SELECT SUM(size*8192.0) AS totalsize
FROM sys.master_files;
SELECT
ds.name as filegroupname
, df.name AS 'FileName'
, physical_name AS 'PhysicalName'
, size/128 AS 'TotalSizeinMB'
, size/128.0 - CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS 'AvailableSpaceInMB'
, CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0 AS 'ActualSpaceUsedInMB'
, (CAST(FILEPROPERTY(df.name, 'SpaceUsed') AS int)/128.0)/(size/128)*100. as '%SpaceUsed'
FROM sys.database_files df LEFT OUTER JOIN sys.data_spaces ds
ON df.data_space_id = ds.data_space_id;
----SELECT DISTINCT dovs.logical_volume_name AS LogicalName,
----dovs.volume_mount_point AS Drive,
----CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
----FROM sys.master_files mf
----CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
----ORDER BY FreeSpaceInMB ASC
----GO
----SELECT DISTINCT DB_NAME(dovs.database_id) DBName,
----dovs.logical_volume_name AS LogicalName,
----dovs.volume_mount_point AS Drive,
----CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
----FROM sys.master_files mf
----CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
----ORDER BY FreeSpaceInMB ASC
----GO
----SELECT DISTINCT DB_NAME(dovs.database_id) DBName,
----mf.physical_name PhysicalFileLocation,
----dovs.logical_volume_name AS LogicalName,
----dovs.volume_mount_point AS Drive,
----CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB
----FROM sys.master_files mf
----CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
----ORDER BY FreeSpaceInMB ASC
----GO
Loading...