November 10, 2014 at 3:44 pm
Hi all,
I have a server with ~25 databases and each database has anywhere between 10 and 500 tables.
I also know that I have a total of 6 TB storage dedicated to this server.
The goal is to find out how much is being used and by whom (e.g. how much is being used by each table in each database) and how much is still available.
What is the most elegant way to do this global space utilization audit across all the objects on the server?
Please advice.
Thanks in advance!
November 10, 2014 at 11:55 pm
Something like this, but run it for each database:
declare
@pagesbigint-- Working variable for size calc.
,@reservedpages bigint
,@usedpages bigint
,@rowCount bigint
select
object_name (p.OBJECT_ID) as ObjectName,
index_id,
sum(a.total_pages) as ReservedPages,
sum(a.used_pages) as usedpages ,
sum(
CASE
-- XML-Index and FT-Index internal tables are not considered "data", but is part of "index_size"
When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0
When a.type <> 1 Then a.used_pages
When p.index_id < 2 Then a.data_pages
Else 0
END
) as pages
into #temp1
from sys.partitions p join
sys.allocation_units a on p.partition_id = a.container_id
left join sys.internal_tables it on p.object_id = it.object_id
group by
p.object_id,
index_id
select
objectname,
index_id,
reservedMB = reservedpages * 8192 / 1024/1024,
dataMB = pages * 8192 / 1024/1024,
index_sizeMB = (usedpages - pages) * 8192 / 1024/1024,
unusedMB = (reservedpages - usedpages) * 8192 / 1024/1024
from #temp1
order by reservedpages desc
November 11, 2014 at 12:57 am
Undocumented sp_MSforeachdb can iterate SQL query over databases . For example
EXEC sp_MSforeachdb 'IF ''[?]'' NOT IN (''[master]'',''[model]'',''[msdb]'')
BEGIN
SELECT name,physical_name,state,size
FROM [?].sys.database_files
END'
November 12, 2014 at 12:19 pm
Thanks for everyone's input.
Check this out for another solution: http://basitaalishan.com/2012/07/01/determine-space-used-for-each-table-in-a-sql-server-database/
November 13, 2014 at 2:58 am
sql_er (11/12/2014)
Check this out for another solution: http://basitaalishan.com/2012/07/01/determine-space-used-for-each-table-in-a-sql-server-database/%5B/quote%5D
Concerning quoted solution. Shouldn't it be
WHERE i.index_id > 1
instead of
WHERE i.index_id <> 0
in the IndexPages () to exclude CLUSTERED index pages already counted as DataPages ?
P.S. unfortunately forum engine doen't like some '>' in sql code 🙁
November 13, 2014 at 8:24 am
I believe you are correct.
I did notice that for instances where tables had clustered indexes, adding index to page space utilization of this query result would show double the correct value.
Great catch!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply