Database physical location, size, disk used..etc

, 2019-04-30

There are plenty of examples online abut getting information such as size, location etc. using a script, i have read myself quite a few, then i decided to write one

which i would give me what i needed, but it had to be simple. Using ideas from some scripts I found, it wrote this one, in the mind that we get and we give to each other,

this script is besides being helpful to me, written also to others so that they can do exactly what I did, modify it and end up with a script fit to their needs. I am not into the writing of articles, anymore, but if I ever feel i need to write I will, for now I hope this script (very simple) helps you. I have included some comments, because that's the part I mostly worked out myself (formulas), some of the scripts I found were not quite there giving the info as far as numbers, they used approximations etc. One thing you can do is add another column with similar function to give you TerraBytes usage.

The cross join, well you could also use a temporary table etc, is probably better, did not bother much, just plugged it in. It is for getting Disk size etc..

 

 

--
--	get all databases sizes
--	written by: angel rapallo on 04/25/2019
--
--	f.size is the number of 8K pages in the database
--	so there are f.size * 8 * 1024 bytes in the database
--	the rest is pure conversion to mega/gyga/ etc..
--
--	there are 1048576 bytes in a megabyte
--	there are 1073741824 bytes in a gigabyte
--
--	total_bytes-available_bytes = used bytes and / total_bytes gives
--	the percentage used of disk
--
select
	d.name,
	f.name as filetype,
	f.physical_name as physicalfile,
	f.state_desc as onlinestatus,	
	f.size * 8.00 * 1024.00 as bytes,    
	cast((f.size * 8.00 * 1024.00) / 1048576.00 as numeric (18,2)) as megabytes,
	cast((f.size * 8.00 * 1024.00) / 1073741824.00 as numeric(18,2)) as gigabytes,	
	cast(cast(v.total_bytes - v.available_bytes as float) / cast(v.total_bytes as float) * 100 as numeric(18,2)) used_disk_percent
from 
                sys.master_files f
    inner join  sys.databases d on d.database_id = f.database_id	
    cross apply sys.dm_os_volume_stats(f.database_id, f.file_id) v
order by
    d.name

Rate

4 (1)

Share

Share

Rate

4 (1)

Related content

Service Broker Part 1: Service Broker Basics

This article covers SQL Server 2008 Service Broker, an asynchronous messaging framework that is directly integrated within the relational engine of SQL Server. The series will provides you with the basics about implementing Service Broker applications and how you can transparently scale them out to support any required workload.

5 (1)

2019-04-15

2,418 reads