SQL Server Instance Database Sizing’s

Views 887

Sometimes when we are administering our SQL Server environments we need to quickly report on this environment indicating for all of our databases (data & tlog) in the Instance or for a subset of databases (data & tlog) in the Instance the :

  • Current Size
  • Max Size
  • Growth Increments
  • Free Space
  • Percentage Used

There is no quick and easy way of achieving this. There are the default standard reports available in SQL Server Management Studio (SSMS) – Right Click on [DBName] | Reports | Standard Reports | Disk Usage which gives you a breakdown of the:

  • Total Space Used
  • Data Files Space Used
  • Transaction Log Space Used
  • Pie Graphs for Data and Tlog usage

Windows-Live-Writer-SQL-Server-Instance-Database-Sizings_44B9-image_2

This report is very nice and quickly and easily shows you the information that you need, but as I stated at the start, you need to get this information for all of your databases in your environment. If you have 20 or more Databases in your instance of SQL Server then this will take a little bit of time. If you have a Windows Server Fail-over Cluster (WSFC) running multiple instance of SQL Server, This task is going to take you a long time to get the information you need.

As such I have created a script which allows you to report on all DB’s in your instance and this can also be run from a central management group to report on all of your instances and the results can then be quickly and easily collated together into Excel to allow you to quickly and easily produce instance based graphs etc for you capacity planning meeting with your manager.

I use the below script:

 

 

Use master
Go

Create Table #tmp_DBNames (
      DBName Varchar(200)
 )

 Create table #tmp_DBSpacing (
     DBName varchar(200),
     FileID Int,
     database_size Decimal(35,2),
      [MaxSize] Decimal(35,2),
      Growth Decimal(35,2)
 )

 Create Table #DBFile_Info (
      FileID int,
      FileGroup int,
      TotalExtents Dec(20,2),
      UsedExtents Dec(20,2),
      DB_FileName Varchar(200),
      FilePathName Varchar(500)
 )

Create Table #DB_Logfile_Info (
     DBName Varchar(200),
     LogSize_Reserved Dec(20,2),
     LogSpace_Used_Percent Dec(20,2),
     Status bit
)

Create Table #DBResults (
      DBName Varchar(200),
      FileID Int,
      DB_Reserved_Space Dec(20,2),
      DB_Used_Space Dec(20,2),
      DB_Free_Space Dec(20,2),
      DB_Free_Percent Dec(20,2),
      DB_Max_Size Dec(35,2),
      DB_Growth Dec(35,2),
      Log_Reserved_Space Dec(20,2),
      Log_Used_Percent Dec(20,2),
      Log_Free_Percent Dec(20,2),
      Log_Max_Size Dec(35,2),
      Log_Growth Dec(35,2)
)

Create Table #DBFileUsage_Report (
      DBName Varchar(200),
      FileType Varchar(100),
      DB_Size_GB dec(35,4),
      Datafile_freespace_GB dec(35,4),
      DB_Pct_Used dec(35,4),
      DB_File_Max_Size_GB dec(35,4),
      DB_File_Allowed_Growth_GB dec(35,4),
      DB_File_Growth_Increment_GB dec(35,4),
      TLog_Size_GB dec(35,4),
      Logfile_Freespace_GB dec(35,4),
      Log_Pct_Used dec(35,4),
      Log_File_Max_Size_GB dec(35,4),
      Log_File_Allowed_Growth_GB dec(35,4),
      Log_File_Growth_Increment_GB dec(35,4)
)


Declare @DBName Varchar(200)
Declare @Cmd VarChar(4000)
Declare @nCmd NVarChar(4000)
Declare @RunCMD nVarchar(500)
Declare @TotalMaxLogSize Dec(20,2)
Declare @TotalLogGrowth Dec(20,2)

