Finding out Total Disk Space in TSQL

Question

Finding out Total Disk Space in TSQL

Viewing 5 posts - 31 through 34 (of 34 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Steve Jones - SSC Editor (1/15/2015)
JimAtWork (2/3/2014)
I have the same challenge and I have to query thousands of servers many of which do not allow the use of OA_ and also do not allow the use of Powershell for security reasons. Any work-arounds for that? TIA
Not to hijack, but Powershell runs all over the place in MS products, so it can't be stopped. Is this just for user scripts?

OR... reduce the number of servers. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RGP SSCarpal Tunnel Points: 4381 More actions · Answer 2

for sql 2008 onwards, free space on drive + percentage

SELECT distinct(volume_mount_point),

total_bytes/1048576 as Size_in_MB,

available_bytes/1048576 as Free_in_MB,

(select ((available_bytes/1048576* 1.0)/(total_bytes/1048576* 1.0) *100)) as FreePercentage

FROM sys.master_files AS f CROSS APPLY

sys.dm_os_volume_stats(f.database_id, f.file_id)

group by volume_mount_point, total_bytes/1048576,

available_bytes/1048576 order by 1

tung858 SSC Veteran Points: 241 More actions · Answer 3

the easiest method is to use...

"select...from sys.master_files mf cross apply sys.dm_os_volume_stats (mf.database_id, mf_file_id) vs"

then CREATE a read-only DUMMY DATABASE that has data files across all drives. Even the local C drive if you want disk stats on all.

Tung Dang
Azure and SQL Server DBA Contractor / Consultant
DataZip

Michael L John One Orange Chip Points: 27055 More actions · Answer 4

tung858 - Friday, February 15, 2019 11:47 AM
the easiest method is to use...
"select...from sys.master_files mf cross apply sys.dm_os_volume_stats (mf.database_id, mf_file_id) vs"
then CREATE a read-only DUMMY DATABASE that has data files across all drives. Even the local C drive if you want disk stats on all.

Cool. How will this work across multiple servers? Is the size column in bytes?

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

tung858 SSC Veteran Points: 241 More actions · Answer 5

Try this for SQL Server 2008 R2 and above

The sizes will convert to mb, gb, or tb depending on how large the drive is.

The query only works with one server. However if you want to see all drives on a server, just create a DBA or dummy database with data or log files in each drive.

select distinct [driveletter] = a2.volume_mount_point
, [drivesize] = case when a2.total_bytes/1024/1024/1024/1024 >= 1 then convert( varchar, convert(decimal(8,1),a2.total_bytes/1024.0/1024/1024/1024)) + ' TB' 
when a2.total_bytes/1024/1024/1024 >= 1 then convert( varchar, convert(decimal(8,1),a2.total_bytes/1024.0/1024/1024)) + ' GB'
else cast(a2.total_bytes/1048576 as varchar) + ' MB' end
, [spaceused%] = convert(decimal(4,1),(a2.total_bytes - a2.available_bytes) * 100.0 / a2.total_bytes)
from sys.master_files a1
cross apply sys.dm_os_volume_stats (a1.database_id, a1.file_id) a2
order by a2.volume_mount_point

This reply was modified 4 years, 2 months ago by tung858.
This reply was modified 4 years, 2 months ago by tung858.
This reply was modified 4 years, 2 months ago by tung858.
This reply was modified 4 years, 2 months ago by tung858.

Tung Dang
Azure and SQL Server DBA Contractor / Consultant
DataZip