I'm trying to get to the bottom of a size issue with a TempDB. TempDB consists of two data files - an 8MB primary (no autogrowth), and a 9.5 GB secondary (set to grow no larger than 10 GB).
The problem is that the available space, as reported by Perfmon ("Free Space in tempdb (KB)") and "unallocated_extent_page_count" from sys.dm_db_file_space_usage is 3383 MB. This indicates that 6300 MB is being used inside TempDB.
Examining sys.dm_db_file_space_usage reports the following figures for the secondary TempDB database (the first is neligible, being 8 MB):
unallocated_extent_page_count: 433000 (3383 MB)
version_store_reserved_page_count: 64 (0.5 MB)
user_object_reserved_page_count: 64 (0.5 MB)
internal_object_reserved_page_count: 320 (2.5 MB)
mixed_extent_page_count: 812120 (6344 MB)
So, obviously the space is being taken by mixed extents. But what? sys.dm_db_task_space_usage and sys.dm_db_session_space_usage do not show any space being used. There are no objects with large number of pages in their indexes. sp_spaceused
reports 2 MB reserved (which makes sense, as it's derived from sys.dm_db_partition_stats).
The only other clue I have is that the Version Store was continually growing, and filled TempDB, resulting in a Version Store shrink operation. The version store size was up to 3891 MB, and the followin
View Complete Post