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

SQL Largest tables Expand / Collapse
Author
Message
Posted Saturday, October 20, 2012 2:39 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 1,379, Visits: 2,698
Hi All

I'm using the built in "Disk Usage by Top Tables Report" in SQL Management Studio to check the tables on my database.

How is the unused space calculated? Is this the amount of space that was created by page splits?

My database has a significant amount of tables with an unused space amount of 4GB+.

According to http://msdn.microsoft.com/en-us/library/cc280506(v=sql.100).aspx, the unused space is:The amount of disk space allocated to one or more objects, but not yet used.

From what I understand, you can't explicitly allocate space to a table.

Any help?

Thanks
Post #1375127
Posted Sunday, October 21, 2012 12:19 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
The unused space is calculated simply by finding the total space allocated and subtracting the space used by data and indexes. If you want to do a deep dive on the subject, see the sp_spaceused stored procedure in the master database.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1375192
Posted Sunday, October 21, 2012 7:43 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 1,379, Visits: 2,698
Jeff Moden (10/21/2012)
The unused space is calculated simply by finding the total space allocated and subtracting the space used by data and indexes. If you want to do a deep dive on the subject, see the sp_spaceused stored procedure in the master database.


Thanks
Post #1375233
Posted Tuesday, October 23, 2012 1:08 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:49 PM
Points: 81, Visits: 930
This query might help

select
a1.object_id
, a2.name
, a1.used_page_count * 8 as total_used_pages
, cast(a1.used_page_count * 8 /1000 as varchar(10)) + 'MB' as SIZE_total_used_pages
, a1.reserved_page_count * 8 as total_reserved_pages
, a1.row_count
from sys.dm_db_partition_stats a1
inner join sys.all_objects a2 on ( a1.object_id = a2.object_id )
left outer join sys.indexes a3 on ( (a1.object_id = a3.object_id) and (a1.index_id = a3.index_id) )
where (select count(distinct partition_number)
from sys.dm_db_partition_stats a4
where (a4.object_id = a1.object_id)) >= 1 and a2.type <>'S'
--and (a1.used_page_count * 8 /1000) > 1 --Uncomment this line to list tables that occupy >1MB space
order by a1.used_page_count desc, a2.name asc, a1.index_id, a1.partition_number

Post #1375863
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse