January 14, 2017 at 9:30 am
New accidental DBA. Am tasked with finding the size of each table in each DB in BYTES. To do so, I'm using this to do so
SELECT
t.NAME AS TableName,
s.Name AS SchemaName,
p.rows AS RowCounts,
SUM(a.used_pages) AS UsedSpace
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
t.Name
However, I'm not really clear on how much space each 'used page' actually contains.... is it 8 KB like the pages from sys.master_files? I have Googled the crap out of this, but I'm not finding any good answer. The white page on sys.allocation_units just says that the used_pages field is 'Number of total pages actually in use'. Maybe I need to go back to Google-fu school.....
Thanks!
January 16, 2017 at 2:52 am
Pages in SQL Server are 8KB.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 16, 2017 at 8:13 am
You should use "total_pages" rather than "used_pages" because the table may currently take up more space, sometimes a lot more space, than it is actually using.
For example, a table could be allocated 500MB but only be using, say, 5MB.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 16, 2017 at 7:05 pm
ScottPletcher - Monday, January 16, 2017 8:13 AMYou should use "total_pages" rather than "used_pages" because the table may currently take up more space, sometimes a lot more space, than it is actually using.For example, a table could be allocated 500MB but only be using, say, 5MB.
Would that work even when trying to measure growth over time? If it is allocated, is it not a static number, or does more get allocated based on need?
January 16, 2017 at 9:17 pm
This was removed by the editor as SPAM
January 17, 2017 at 2:55 am
scarr030 - Monday, January 16, 2017 7:05 PMdoes more get allocated based on need?
Yes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply