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)
Alternatively(and simpler) use Stored procedure to identify the top n biggest tables in a database