Primary datafile bloated with nothing - can't shrink
Hi, I have the following situation: Database with 1 datafile in primary filegroup, 16 datafiles in 3 other filegroups. There is no userdata in the primary filegroup. This database is a principal in an async mirror. The primary datafile is currently 90GB (yes gigabyte) keeps growing, but when I query sysindexes select sum(dpages)/128 from sysindexes where groupid=1 results in 307MB. I tried to shrink this file, but it could only shrink to 83 GB. I assume mirroring is causing this, but this is not normal. There are som queue_message* files but these are empty. Any ideas what's filling my PRIMARY datafile and how to get rid of it?
Are you querying from sysindexes? Try looking in database_files and let us know what it returns for the primary filegroup SELECT name, size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 [MBFree] FROM sys.database_files WHERE type = 0
The free space is reported by this query is the same as I see in SSMS. Note: I use sysindexes because of the dpages column (which is not in sys.indexes). I got some more info when querying sys.allocation_units, but I need to examine this resultset.
You shrunk with TRUNCATEONLY? What if you create another file for PRIMARY and perform a DBCC SHRINKFILE with EMPTYFILE on the original file?
Can't actually "play" on a live system ... And I want to know what's causing this problem
I think I found the answer. According to this query:
select sa.*, sp.*, object_name(sp.object_id) from sys.allocation_units sa join sys.partitions sp on sa.container_id = sp.hobt_id where sa.data_space_id = 1 order by sa.total_pages descit seems to be LOB_DATA.
