DMV Stacks#1(How to check Database Size , Log Size , Free Size)

Hi dears today i will write small DMV but the usage of it is very high needful and important today our DMV is How to check the Database size , Log Size , Free Size in SQL Server i will not Speak more let my DMV Complete what i need to Say :

at the first i will Use in my Demo Database AdventureWorkyou can download it from here SQL Server Database Product Simple

1- Check Database Size for one DB :

by this DMV you can find the Database Size by MB or GB for one DB on your Database Server and you can know the Physical File located for this DB

SELECT DB_NAME(database_id) AS DatabaseName,Name AS Logical_Name,Physical_Name, (size*8)/1024 SizeMB FROM sys.master_files WHERE DB_NAME(database_id) = 'AdventureWorks'

2- Database Size For All Databases on your DB Server :

here we can get all database size for all DB Server Each database separately

SELECT d.NAME,ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs ,(SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs

FROM sys.master_files mf INNER JOIN sys.databases d

ON d.database_id = mf.database_id GROUP BY d.NAME

Order by (SUM(mf.size) * 8 / 1024) DESC

IF you need to know the SUM of the Database size it’s very simple the same previous DMV with CTE (Conman table Expression Technology )

Note : i used here in this DMV Concat Function this new Function in SQL Server 2012 by it we do Concatenation between integer and String in the previous SQL Server version you should Convert the integer to String at first now it very easy

With CTE AS (

SELECT d.NAME,ROUND(SUM(mf.size) * 8 / 1024, 0) Size_MBs,

(SUM(mf.size) * 8 / 1024) / 1024 AS Size_GBs

FROM sys.master_files mf INNER JOIN sys.databases d

ON d.database_id = mf.database_id

GROUP BY d.NAME)

Select Concat (SUM(Size_GBs),' GB') [DB Server SIZE] from CTE

3- Check Free Size for one Database :

sometimes we need free size at this time we should know How many GB can we free it from our Database Server

SELECT DB_NAME() AS DbName, name AS FileName, size/128.0 AS CurrentSizeMB,

 size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 ASFreeSpaceMB

FROM sys.database_files;

For all Databases and the SUM free Size :

one single DMV to know all Databases Free size and the SUM of this Frees size using Temp Table

</pre>

Create Table #LogSize

(

Database_name Nvarchar(200),

[FILENAME] Nvarchar(200),

 CurrentSizeMB BIGINT,

 FreeSpaceMB BIGINT

)

insert into #LogSize

exec sp_msforeachdb

'use [?];

SELECT DB_NAME() AS DbName,

name AS FileName,

size/128.0 AS CurrentSizeMB,

size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB

FROM sys.database_files;

'

Select *,FreeSpaceMB/1024 AS FreeSpaceGB from #LogSize

ORDER BY FreeSpaceMB Desc

SELECT SUM(FreeSpaceMB/1024) TOTAL_Free_SIZE_GB from #LogSize

drop Table #LogSize

<pre>

4- Check Log Size for all Database or one Database

here i am using temp table to save in it all my Database log size inforamtion and then i can Select what i need based on my requirement

</pre>

Create Table #LogSize

(

Database_name Nvarchar(200),

Log_File_Size_MB Int ,

Log_File_Size_GB INT

)

insert into #LogSize

exec sp_msforeachdb

'use [?];

 select DB_NAME() AS DbName,

sum(size)/128.0 AS Log_File_Size_MB,

(sum(size)/128.0)/1024 AS Log_File_Size_GB

from sys.database_files where type=1

group by type

'

Select * from #LogSize where Log_File_Size_MB > 100 order by Log_File_Size_MBdesc

drop Table #LogSize

<pre>

Follow Me
Mostafa Elmasry

Published by Mustafa EL-Masry

I am Microsoft database consultant working as a Database administrator for more than +10 Years I have very good knowledge about Database Migration, Consolidation, Performance Tuning, Automation Using T-SQL, and PowerShell and so many other tasks I do it in multiple customers here in KSA and as of now, I am working in Bank Albilad managing the core banking system that is hosted in SQL Server Database 8 TB. Also, I am Microsoft certified 2008 and 2016 in SQL Server (2x MCTS, 2x MCTIP, MCSA, MCSE) and I am Microsoft Certified Trainer (MCT) also I am azure Certified (AZ-900, AZ-103) also I was awarded by Microsoft Azure Heroes 3 times as (Azure Content hero, Azure Community hero and Azure Mentor) For more information check my page https://mostafaelmasry.com/about-me/ View all posts by Mustafa EL-Masry