Insert Into #tmp_DBNames
  Select Name
  from sysdatabases
  --Where Name not in ('master','msdb','model','tempdb')
  Where Name not in ('master','msdb','model')

  -- only need to run once as it gets all DB's for current instance
  Insert Into #DB_Logfile_Info
  exec ('DBCC SQLPERF(LOGSPACE)')

  Declare DBNames_Cursor Cursor For
      Select DBName
      From #tmp_DBNames


  Open DBNames_Cursor

  Fetch Next From DBNames_Cursor
  into @DBName

  While @@Fetch_Status = 0
  Begin

      Set @Cmd = 'Select    fileid,
                          convert(decimal(35,2),size) / convert( float, (1048576 /(select low from master.dbo.spt_values where number = 1 and type = ''E''))),
                          convert(decimal(35,2),maxsize) / convert( float, (1048576 /(select low from master.dbo.spt_values where number = 1 and type = ''E''))),
                          convert(decimal(35,2),growth) / convert( float, (1048576 /(select low from master.dbo.spt_values where number = 1 and type = ''E'')))
                          From [' + @DBName + '].dbo.sysfiles'

      Set @nCmd = Cast(@Cmd As nVarchar(1000))

      insert Into #tmp_DBSpacing (fileid, database_size,[MaxSize],growth)
      Exec sp_Executesql @nCmd

      Update #tmp_DBSpacing
      Set DBName = @DBName
      Where DBName is Null

      Set @RunCMD = 'Use [' + @DBName + '] DBCC showfilestats'

      Insert into #DBFile_Info
      Exec sp_executesql @RunCMD

      Insert Into #DBResults (DBName, FileID, DB_Reserved_Space, DB_Used_Space, DB_Free_Space, DB_Free_Percent, Log_Reserved_Space, Log_Used_Percent, Log_Free_Percent)
      Select    @DBName,
              FileID,
              (TotalExtents * 64 /1024),
              (UsedExtents * 64 / 1024),
              (TotalExtents * 64 /1024) - (UsedExtents * 64 / 1024),
              (((TotalExtents * 64 /1024) - (UsedExtents * 64 / 1024)) / (TotalExtents * 64 /1024)) * 100,
              LogSize_Reserved,
              LogSpace_Used_Percent,
              100 - LogSpace_Used_Percent
      From #DBFile_Info dfi ,#DB_Logfile_Info dli
      Where dli.DBName = @DBName

      -- Update the newly populated DBResults with the MaxSize values for the data files
      Update #DBResults
      Set DB_Max_Size = #tmp_DBSpacing.[MaxSize],
          DB_Growth = #tmp_DBSpacing.Growth
      From #tmp_DBSpacing Inner Join #DBResults On #DBResults.DBName = #tmp_DBSpacing.DBName
                                                  And #DBResults.FileID = #tmp_DBSpacing.FileID
      Where #DBResults.DBName = @DBName

      -- Determine the maxsize for the tlog file(s)
      Select @TotalMaxLogSize = Sum(#tmp_DBSpacing.[MaxSize]),
              @TotalLogGrowth = Sum(#tmp_DBSpacing.Growth)
      From #tmp_DBSpacing
      Where #tmp_DBSpacing.FileId Not In (Select FileID
                              From #DBResults
                              Where DBName = #tmp_DBSpacing.DBName)
      And #tmp_DBSpacing.DBName = @DBName

      -- Update the max Tlog size based on the above calculation
      Update #DBResults
      Set Log_Max_Size = @TotalMaxLogSize,
          Log_Growth = @TotalLogGrowth
      Where DBName = @DBName

      Truncate table #DBFile_Info

      Fetch Next From DBNames_Cursor
      into @DBName

  End

  Close DBNames_Cursor
  Deallocate DBNames_Cursor


  Insert Into #DBFileUsage_Report
  Select DBName,
          FileType = Case
              When FileId = 1 Then 'Primary File'
              Else 'Secondary File'
          End,
          (DB_Reserved_Space/1024) As 'DB File Size (GB)' ,
          (DB_Free_Space/1024) As 'DB Free Space in File (GB)',
          'DB % Used' = Case
                  When (DB_Reserved_Space/DB_Max_Size) * 100 < 0 Then 0
                  Else (DB_Reserved_Space/DB_Max_Size) * 100
          End,
          (DB_Max_Size/1024) As 'DB Max Size (GB)',
          'Allowed DB Growth' = Case
                  When DB_Max_Size - DB_Reserved_Space < 0 Then 0
                  Else (DB_Max_Size - DB_Reserved_Space)/1024
          End,
          (DB_Growth/1024) As 'Growth Increment (GB)',
          (Log_Reserved_Space/1024) As 'Logfile Size (GB)',
          (((Log_Reserved_Space * Log_Free_Percent) /100)/1024) As 'Logfile Freespace (GB)',
          --((Log_Reserved_Space/Log_Max_Size) * 100) As 'Log % Used'
          'Log % Used' = Case
              When Log_Max_Size > 0 Then ((Log_Reserved_Space/Log_Max_Size) * 100)
              else 0
          End,
          (Log_Max_Size/1024) As 'Log Max Size (GB)',
          --((Log_Max_Size - Log_Reserved_Space)/1024) As 'Allowed Log Growth (GB)'
          'Allowed Log Growth (GB)' = Case
              When Log_Max_Size > 0 Then ((Log_Max_Size - Log_Reserved_Space)/1024)
              Else 0
          End,
          (Log_Growth/1024) As 'Growth Increment (GB)'
  from #DBResults
  Order By DBName Asc, DB_Reserved_Space Desc

  Select * from #DBFileUsage_Report
  --where DBName in ('dev0NextCCDB','TempDB')

  Drop Table #tmp_DBNames
  Drop table #tmp_DBSpacing
  Drop Table #DBFile_Info
  Drop Table #DBResults
  Drop Table #DB_Logfile_Info
  Drop Table #DBFileUsage_Report

 




I hope this helps reduce your workload for this small repetitive task.

Leave a Reply

Your email address will not be published. Required fields are marked *

Warwick

I am a Microsoft Data Platform MVP as well as a Microsoft Certified Master working as the Principal Consultant here at SQL Masters Consulting. When I am not working with the SQL Server Stack I like to get away to the Snow and spend time Snowboarding.

Search