Database Growth Track

SQL Server Administration
1

Hi,
I am trying to find it out to get the last 6 month Database Growth using Backup size but having issue as we have job purging the Backup History table.

Whenever i run it i am just getting current month backup size.
We are also not storing Database size but i am planning to run this one moving forward but meanwhile i need to get the report for previous few months so i can have capacity planning idea too?
Is it any other way we can get it previous few month Database growth?
Is it any other idea which can help to achieve it?
Not sure Disk space or Datafile size...???

Thanks in advance for your help!

2

Did you check the standard reports available in SSMS related to disk usage?

3

Thanks Ahmed.
That's good information but it won't help to track growth track, right?
Even though if i can get by Server level then also it will be fine.

4

How far back do your actual backup files .bak and log files go back to and do you have access to them

5

Thanks Yosiasz.
From MSDS - Backup history, I can go only last 2-3 weeks but not sure for backup files as out daily full backup and Tlog backup we are storing on different device so need to check as i don't have access directly to device.

6

If your backups are compressed - it will not be easy to identify the growth rate from the size of those files. If you have a backup of the MSDB database you could restore that as another name - and pull the information from there.

You would have to restore at least one per month to trend the growth - if you have those backups available and can restore them. Just make sure they are restored to a different name and not overwriting the existing current database.

If you cannot do that - then you are pretty much out of luck. At least now you can set it up so you can track the data and trend the growth going forward - which is really the most important part of this exercise.

1 Like
7

Would it be to possible to extrapolate/guesstimate the size even if it is compressed?

If 50gb is conpressed to xyz and then a sampling of a few of them to average the estimated size

8

It might be possible - if you have data available showing actual space used during the same time frame. The problem is trying to correlate an increase in the backup size with data space used - which all depends on how compressible the data is compared to space used.

Probably would be better to capture data space usage, index space usage, unallocated, etc... every day instead of trying to determine growth from backups.

9

Thanks Jeff. Current backup is probably they are storing other places but it will be harder to get without access, if i am able to get it, i might use RESTORE VERIFY also to get the size.
You are right, i need to start to store the Size info in one table so it will be easy to retrieve.
Do you suggest any better script or just regular backup size script to run at the end of month to get the month end backup size?

10

Thanks Yosiaz. Not sure they are backing up with compress or not as using 3rd party vendor software.

11

I would not recommend using backup size to capture database growth. I would capture the data space used, index space used, reserved, unused, etc...

There are plenty of scripts available to grab that data.

12

Thanks Jeff.
I have seen some of the documents that they do using Backup size but i agreed that it won't be exact size to match the Database size. I will google it but feel free if you have something or you can pin point as there are lots of different way and it's confusing, appreciate your help!

13

Here is something that will return multiple columns for a single database:

    Use YourDatabaseHere;
     Go

   With dbSizes (db_size, log_size) 
     As (
 Select sum(convert(bigint, Case When status & 64 = 0 Then size Else 0 End))
      , sum(convert(bigint, Case When status & 64 != 0 Then size Else 0 End))
   From dbo.sysfiles
        )
      , pagesUsed (reserved_pages, used_pages, total_pages) 
     As (
 Select sum(a.total_pages)
      , sum(a.used_pages)
      , sum(Case When it.internal_type In (202, 204) Then 0
                 When a.type != 1 Then a.used_pages
                 When p.index_id < 2 Then a.data_pages
                 Else 0
             End)
   From sys.partitions                   p
  Inner Join sys.allocation_units        a On p.partition_id = a.container_id
   Left Join sys.internal_tables        it On p.object_id = it.object_id
        )
 Select database_size_gb = cast(s.database_size_mb / 1024 As decimal(19,4))
      , s.database_size_mb
      , s.reserved_mb
      , s.data_mb
      , s.log_size_mb
      , s.index_mb
      , s.unused_mb
      , s.unallocated_mb
      , data_reserved_mb = s.reserved_mb + s.unallocated_mb
      , unallocated = cast(s.unallocated_mb * 100.0 / (s.reserved_mb + s.unallocated_mb) As decimal(19,4))
      , data_used = cast(s.data_mb * 100 / (s.reserved_mb + s.unallocated_mb) As decimal(19,4))
      , index_used = cast(s.index_mb * 100 / (s.reserved_mb + s.unallocated_mb) As decimal(19,4))
      , unused = cast(s.unused_mb * 100 / (s.reserved_mb + s.unallocated_mb) As decimal(19,4))
   From pagesUsed               pu
  Cross Apply dbSizes           ds
  Cross Apply (Values (convert(decimal(19,4), (ds.db_size + ds.log_size) * 8192 / 1048576.0)
                     , cast(pu.reserved_pages * 8192 / 1048576.0 As decimal(19,4))
                     , cast(pu.total_pages * 8192 / 1048576.0 As decimal(19,4))
                     , cast(ds.log_size * 8192 / 1048576.0 As decimal(19,4))
                     , cast((pu.used_pages - pu.total_pages) * 8192 / 1048576.0 As decimal(19,4))
                     , cast((pu.reserved_pages - pu.used_pages) * 8192 / 1048576.0 As decimal(19,4))
                     , Case When ds.db_size >= pu.reserved_pages 
                            Then convert(decimal(19,4), (ds.db_size - pu.reserved_pages) * 8192 / 1048576.0)
                            Else 0
                        End)
              ) As s(database_size_mb, reserved_mb, data_mb, log_size_mb, index_mb, unused_mb, unallocated_mb);

