Systems Engineering and RDBMS

Archive for October 29th, 2007

DBCC UPDATEUSAGE, sp_spaceused and Estimating the size of the database

Posted by decipherinfosys on October 29, 2007

In some of our previous blog posts, we have covered techniques on how to go about calculating the size of the database (Oracle or SQL Server). Search for “estimating the size” on this site and you will get the three posts for Oracle. Likewise, for SQL Server, Microsoft has provided guidelines in BOL – search BOL for “Estimating the size of a database” and you will get all the steps and calculations that you need to estimate the size of the table, a heap, a clustered or a non-clustered index. Based on those, one can write up a stored procedure or a script to compute the size. We had written a similar script while doing capacity planning for one of our clients. The script took the @db_name, @table_name, @num_rows, @var_pcnt as the parameters (database name, table name, number of rows for which the size needs to be estimated and the percentage fill for the variable length columns) and gave the respective size distributions as well as the total estimated size for the table and it’s indexes. You can also reference those calculations from the online version:

http://msdn2.microsoft.com/en-us/library/ms187445.aspx

In SQL Server 2000, one needed to use DBCC UPDATEUSAGE command in order to get the right page and rowcounts from the catalog views – in SQL Server 2005, this is no longer the case since SQL Server 2005 always maintains these correctly. However, if you have upgraded from SQL Server 2000 to SQL Server 2005, then you would need to run this command in order to remove any inaccuracies for page and row-counts else the data that is shown by sp_spaceused system procedure will be incorrect. You can get more information about this command and it’s different options from BOL.

Besides using the calculations mentioned above, you can also chose to wrap that using a GUI tool and provided additional value to your employer/client. One such utility that is available freely is the one written by Øystein Sundsbø. It is posted on his blog and you can access it here:

http://dbgoby.blogspot.com/2007/10/db-goby-v10.html

It does way more than just estimate the size of the database and is compatible with SQL Server 2005 as well as SQL Server 2008.

Posted in SQL Server | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.

Join 79 other followers