RSS

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

17 May

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'

DatabaseSize_1

 

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 SIZEfrom 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 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s