If you run the Disk Usage report you will find that these numbers match up to what is shown on that report.

To run this for every database - you need to wrap it in a cursor and use dynamic SQL pass in the database. Something like this...

    Use Works;
     Go

Declare @database sysname
      , @sqlCommand nvarchar(max) = '';

Declare dbList Cursor Local fast_forward
    For
 Select db.name 
   From sys.databases                    db;

 --==== Open and fetch
   Open dbList;
  Fetch Next From dbList Into @database;

  While @@fetch_status = 0
  Begin

    Set @sqlCommand = '
    Use ' + quotename(@database) + ';
  
   With dbSizes (db_size, log_size) 
     As (
 Select sum(convert(bigint, Case When status & 64 = 0 Then size Else 0 End))
      , sum(convert(bigint, Case When status & 64 != 0 Then size Else 0 End))
   From dbo.sysfiles
        )
      , pagesUsed (reserved_pages, used_pages, total_pages) 
     As (
 Select sum(a.total_pages)
      , sum(a.used_pages)
      , sum(Case When it.internal_type In (202, 204) Then 0
                 When a.type != 1 Then a.used_pages
                 When p.index_id < 2 Then a.data_pages
                 Else 0
             End)
   From sys.partitions                   p
  Inner Join sys.allocation_units        a On p.partition_id = a.container_id
   Left Join sys.internal_tables        it On p.object_id = it.object_id
        )
 Select database_id = db_id()
      , database_name = db_name()
      , database_size_gb = cast(s.database_size_mb / 1024 As decimal(19,4))
      , s.database_size_mb
      , s.reserved_mb
      , s.data_mb
      , s.log_size_mb
      , s.index_mb
      , s.unused_mb
      , s.unallocated_mb
      , data_reserved_mb = s.reserved_mb + s.unallocated_mb
      , unallocated = cast(s.unallocated_mb * 100.0 / (s.reserved_mb + s.unallocated_mb) As decimal(19,4))
      , data_used = cast(s.data_mb * 100 / (s.reserved_mb + s.unallocated_mb) As decimal(19,4))
      , index_used = cast(s.index_mb * 100 / (s.reserved_mb + s.unallocated_mb) As decimal(19,4))
      , unused = cast(s.unused_mb * 100 / (s.reserved_mb + s.unallocated_mb) As decimal(19,4))
   From pagesUsed               pu
  Cross Apply dbSizes           ds
  Cross Apply (Values (convert(decimal(19,4), (ds.db_size + ds.log_size) * 8192 / 1048576.0)
                     , cast(pu.reserved_pages * 8192 / 1048576.0 As decimal(19,4))
                     , cast(pu.total_pages * 8192 / 1048576.0 As decimal(19,4))
                     , cast(ds.log_size * 8192 / 1048576.0 As decimal(19,4))
                     , cast((pu.used_pages - pu.total_pages) * 8192 / 1048576.0 As decimal(19,4))
                     , cast((pu.reserved_pages - pu.used_pages) * 8192 / 1048576.0 As decimal(19,4))
                     , Case When ds.db_size >= pu.reserved_pages 
                            Then convert(decimal(19,4), (ds.db_size - pu.reserved_pages) * 8192 / 1048576.0)
                            Else 0
                        End)
              ) As s(database_size_mb, reserved_mb, data_mb, log_size_mb, index_mb, unused_mb, unallocated_mb);
'

 --==== Print/Execute SQL Command
  Print @sqlCommand;
 Execute sp_executeSql @sqlCommand;

  Fetch Next From dbList Into @database;

    End 

  Close dbList;
Deallocate dbList;

Modify this to insert the data into a tracking table by date...

14

Thank you so much Jeff putting together both the script, I will use the cursor script for all the databases so i can run into different server. Script looks really nice and smart way written.
I normally create Stored Proc/Table to keep track with job.
I have question for the actual size, when i have to consider, i have to count sum of all the following?
database_size_mb reserved_mb data_mb log_size_mb index_mb unused_mb unallocated_mb data_reserved_mb unallocated data_used index_used unused

Thanks for your great help!

15

It is all there in the script - review the data returned and how that data is captured and that will tell you what values you need to consider.