Updating a stored procedure

The formula is "Total(MB) - Unused(MB) == Used(MB) = Index(MB) Data(MB)", i.e., the used space is the simultaneous result of total minus unused as well as index plus data.

The "reserved" column in sp_spaceused is actually equivalent to the total size in sp_SOS.

The arrow (= =) indicates the actual commands followed by updating details.

Figure 1: sp_SOS shows the grouping of user tables that has similar names and the subtotal space for that group in a SQL Server 2000 user database.

With sp_SOS I wanted to retain the core functionality of sp_spaceused -- i.e., the algorithm to sum up data, index and reserved and unused spaces for an object.

With sp_SOS, I can sum up the values at the bottom of the result.

Figure 2 shows the details of DBCC UPDATEUSAGE for each table involved.

This allows you to sum up a group of objects that bear a similar pattern in names or owner names.

While we are still using Adventure Works as the sample database, we want to list all Sales schema-owned objects that look like "Sales" in the order of used disk space. I like to run "DBCC UPDATEUSAGE" first to update any incorrect values. Listing 3: List objects owned by Sales schema and named like "Sales", in a descending order of used disk space.

Leave a Reply