Watch your file size of databases in SQL Server 2008/2005

Keeping watch on file size of databases is one of the important task for DBA. I am using one small yet very useful script since very long time for this task which I wanted to share with all of my blog reader. I don’t exactly remember whether I have developed it or found it from internet somewhere. I tried to find this script by GOOGLE but didn’t find it anywhere so I forced to believe that I might have developed it. If anybody knows where does it located than do let me know so that I can give due credit.


      [maxsize]=(case maxsize when 1 then N’Unlimited’ else CONVERT(nvarchar(15),convert(bigint,maxsize)*8)+N’ KB’ END),
      [growth]=(case status & 0x100000 when 0x100000 then convert(nvarchar(15),growth)+N’%’ else convert(nvarchar(15),convert(bigint,growth)*8)+ N’ KB’ end),
      [usage]=(case status & 0x40 when 0x40 then ‘log only’ else ‘data only’ end)
 from  sysaltfiles
order by dbid

Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of