Introduction
Do you have or have you ever managed a legacy SQL database that has become large and unwieldy? This tool can be executed at anytime, even as a scheduled task, to create a unique date and time stamped CSV file with a report of the table space used for each table in the database, using the sp_spaceused stored procedure.
Background
This article uses my previous submission Custom Configuration SQL Connection String Section to iterate through a defined collection of SQL connection strings to generate the CSV files. The SQL itself is quite simple, and uses a cursor to iterate through all the tables defined in the sysobjects table to execute the sp_spaceused stored procedure.
Using the code
Using the application is quite simple. For each SqlConnectionString defined in the App.Config file, a SqlConnection is established and a cursor is used to iterate through the tables found in the database to generate a CSV file. The file name format is [Database Name].TableSpaceUsed.[yyyy_MM_dd_HH_mm].CSV and can be easily changed in the Main method.
This sample assumes that the (local) instance of the SQL Server has the Model, pubs, and Northwind databases and that the account the application is running under has permissions to access the tables.
The project can be easily modified to store the results in another SQL database or an XML file.
Points of Interest
I used this tool at my employer's because we have a database that has grown by 19% in five months from 8 GB to 9.6 GB, and it has been determined that over 1/2 of the capacity is consumed in three tables. We are now researching how to flatten those tables to reduce the database size.
History
- First version, January 26th, 2006.
