UPDATE: The sp_SOS procedure is not working with the latest versions of SQL Server.
Use Stored procedure to identify the top n biggest tables in a database instead
To find the biggest tables in the SQL Server database I am using good stored procedure
EXEC dbo.sp_SOS @OrderBy=‘T’
/*downloaded from http://media.techtarget.com/searchSQLServer/downloads/URL_for_SQL_Script_download_Tip_on_Sp_SOS.doc
The related article “Find size of SQL Server tables and other objects with stored procedure”
Valid @OrderBy parameters are:
‘N’ –> Listing by object name
‘R’ –> Listing by number of records
‘T’ –> Listing by total size
‘U’ –> Listing by used portion (excluding free space)
‘I’ –> Listing by index size
‘D’ –> Listing by data size
‘F’ –> Listing by unused (free) space
‘Y’ –> Listing by object type
*/
It’s a start point before you will Archive Old data or consider to Partition table (see Sql Server 2005 – Twelve Tips For Optimizing Query Performance by Tony Wright)