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.

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

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

One thought on “Find database file size in SQL Server instance

Comments are closed.