Find database file size in SQL Server instance

Find database file size in SQL Server instance

I used find database file size so many times a day for different databases in different instance/server. I can go to SSMS and find out the same from database property from GUI but I am script buddy and would like to keep each possible script handy so that I can quickly find out the information what I want.

I have used system view “sysaltfiles” from “master” database which keeps information about each and every database file available in current SQL Server instance. We actually get total number of pages contained in each database file and later on we have to convert number of pages to MB.

Here is the very small yet very useful script to find the database file size in SQL Server.

[sourcecode language=”sql”]SELECT
DB_NAME(dbid) AS DatabaseName
,Name as LogicalFileName
,CASE WHEN GroupID=1 THEN ‘Data’ ELSE ‘Log’ END AS FileType
,FileName as FilePath
,size as TotalPage
,(Size*8192E)/1048576 AS FileSizeInMB
FROM
master..SysAltFiles[/sourcecode]

There are few different way of getting same information, which way do you prefer to use? If you have any other script, do share it!!!!

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

1 thought on “Find database file size in SQL Server instance”

Comments are closed.