Skip to main content
Dave Mason - Mastodon Dave Mason - LinkedIn Dave Mason - GitHub Dave Mason - Counter Social

SQL Server Disk Space: How Much Will You Need?

When you're a DBA, it's common to be asked "How much data do we have?" Most of the time, you can just make something up. Ya' know, some really big number that will impress your boss, the marketing department, or the annoying sales guy that can't stop blurting out tech acronyms. When it's the SAN administrator, fiction won't cut it. You'll need the facts. It's pretty straightforward to quickly produce a query that shows how much space the databases occupy on disk. That answers the storage question--for now 1 Excluding backup space. But what about the future? Can you estimate future disk space needs?

It's pretty easy to keep a history of database file sizes. Just query the master files system view and dump the results into a table on a periodic basis. I do this once per day. Here's what my archive table looks like: 


CREATE TABLE dbo.MasterFilesHistory (
    MasterFilesHistoryId INT IDENTITY
        CONSTRAINT PK_MasterFilesHistory PRIMARY KEY,
    server_name SYSNAME,
    database_name SYSNAME,
    type_desc NVARCHAR(60),
    size INT,
    StatisticsDate DATE NOT NULL
        CONSTRAINT DF_MasterFilesHistory_StatisticsDate DEFAULT(CURRENT_TIMESTAMP)
)
GO

After accumulating a few months of data, you can query the table to get an idea of the growth (or decline) of your data over a period of time. For example: 

SELECT 
    SUM(size) / 1024.0 / 1024.0 * 8  TotalSize_GB,
    StatisticsDate
FROM dbo.MasterFilesHistory fh
WHERE DATENAME(dw, fh.StatisticsDate) = 'SATURDAY'
GROUP BY fh.StatisticsDate
ORDER BY fh.StatisticsDate


I decided to take this a step further. I used the query output to create a graphical report and set up a SQL Agent job to email the report once per week. For the report, I chose Google Charts 2 I would imagine there are many options for creating and emailing a report of SQL Server data. Google Charts might not be your cup of tea. C'est la vie. I've used a few charts in some other posts. Here's one. And another. To generate the report, I created this stored proc: 


CREATE PROCEDURE dbo.[Disk Usage By Week - Database Files]
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @ArrayItems VARCHAR(MAX) = ''

 SELECT @ArrayItems += '[''' + CONVERT(VARCHAR, StatisticsDate, 101) + ''', ' + 
 CAST(SUM(size) / 1024.0 / 1024.0 * 8 AS VARCHAR) + '],' + CHAR(13) + CHAR(10) 
 FROM dbo.MasterFilesHistory fh
 WHERE DATENAME(dw, fh.StatisticsDate) = 'SATURDAY'
 --Only return 12 mos of data.
 AND StatisticsDate >= CURRENT_TIMESTAMP - 365 
 GROUP BY fh.StatisticsDate
 ORDER BY fh.StatisticsDate

 DECLARE @HTML VARCHAR(MAX) = '<!DOCTYPE html>

 <html lang="en" xmlns="http://www.w3.org/1999/xhtml">
 <head>
 <meta charset="utf-8" />
 <title>
 Disk Usage By Week - SQL Server Backups
 </title>
 </head>
 <body>
 <form name="form1" id="form1">
 <script type="text/javascript"
  src="https://www.google.com/jsapi?autoload={''modules'':[{''name'':''visualization'',''version'':''1'',''packages'':[''corechart'']}]}">
 </script>
 <script type="text/javascript">
 google.load(''visualization'', ''1'', { packages: [''corechart''] });
 google.setOnLoadCallback(drawChart);

 function drawChart() {
  var data = google.visualization.arrayToDataTable([[''Date'', ''GB''],' +
  @ArrayItems +
  ']);

  var options = {
     title: ''Disk Usage By Week - SQL Server Database Files'',
     //width: 1000,
     height: 563,
     hAxis: {
      title: ''Week Start Date'',
      gridlines: { count: 10 }
     },
     vAxis: {
      title: ''Size (in GB)''
     }
    };

    var chart = new google.visualization.ColumnChart(
     document.getElementById(''ColumnChart1''));

    chart.draw(data, options);
 }
 </script>
 <div id="ColumnChart1">
 </div>
 </form>

 </body>
 </html>'

 SELECT @HTML
END
GO

I know what you're thinking 3 Cue Thomas Magnum "Yuck! All that dynamic HTML generation makes me wanna puke." Sorry about that. When you run the stored proc, you indeed get a chunk of HTML. To see what it looks like in a browser, copy the output, paste it into notepad, save the file somewhere on disk, change the file extension from "txt" to "html", and open it in the browser of your choice. It should look something like this:


Now, how do we get that report into an email message? In short, I don't know how. I wasn't able to find a way to directly embed the chart in email. Which kinda makes sense. It uses javascript. Correct me in the comments if I'm wrong, but I doubt there are any mainstream email clients that support javascript in HTML formatted messages. But, we can include the chart as an html file attachment with an email message. Fortunately, this can be done from t-sql. It's sp_send_dbmail to the rescue! 


DECLARE @Subj NVARCHAR(255)
SET @Subj = 'Disk Usage By Week - Database Files'
EXEC msdb.dbo.sp_send_dbmail
 @recipients = 'You@YourDomain.com',
 @subject = @Subj,
 @body = 'See attachment(s)...',
 @query = 'EXEC dbo.[Disk Usage By Week - Database Files]',
 @attach_query_result_as_file = 1,
 @query_attachment_filename = 'WeeklyStatistics.html',
 @query_result_header = 0,
 @exclude_query_output = 1,
 @query_no_truncate = 1,
 @query_result_width = 32767,
 @reply_to = 'You@YourDomain.com'

Comments

Post comment