Capture database file size information
Thursday, March 13, 2008 01:55 PM
Being able to retrieve information about the size of files in an SQL Server 2005 database, one can better plan for space.
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.
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 for 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
Tim Chapman is a SQL Server database administrator and consultant who works for a bank. He has more than eight years of IT experience, and he is a Microsoft-certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.
Play video
» Achieve enhanced server performance with energy-efficient blade technology
There are currently no comments for this post.