Adsense

Thursday, January 24, 2013

How fast is my SQL Server database growing?


Do you know how quickly your database is growing?  If not you can get a pretty good idea based on how the size of your full backups have grown over time. 

Run the script below, and then run the output of that script and you will see the growth of your backup day by day.  Putting the output into Excel can help to generate a graph for this.  Overall it’s a quick and dirty way to get some idea of how quick you’re growing.

use master
go
DECLARE @dbname as sysname
DECLARE @select_string as varchar(4000)
DECLARE @for_string as varchar(4000)
DECLARE @init as bit

DECLARE curs CURSOR FOR
SELECT name FROM sysdatabases

OPEN CURS

FETCH NEXT FROM curs INTO @dbname

SELECT      @select_string = 'SELECT DISTINCT backup_date',
      @for_string = 'FOR database_name IN (',
      @init = 0

WHILE @@FETCH_STATUS = 0
BEGIN
      IF @init = 0
      BEGIN
            SET @init = 1

            SELECT      @select_string = @select_string + ', [' + @dbname + '] as ' + '"' + @dbname +'"',
            @for_string = @for_string +  @dbname
      END
      ELSE
      BEGIN
            SELECT      @select_string = @select_string + ', [' + @dbname + '] as ' + '"' + @dbname +'"',
                  @for_string = @for_string + ', ' + @dbname
      END
      FETCH NEXT FROM curs INTO @dbname
END

CLOSE curs
DEALLOCATE curs

PRINT @select_string
PRINT 'from
      (select isnull(backup_size / (1024 * 1024 * 1024), 0) as backup_size,
      database_name,
      cast(cast(backup_finish_date as varchar(12)) as datetime) as backup_date
      from msdb.dbo.backupset
      where type = ''d'') p
pivot
      (
      sum(backup_size)'
PRINT @for_string
PRINT ')) as pvt 
order by backup_date'

4 comments:

  1. You should use [ ] around databasenames in @For_string, otherwise databasenames test-kees will result in syntax errors.

    ReplyDelete
  2. hi, the result is in GB or mb?

    ReplyDelete
  3. Thank you so much for this. I just joined a company and now I have good historic idea of how they have been growing their DB. Awesome!

    ReplyDelete