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!!!!
Reference: Ritesh Shah
Sharing IT tips at “Quick Learn“
Note: Microsoft Books online is a default reference of all articles