Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Identify Large Tables Expand / Collapse
Author
Message
Posted Thursday, January 31, 2008 6:59 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:18 AM
Points: 305, Visits: 567
Comments posted to this topic are about the item Identify Large Tables

Jesse McLain
jesse@jessemclain.com
www.jessemclain.com
www.jessesql.blogspot.com
Post #449921
Posted Thursday, January 15, 2009 1:05 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:30 PM
Points: 115, Visits: 321
Jessie, thanks very much for this, but I found something rather interesting, perhaps you can explain.

The query identified a table with row count = 154 and phys_size_KB = 26760. However, when I selected all rows I got a count of 202 is this because the statistics are out of date?

This table has a high unused allocation of 99%. I calculated this based on unused/reserved. I just ran a shrink on the database and it does not seem to help. Can you explain this?

Thanks




Doug
Post #637522
Posted Thursday, January 15, 2009 1:26 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 21, 2014 12:30 PM
Points: 115, Visits: 321
Jessie, I did some searching and found that you should add a param to the sp_spaceUsed proc. This will give you a more accurate value.


EXEC sp_spaceused @User_Table_Name,'true'

I also used this to get the unused KB's for each table

Unused_KB = CONVERT(int, LEFT([unused], PATINDEX('% KB', [unused]) - 1))

Thanks again.




Doug
Post #637546
Posted Friday, February 13, 2009 1:12 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 22, 2014 11:18 AM
Points: 305, Visits: 567
Doug, thanks for the responses. I updated the code by adding the 'TRUE' parameter to the sp_spaceused call (and gave you credit for that - I submitted the updated version and it should be published soon). I noticed that it slowed down performance the first time it ran, and then ran instantaneously on subsequent executions.

Jesse McLain
jesse@jessemclain.com
www.jessemclain.com
www.jessesql.blogspot.com
Post #656954
Posted Monday, March 26, 2012 12:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 5:23 AM
Points: 1,522, Visits: 2,731
here is another way to do this without a cursor

http://www.wvmitchell.com/tips/SQL_table_sizes.htm
Post #1272899
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse