One of the challenges in managing a SQL Server database is keeping track of its size and growth over time. The built-in stored procedure sp_spaceused provides useful information about the physical space consumed by a database or database object. However, it has some limitations that can make it difficult to use in an automated monitoring system.
One limitation of sp_spaceused is that it does not include the schema name of the object, making it impossible to differentiate between identically named tables in different schemas. This can be problematic if you have multiple tables with the same name in your database. Additionally, the data size values returned by sp_spaceused include the “KB” suffix, which can complicate exporting the results for graphing purposes.
To overcome these limitations, we can create our own version of sp_spaceused that includes the schema name and returns the space information as numeric values. By modifying the original stored procedure and using system management views, we can create a more robust and efficient solution.
First, we need to modify the output portion of the stored procedure to return the object name and space information as numeric values. This can be achieved by changing the relevant lines of code in the procedure. We also need to update the procedure name to differentiate it from the built-in sp_spaceused.
Once we have our modified version of sp_spaceused, we can create a new stored procedure that runs it for all tables in a given database. This can be done by looping through all user tables in the database and executing our modified sp_spaceused for each table. The results can then be stored in a temporary table or a dedicated table in a separate database for ongoing monitoring.
It’s important to validate our stored procedures by comparing the results with the original sp_spaceused and ensuring consistency. We can also use the sp_msforeachdb system stored procedure to run our monitoring procedure for all databases on the server.
By implementing this automated database size monitoring system, we can easily track the growth of our databases over time and identify any potential issues. The lightweight nature of the stored procedures ensures that even on servers with a large number of databases and tables, the monitoring process is quick and efficient.
Overall, using our modified version of sp_spaceused and a monitoring procedure, we can simplify the task of tracking database size and growth, making it easier to manage and optimize our SQL Server environment.