Get database file size information in SQL Server 2005

It is important to capture trends in the size of SQL Server 2005 databases, because DBAs can use this information to plan for future space requirements, the type of notification to question, and the time period to plan heavy loads. This article will introduce a simple way to capture this information.

Each database on SQL Server contains information about the size of the database file, along with some other relevant information. To get this information, we need a way to retrieve the relevant data at once from a single number library.

There are two ways to implement this feature:

sp_spaceused: This system stored procedure will return the disk space used by the tables, indexed views, or SQL Server service Broker queues in the current database. This is the return database of the stored procedure or the size of the table in the database, but the output of the report is not very friendly and difficult to understand. The stored procedure captures information about each database by its script, but it may need to use a user-defined cursor.

SP_MSFOREACHDB: This is a very useful system stored procedure that passes any SQL script to you to execute on an instance of SQL Server on each database. Stored procedures traverse each database, which eliminates the task of specifying the database name. In this article we will use this stored procedure to capture information about the size of each database file.

This information I want to collect and store can be obtained in the sys.database_files system view, including the size of the database file, and other related information, such as the state of the database, the way the database file grows (increasing by absolute size or by percentage), whether it is read-only, and so on. I need to capture this information in each database.

The following script, based on the structure of the system view sys.database_files, creates a table named DatabaseFiles (if it does not exist) and adds a new field to record when the captured record is inserted into the datasheet.

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 you need to populate the DatabaseFiles datasheet. The following script uses the SP_MSFOREACHDB stored procedure and reads the data from sys.database_files through a SQL script and inserts it into the DatabaseFiles datasheet.

EXECUTE sp_msforeachdb ' INSERT into DatabaseFiles SELECT *, GETDATE () from [?]. Sys.database_files '

From the script we can find that the database name of each database is somewhat special: use [?] As the prefix for the view sys.database_files.

When this code executes, it actually traverses each database instance and replaces the database name as a parameter [?] Mark. Information for each database is inserted into the DatabaseFiles table. This is much easier than writing a custom cursor yourself. I also added a getdate () function to record the time that the record was inserted into the table.

Note: This example is a bit of a violation of coding standards, and when you insert a table using SELECT *, you don't explicitly specify that the field name does not list. If used in the actual project, we recommend that you make a change, indicating the field name.

To ensure that all data is captured correctly, let's look at the contents of the DatabaseFiles datasheet.

SELECT * from DatabaseFiles

After you execute the above command, you can get information similar to the following:

Figure 1: Viewing data for the size of the database captured