Agent Jobs

How To Visualize Database Growth In SQL Server Using Excel

How to visualize a database growth chart using Excel. Here’s a straight up ‘How To’ write up to get you started. You’ll love this. It’s easy I promise. Notice the following graph. 2 lines RED and BLUE. RED represents the current compression ratio, and because compression hasn’t been configured for the database they represent the same level as the usual backup size which is in BLUE. As you can see; they are parallel through the history of the database until it gets to July 2015 where I enabled compression.

Below is some SQL logic from a former post about trending your database growth using backups (https://mikesdatawork.wordpress.com/2015/07/27/get-database-size-trending-on-all-databases/) This was inspired by the original author Erin Stellato at SQL Skills (http://www.sqlskills.com/blogs/erin/trending-database-growth-from-backups/). Check that out for more info :)

By the way; you can enable backup compression with the following statement.


exec master..sp_configure 'show advanced options' 1 reconfigure;

exec master..sp_configure 'backup compression default', 1 reconfigure;

go

I adjusted the SQL logic so it could be run against all databases on a server ( or group of servers if registered under a single folder in SSMS ). I’ve also included some modifications from ‘Jeffs’ post within the original article. Basically I added an extra 365 days to it. From there you can get a pretty good idea how to modify the logic to your liking and reach back as far as needed without too much hassle.


use master;

set nocount on

declare @get_dbsize_history varchar(max)

declare @first_day_of_year varchar(25)

set @first_day_of_year = (select dateadd(year, datediff(year, 0, getdate()), 0)) -365 --> Go back to the first of the year plus 1 extra year (365 days)

set @get_dbsize_history = ''

select @get_dbsize_history = @get_dbsize_history +

'

select

''database'' = cast(upper([database_name]) as varchar(20))

, ''month'' = convert(varchar(7),[backup_start_date],120)

, ''backup size gb'' = str(avg([backup_size]/1024/1024/1024),5,2)

, ''compressed bu size'' = str(avg([compressed_backup_size]/1024/1024/1024),5,2)

, ''compression ratio'' = str(avg([backup_size]/[compressed_backup_size]),5,2)

from

msdb.dbo.backupset

where

[database_name] = ''' + upper(name) + '''

and [type] = ''d''

and backup_finish_date > ''' + @first_day_of_year + '''

and database_name in (select name from master.sys.databases)

group by

[database_name]

, convert(varchar(7),[backup_start_date],120)

order by

[database_name],convert(varchar(7),[backup_start_date],120);' + char(10) + char(10)

from

sys.databases

where

database_id > 4

exec (@get_dbsize_history)

So here’s what you do. You find a server that has a variety of backup sizes through the history you’re looking at. These make the best examples to illustrate growth history. Copy the results from SSMS (using a single database history) and paste them straight into Excel. Nothing crazy, or difficult. In this example we are using a server called “MyServerName”, and a single database called “MyDatabase” which is basically about 1.5 years history. It might help to insert an extra row and add the Column titles.

Perform the following actions.

1. Highlight 3 columns Month, Backup Size, Compressed

2. Click ‘Insert’.

3. Click ‘Line’ chart.

4. Select ‘3-D Line’ chart.

Change the charting.

1. Click ‘Chart Tools’, and select the ‘Design’ tab.

2. Select ‘Style 42’ below.

Feel free to drag the chart out a bit to make it more visible.

Next we want to change the chart layout slightly. Perform the following actions.

1. Select layout ‘6’.

2. Double click on the inner corner of the chart to get the format properties.

3. Select ‘3-D Rotation’ on the left, and change the [X Rotation] to 30.

4. Click ‘Close’.

Looking at the chart it seems like we are almost done. Lets punch up those lines a bit and add some width. To do so; perform the following actions.

1. Double click the line to bring up the Data Point properties.

2. Click on ‘Series Options’ on the left, and drag the handle all the way to the left towards ‘No Gap’.

Now just change the titles and you’re done. Showing your capacity and storage requirements will be much easier now not just with backups, but with any standing database growth or otherwise. This can also be used to track performance over time from sys.dm_os_performance_stats etc.

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