December 6, 2012 at 1:18 pm
As a DBA I often need to watch SQL server disk space. I got alerts then I request more from network engineer. Sometimes the manager ask me to give a report of database growth like the increase rate by month of last 6 months.
Like the drive we have
E:\MSSQL\Data
F:\MSSQL\Log
G:\DBBackup
I know I can do something like to query backupset table to find out backup file growth.
and can query sys.masterfiles and then save into a table , collect by each month.
I don't have a script or process of doing this yet,
But would like to know from other DBA, what is your method of doing this?
Basically I want to give them a history of size growth in above 3 directories?
thanks
December 6, 2012 at 3:36 pm
I like this one, run it once per week or month and log to a table. Can even go dim/fact tables and load into a cube, then create some nice graphed reports off in excel or ssrs
Regards,
Chris
mssqlconsulting.com
select @@ServerName As SQLServerName, dbid, d.name, d.compatibility_level, convert(decimal(18,2), (sum(size)*8)/1024.0) as db_size_in_mb
, (select convert(decimal(18,2), (Sum(size)*8)/1024.0) from sys.sysaltfiles
where dbid=saf.dbid and groupid=0
group by groupid) as log_size_in_mb
, Round(((select convert(decimal(18,2), (Sum(size)*8)/1024.0) from sys.sysaltfiles
where dbid=saf.dbid and groupid=0 group by groupid)
/ convert(decimal(18,2),(sum(size)*8)/1024.0))* 100,2) as log_size_percent
, (select max(backup_finish_date) from msdb.dbo.backupset b
where type = 'D'
and b.database_name = d.name) as LastFullBackup
, (select max(backup_finish_date) from msdb.dbo.backupset b
where type = 'L'
and b.database_name = d.name) as LastLogBackup
, d.recovery_model_desc
, d.create_date
from master.sys.sysaltfiles saf
join master.sys.databases d on saf.dbid=d.database_id
where groupid>0 and dbid not in (1,3,4)
group by dbid, d.name, d.compatibility_level, d.recovery_model_desc, d.create_date
order by db_size_in_mb desc
Chris Becker bcsdata.net
December 6, 2012 at 3:57 pm
Thanks, this is good for tracking database size change. I will give it a try.
Also how about the way to check above 3 directories growth?
December 6, 2012 at 4:31 pm
I don't understand, datafiles grow in your dbData directory, log files grow in your dbLog dir, and backups grow in your dbBak dir. Additionally use the backup_size in backupset table for bak dir. The script I gave tracks growth of dir contents.
Chris Becker bcsdata.net
December 6, 2012 at 4:42 pm
Those help.
I didn't see backup size in this query. Do you mean create another query using backupSet to achieve that?
Additionally,
I wonder if a way to check directly the folder or a drive space growth not in a database standpoint, but checking from NTFS system and save that in a table?
Thanks
December 6, 2012 at 6:40 pm
You could use wmi for that, ssis has a wmi transform
Chris Becker bcsdata.net
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply