0

I have a SQL Server database that has reached the 10 GB limit.

When I do use EXEC sp_spaceused, I get

reserved data index_size unused
10483208 KB 10352336 KB 90264 KB 40608 KB

However when I do this query:

SELECT SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables t JOIN sys.indexes i ON t.OBJECT_ID = i.object_id JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id JOIN sys.allocation_units a ON p.partition_id = a.container_id LEFT JOIN sys.schemas s ON t.schema_id = s.schema_id

I get that the sum of the tables barely is 450 MB.

I've started getting this error when doing some procedures:

System.Data.SqlClient.SqlException (0x80131904): Could not allocate space for object 'sys.change_tracking_517680992' .'sys_change_cidx' from database '***' because filegroup 'PRIMARY' is full. Delete unnecessary files, remove objects from the filegroup, add additional files to the filegroup, or set the auto-growth option for existing files in the filegroup to create disk space.

So I guess it's logs or tracking files that are taking up the remaining space? Can I delete them somehow?

Improve this question
0

2 Answers 2

Reset to default
1

Based on error message the space could be taken by Change Tracking

Do you have it enabled? Do you have Auto cleanup configured? What is retention_period? Is it something you are expected?

Check in sys.change_tracking_databases

If your problem related to change tracking here are some troubleshooting

Improve this answer
1
  • Thank you! Change Tracking Auto Cleanup was set to False, I've changed it to True. This solved my problem. Commented Apr 23, 2024 at 14:01
5

Did you run the "Disk Usage by Table" report in SSMS? It runs this query:

SELECT (row_number() over(order by a3.name, a2.name))%2 as l1, a3.name AS [schemaname], a2.name AS [tablename], a1.rows as row_count, (a1.reserved + ISNULL(a4.reserved,0))* 8 AS reserved, a1.data * 8 AS data, (CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 AS index_size, (CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 AS unused FROM (SELECT ps.object_id, SUM ( CASE WHEN (ps.index_id < 2) THEN row_count ELSE 0 END ) AS [rows], SUM (ps.reserved_page_count) AS reserved, SUM ( CASE WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count) ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END ) AS data, SUM (ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps WHERE ps.object_id NOT IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1) GROUP BY ps.object_id) AS a1 LEFT OUTER JOIN (SELECT it.parent_id, SUM(ps.reserved_page_count) AS reserved, SUM(ps.used_page_count) AS used FROM sys.dm_db_partition_stats ps INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id) WHERE it.internal_type IN (202,204) GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id) INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id ) INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id) WHERE a2.type <> N'S' and a2.type <> N'IT' ORDER BY a3.name, a2.name
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.