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:
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:
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'