Monitoring database growth is a key factor in planning a server’s resource. Use this script and create easy to understand database growth report
Why You Need to Monitor Database Growth
If you are a SQL Database administrator and if you are not having “SQL Server database capacity planning” in your key task list, then you can be sure that databases on your server will fill up your disk space pretty soon. Tracking the size and growth of SQL databases is one of the primary task of Capacity planning. This also ensures that there is enough space on disk for your databases to grow.
DIY via SQL Script and Excel
In this article, we will use SQL and Excel to capture the size of SQL databases along with the pattern. This will enable us to plan for Imminent space requirements and also help us understand the timeline during which there is a heavy volume.
This process is subdivided into four different steps so that you would be able to follow without any trouble.
Step 1: Execute this script on a new query window.
The output will have database names as row headers and Month as Column headers. Values shown in the output are database size in GB.
declare @v_count as integer, @do_count as integer, @v_month as varchar(20),@sql as varchar(5000) create table [t_databases] ( Database_Name varchar(200), January float(3), February float(3), March float(3), April float(3), May float(3), June float(3), July float(3), August float(3), September float(3), October float(3), November float(3), December float(3), ) create table t_databases_gateway ( Database_Name varchar(200), Database_Size float(3) ) set @v_count = (select COUNT(*) from t_databases ) if @v_count = 0 begin insert into t_databases(Database_Name) select name from sysdatabases end if @v_count <> 0 begin --this script captures the size of all databases. You can add a where clause to capture the size of specific database name INSERT t_databases (Database_Name) SELECT DISTINCT Name FROM sysdatabases cr LEFT JOIN t_databases c ON cr.Name = c.Database_Name WHERE c.Database_Name IS NULL end --select * from master.dbo.t_databases --drop table t_databases set @do_count = 1 while (@do_count <=12) begin set @v_month = DATENAME(m, str(@do_count) + '/1/2016') –change the year to 2017 or other year as per your requirement truncate table master.dbo.t_databases_gateway insert into master.dbo.t_databases_gateway select distinct (msdb.dbo.backupset.[database_name]),max(msdb.dbo.backupset.[Backup_Size]/1073741824) from msdb.dbo.backupset inner join master.dbo.t_databases on msdb.dbo.backupset.[database_name] = master.dbo.t_databases.[Database_Name] where datepart(m,msdb.dbo.backupset.[backup_finish_date]) = @do_count and datepart(yyyy,msdb.dbo.backupset.[backup_finish_date] ) = 2015 group by msdb.dbo.backupset.[database_name] set @sql = 'update master.dbo.t_databases set ' + @v_month + ' = (select Database_Size from master.dbo.t_databases_gateway where master.dbo.t_databases.Database_Name = master.dbo.t_databases_gateway.Database_Name)' exec (@sql) set @do_count = @do_count + 1 end select * from t_databases drop table t_databases_gateway drop table t_databases
Step 2: Create a new instance of Excel, and copy the Output from previous step into your new Excel sheet.
Step 3: Now select first two rows on the sheet and insert a 2D line chart.
This will create the trend chart for the selected database. In the Chart, X Axis will denote months and Y Axis will denote the database size in GB.
Step 4: Select the entire data by dragging the ‘plus’ symbol to envelop it.
This will show the trend of all databases on the chart.
If you skip Step 3 and insert a 2D line chart by selecting the entire table, you will still get a chart but the X axis will denote Database names instead of Month,This is not the required output.
The Output table might have some NULL values. This happens because the database backup history might not have any record for the database for that particular month. It also implies that the script refers to the backup size in the backup history table to track the growth trend. If any of your databases is not included in the backup plan, the Output table will still hold the database name but values for all months will be NULL
Fix Oversized Database
If you do not follow the above strategy in the past, and your database is oversized, then it may cause various problems. In such a case, you’d better to find a SQL Server file recovery tool to solve the problem effectively and efficiently.
Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook problem and excel recovery software products. For more information visit www.datanumen.com