Monday, December 10, 2012

How to Track Database Files Growth In SQL Server?

Last weekend I had visited one of my friends organization to configure SQL Server best practices on newly installed servers. I had configured all servers as per the best practices and keeping in mind the OLTP application functionality. One of the senior most employee from this organization has asked me is there anyway to track the growth of the disk drives and when we have request to client to add additional space to the existing drives. I have given below solution to them and asked them to automate daily/weekly insert the output into the table from there you can generate report and you can analyze forecast db growth.

Here is the solution:

Select @@ServerName As ServerName,
dbid, 
A.name, 
A.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=sqlfix.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=sqlfix.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 = 'A' 
and b.database_name = A.name) as LastFullBackup, 
(select max(backup_finish_date) from msdb.dbo.backupset B 
where type = 'C' 
and b.database_name = A.name) as LastLogBackup, 
A.recovery_model_desc, 
A.create_date
from master.sys.sysaltfiles sqlfix 
join master.sys.databases A on sqlfix.dbid=A.database_id 
where groupid>0 and dbid not in (1,3,4) 
group by dbid, A.name, A.compatibility_level,
A.recovery_model_desc, A.create_date 
order by db_size_in_mb desc
 


Track SQL Database Growth. Tracking and Reporting Database Growth.How to track database file growth over a period of time. Database Growth Tracker. How to track database growth across multiple SQL Server instances. Track the OLTP Database Daily growth size. Using v$datafile to track database growth. SQL Script to track database growth date and time. Track Database file growth. Tracking Oracle database growth. microsoft sql server 2000. microsoft sql server 2005. microsoft sql server 2008 R2. microsoft sql server 2012. Database growth Monitoring on diff servers. How to Track SQL Server Database Growth with Foglight . Formula To Calculate Future Database Growth. Stored procedure to track database file growth. tracking dbgrowth using below query Transact SQL.

0 comments:

Post a Comment