sqlorcl

Just another WordPress.com site

Track Database file growth

It is important to know the database files size and free space available to each of the database files. Server monitoring tool may send alert messages by email when server drives free space is exceed to threshold limit. If the database files are set auto growth (most of the production servers set this option) then whenever data file need to grow it will grow based on the option selected in Auto growth/Max Size window. But sometimes we may not have enough free space in the data drive so the operating system will not complete the SQL server data file growth request. The following script will give us details idea about each database data files auto growth setting and well as how much free space available in the data file.

if exists ( select * from tempdb.dbo.sysobjects o
where o.xtype in (‘U’) and o.id = object_id( N’tempdb..#fileInfo’ ))
drop table #fileInfo
go

set nocount on
go
create table #fileInfo
(
[ID] int not null identity (1, 1) primary key,
[databaseID] smallint not null,
[fileID] smallint not null ,
[fileSize] int not null ,
[fileUsed] int not null ,
[fileUnused] int not null
)
go

declare @sql nvarchar(4000)
set @sql =
‘use [‘+’?’+’] ;
if db_name() N”?” goto Error_Exit

insert into #fileInfo
(
[databaseID],
[fileID],
[fileSize],
[fileUsed],
[fileUnused]
)

Select
DB_ID(),
aa.fileid,
— All sizes are calculated in MB
convert(int,round((aa.size*1.000)/128.000,0)),
convert(int,round(fileproperty(aa.name,”SpaceUsed”)/128.000,0)),
convert(int,round((aa.size-fileproperty(aa.name,”SpaceUsed”))/128.000,0))
from
dbo.sysfiles aa
left join
dbo.sysfilegroups bb
on ( aa.groupid = bb.groupid )

Error_Exit:

exec sp_msforeachdb @sql

select dbid
,d.name
,d.compatibility_level
,d.recovery_model_desc
,case when saf.status & 0x100000 = 0 then convert(varchar,ceiling((saf.growth * 8192)/(1024.0*1024.0))) + ‘ MB’
else convert (varchar, saf.growth) + ‘ Percent’
end as Growth
,convert(decimal(18,2),(sum(size)*8)/1024.0) as db_size_in_mb
,convert(decimal(18,2),db_info.fileUsed) as file_used_mb
,convert(decimal(18,2),db_info.fileUnused) as file_unused_mb
,(select convert(decimal(18,2),(size*8)/1024.0)
from sys.sysaltfiles where dbid=saf.dbid and groupid=0) as log_size_in_mb
from sys.sysaltfiles saf
join sys.databases d on saf.dbid=d.database_id
join #fileInfo db_info
on saf.dbid = db_info.databaseID and saf.fileid = db_info.fileID
where groupid>0
group by dbid,d.name,d.compatibility_level,Growth,saf.status,
db_info.fileUsed,db_info.fileUnused,d.recovery_model_desc
order by name

The output of this query will look like this.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: