How to check the database size in SQL Server?

Hi,
 
Today, I got know the size of the database. But unfortunately I could not get the correct database size on the file system until I take the back up.
 
Select the database, right click and click on properties.

 

 
In the above image, the size shown 460.81 MB is sum of Data and Log files. It doesn’t mean that real size of the database would be the same.
 
I have taken the back up of the database and on the file system the size mentioned was 262 MB.

 
Space Available 90.03 MB is the space that is still available to fill up. Once this space is utilized then SQL Server will allocates new Data space and Log space as per the growth rate set.
 
Use the following query to see the size of Log files and data files individually.
 

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) = 'DB_NAME'


Output

351 + 109 = 460 MB.
 
Hope this helps.
 

Cheers

Gopinath 

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s