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.

 

select

      databasename=DB_NAME(dbid),

      name,

      fileid,

      drive=LEFT(filename,1),

      filename,

      filegroup=FILEGROUP_NAME(groupid),

      [size]=CONVERT(nvarchar(15),convert(bigint,[size])*8)+N'KB',

      [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
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com