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:
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:
CREATE TABLE DB_SIZE_AUDIT
DATA_CAPTURE_DATE DATETIME NOT NULL DEFAULT GETDATE(),
CONSTRAINT PK_DB_SIZE_AUDIT PRIMARY KEY (DATABASE_NAME, DATA_CAPTURE_DATE)
INSERT INTO DB_SIZE_AUDIT (DATABASE_NAME, DB_SIZE_MB, OWNER_NAME, DB_ID_VAL, DATE_CREATED, STATUS_TEXT, COMPAT_LVL)
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.
One Response to “Simple Script to capture the size of the databases in SQL Server”
Sorry, the comment form is closed at this time.