Capturing SQL Server 2005 database file size information

It's very important to capture trends of the sizes of your SQL Server 2005 database because it allows you to plan for future space needs, notice types of problems, and plan for time periods of heavy volume. I'll show you the simple method that I use to capture this information.

An example

I will capture a snapshot of the information related to the sizes of my database files; in my next article, I will analyse the information to see when my data files and log files grow the most.

Each database on the SQL Server contains information regarding the size of the database files, along with some other related information. In order for me to get to this information, I need a method to retrieve the data from the individual databases one at a time. I have two available options:

• sp_spaceused: This system stored procedure will return the size statistics for the current database context in which it is running. It is very useful for returning ad hoc information regarding database or table sizes within the database; however, it is not very friendly for reporting purposes. It is possible to capture the information for each database through a script, but it would require the use of a user-defined cursor.
• sp_msforeachdb: This is a very useful system stored procedure that will execute any SQL script you pass to in each of the databases on your SQL Server instance. The stored procedure just loops through the databases, which is simple to write, but it saves you from having to do it yourself. This is the method I will use for my code to capture database file size information.

The information I want to gather and store is available in the sys.database_files system view. This gives me the size of the database files, along with some other handy information such as the state of the database, the manner in which the files grow (size or percentage), and if it is read-only. I will need to capture this information for each database.

The script below creates a table named DatabaseFiles (if it does not already exist) based upon the structure of the system view sys.database_files; it also adds a new column to capture when the record was added to the table.

IF OBJECT_ID('DatabaseFiles') IS NULL
BEGIN
  SELECT TOP 0 * INTO DatabaseFiles
  FROM sys.database_files

  ALTER TABLE DatabaseFiles
  ADD CreationDate DATETIME DEFAULT(GETDATE())
END

Now it is time to populate the DatabaseFiles table. This script uses the sp_msforeachdb stored procedure and passes a SQL script that inserts data from the sys.database_files view into the DatabaseFiles table that I created above. If you examine the script, you will notice that I am building in the database name for each database. This is subtle, and it's accomplished by the [?] prefix to the sys.database_files view. This code is actually executed in each database on the instance, and the name of the database is used in place of the [?] marker. Information for each database is inserted into the DatabaseFiles table with one line of code, and it is a lot easier than writing a cursor to do the same. I also added a GETDATE() call to indicate when the records were inserted into the table.

Note: This example somewhat goes against two coding standards that I am typically strict about: using SELECT * and inserting into a table without a column list. I omitted them because the SQL string that I am building would have been a lot less desirable to view. If this was code that I put into a production environment, I would have made the necessary changes accordingly.

EXECUTE sp_msforeachdb 'INSERT INTO DatabaseFiles SELECT *, GETDATE() FROM [?].sys.database_files'

To make sure that all of my data was captured correctly, I'll look at what is in the table.

SELECT * FROM DatabaseFiles

What's next?

In my next article, I will show how you can create and schedule a SQL Server job to run on a regular basis to populate your DatabaseFiles table, as well as how to create a report to show file growth trends over time.