Systems Engineering and RDBMS

Simple Script to capture the size of the databases in SQL Server

Posted by decipherinfosys on January 14, 2008

A client DBA asked whether a simple script could be written to monitor the size of the databases in an instance.  Though there are a lot of tools out there that will do that using one of their in-built reports, this shop did not have those tools and utilities in place.  One can simply make use of the sp_helpdb system stored procedure in order to get to that data.  And in case you want to see how this system stored procedure captures the data, all you need to do is to look at the text of that stored procedure:

sp_helptext sp_helpdb
go

And you will see the calls being made to sysdatabases, sysfiles etc.  It is a good learning exercise to learn about the internals by looking at the code of these system stored procedures.

You can just make use of the sp_helpdb system stored procedure and dump that output into a temp table and report on the sizes.  You can also maintain a physical table and dump the data in it and see the percentage growth over a period of time.  That way you can do your calculations on the growth that you are encountering in your instance.  Here is an example:

USE TEMPDB
GO
CREATE TABLE DB_SIZE_AUDIT
(
DATABASE_NAME            NVARCHAR(50),
DB_SIZE_MB                NVARCHAR(20),
OWNER_NAME                NVARCHAR(50),
DB_ID_VAL                INT,
DATE_CREATED            DATETIME,
STATUS_TEXT                NVARCHAR(1024),
COMPAT_LVL                INT,
DATA_CAPTURE_DATE DATETIME NOT NULL DEFAULT GETDATE(),
CONSTRAINT PK_DB_SIZE_AUDIT PRIMARY KEY (DATABASE_NAME, DATA_CAPTURE_DATE)
)
GO

INSERT INTO DB_SIZE_AUDIT (DATABASE_NAME, DB_SIZE_MB, OWNER_NAME, DB_ID_VAL, DATE_CREATED, STATUS_TEXT, COMPAT_LVL)
EXEC SP_HELPDB
GO

Then, you can schedule the insert script execution to be a nightly task and do trend analysis on the data that is captured.  You would need to substring the “MB” out of the DB_SIZE_MB column prior to converting it to be NUMERIC(10,2) data-type.

About these ads

One Response to “Simple Script to capture the size of the databases in SQL Server”

  1. [...] different reasons – one can be to monitor the size of the databases – we had blogged that before here. For the error above, that the end user received – the issue resolution is pretty straight forward. [...]

Sorry, the comment form is closed at this time.

 
Follow

Get every new post delivered to your Inbox.

Join 84 other followers

%d bloggers like this: