I know that it's a repeated question but I've tried all solution and workaround suggested from every possible question present on stackoverflow.com
I have inherited a dedicated SQL Pool with a SQL Server version 2012.0.2000.8 on which there are several tables.
We're looking to understand how much retention should we set and to do that we're analyzing the size of every table.
- Show data and disk use breakdown by table
- space usage on sys.allocation_units and sp_spaceused
- Generating report by clicking on the right of database, but I don't have that option in SSMS
Tried to write this query
SELECT OBJECT_SCHEMA_NAME(p.object_id) + '.' + OBJECT_NAME(p.object_id) AS TableName, SUM(p.rows) AS RowCounts, CONVERT(DECIMAL,SUM(p.reserved_page_count)) * 8 / 1024 AS TotalSpaceMB, CONVERT(DECIMAL,SUM(p.used_page_count)) * 8 / 1024 AS UsedSpaceMB , CONVERT(DECIMAL,SUM(p.reserved_page_count - p.used_page_count)) * 8 / 1024 AS UnusedSpaceMB FROM sys.partitions p WHERE p.index_id <= 1 GROUP BY p.object_id ORDER BY UsedSpaceMB DESC, TableName;
or this one
SELECT t.NAME AS TableName, p.rows AS RowCounts, CONVERT(DECIMAL,SUM(a.total_pages)) * 8 / 1024 / 1024 AS TotalSpaceGB, SUM(a.used_pages) * 8 / 1024 / 1024 AS UsedSpaceGB , (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 / 1024 AS UnusedSpaceGB 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 WHERE t.NAME = 'mytable' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255 GROUP BY t.Name, s.Name, p.Rows ORDER BY UsedSpaceGB DESC, t.Name
Everytime there is a use of Sys Views (sys.schemas / sys.partition / sys.allocation_units etc. etc.) it says
Catalog view 'X' is not supported in this version.
I think that mostly there could be a problem with some permission since I cannot use system views and they should be present in my version of SQL Pool.
So, my question is,
how I can fix using system views ?