asked
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?
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
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.
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.
