Identify Large Tables

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jesse McLain » Identify Large Tables

Identify Large Tables

Rate Topic

Display Mode

Topic Options

Author

Message

Jesse McLain

Posted Thursday, January 31, 2008 6:59 AM

Old Hand

Group: General Forum Members
Last Login: Monday, May 06, 2013 8:47 AM
Points: 304, Visits: 552

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

Doug S.

Posted Thursday, January 15, 2009 1:05 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 21, 2011 8:43 AM
Points: 115, Visits: 315

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

Doug S.

Posted Thursday, January 15, 2009 1:26 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 21, 2011 8:43 AM
Points: 115, Visits: 315

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

Jesse McLain

Posted Friday, February 13, 2009 1:12 PM

Old Hand

Group: General Forum Members
Last Login: Monday, May 06, 2013 8:47 AM
Points: 304, Visits: 552

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

WILLIAM MITCHELL

Posted Monday, March 26, 2012 12:04 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 6:45 AM
Points: 1,441, Visits: 2,485

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 »

Permissions