I enjoyed Gregory Larsen's article "Avoiding the Red Zone" (December 2002, InstantDoc ID 26874). But why didn't the author implement a stored procedure to collect the necessary database-growth statistics and store them directly in the DBA database rather than creating a procedure that generates a script through OSQL, which is then itself executed through OSQL? DBCC SHOWFILESTATS must be executed in the context of each database, which means that only a procedure in master can perform this check on a database without resorting to xp_cmdshell and OSQL. Unless you must avoid adding a procedure to master, a stored procedure seems to be a more elegant solution.
Our organization has kept database-growth, data-file, and table statistics for years by scheduling a wrapper stored procedure that executes a procedure in master for each application database, such as:
EXEC @retcode =.dbo.sp_DBA_add_ database_space_history
Because we maintain several hundred servers, we send all these details to a central history server for reporting purposes, using either linked-server inserts from SQL Server 2000 or 7.0 or remote procedure calls from SQL Server 6.5. Note that because of a bug, SQL Server 2000 doesn't apply database options correctly when you use a procedure in master and a cursor. The best workaround is to use a table variable instead of a cursor to populate the list of databases.
Your approach of creating a stored procedure in master is an excellent solution that simplifies the process of gathering space-usage statistics as long as you don't have restrictions on storing user objects in master. In our organization, we've decided not to put user objects in master, but that's a decision that each organization needs to make for itself.
Metadata in Tables vs. Extended Properties
I have a question related to Michelle A. Poolet's Solutions by Design column "A Business Metadata Repository" (October 2002, InstantDoc ID 26273), which describes the value of creating extended properties on tables, columns, and other objects in SQL Server 2000. The company I work for maintains two tables that contain metadata: one for tables and the other for columns. Is there any advantage to moving the metadata from our table structure into extended properties? Currently, we aren't using the metadata information in these tables. I've thought about auto-generating a data dictionary based on the metadata, but we can still do that with our current structure without using extended properties and without requiring customers to be on SQL Server 2000.
The main advantage to storing metadata as extended properties is that the metadata is intimately associated with the database object that it describes—you don't need to create and manage additional user tables to store the metadata. If calling programs heavily used the metadata, you might find that storing all the metadata in two user tables would cause excessive contention. However, that doesn't sound like your situation. You also mentioned that you have customers who aren't on SQL Server 2000, and that's a good reason to keep things the way they are.
