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
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
0 comments:
Post a Comment