Monitoring database size

One thing every DBA ought to have (and many do!) is a way to monitor and routinely report on the size of databases and their growth.

This is important when it comes time to discuss the purchase or expansion of a SAN, NAS or other storage subsystem.

Frequently management will make decisions that significantly accelerate database growth, yet the nature of the change makes it almost impossible to predict with any accuracy how fast the growth will be.

Having a good means of tracking, measuring and reporting on this growth is vital to our success when wearing our DBA hat.

The script below is one I use.  Every SQL Server install I do, the first thing I do after getting the server up on its legs is I create the database 'DBA'.  This holds all my management and measurement 'stuff'.  This script creates the table tbl_dbsize (in the comment).  Then, whenever I run it, the script gets information on the database files for each database on the server and the size of the file in MB.  The "inserted on" timestamp column gives me a way to measure the growth over time.

We recently made the decision to start scanning copies of important documents at the company where I am employed; these document scans can be added to a 'file room' tab in our ERP system.  This is great for our ability to attach document copies to transactions in the system.  However, when all our locations start doing this and we have no way of knowing with certainty the size of the scanned files, close monitoring is prudent.

So, I'm ratcheting up the frequency of this job from monthly to weekly right now in order to see what impact these scans will have on growth of the database.

If you don't have a script or job like this, give this one a try.


USE [DBA]
/*
Report: Database Size
Contents:   size of database files

CREATE TABLE tbl_dbsize (
[inserted on] DATETIME,
[DB Name] NVARCHAR(255),
[Logical file] NVARCHAR(255),
[Physical file]    NVARCHAR(1000),
[MB] INTEGER
)
;
*/

INSERT INTO tbl_dbsize
SELECT GETDATE()[inserted on],
DB_NAME(database_id) AS [DB Name],
Name AS [Logical file],
Physical_Name [Physical file], (size*8)/1024 SizeMB
FROM sys.master_files




SELECT * FROM dbsize; -- <-- right now I don't have the need for an SSRS report, I dump the answer set into an Excel spreadsheet that I can use to track the growth of files over time.