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
Advertisements