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