question

Wilfred van Dijk avatar image
Wilfred van Dijk 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?
administrationshrink-databasedatafiles
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ozamora avatar image
ozamora answered
You shrunk with TRUNCATEONLY? What if you create another file for PRIMARY and perform a DBCC SHRINKFILE with EMPTYFILE on the original file?
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Wilfred van Dijk avatar image Wilfred van Dijk commented ·
Can't actually "play" on a live system ... And I want to know what's causing this problem
0 Likes 0 ·
Scot Hauder avatar image
Scot Hauder answered
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
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

WilliamD avatar image WilliamD commented ·
He is querying sysindexes - it wan't clear, so I reformatted is question
0 Likes 0 ·
Wilfred van Dijk avatar image Wilfred van Dijk commented ·
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.
0 Likes 0 ·
Scot Hauder avatar image Scot Hauder commented ·
0 Likes 0 ·
Wilfred van Dijk avatar image
Wilfred van Dijk answered
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 desc
it seems to be LOB_DATA.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.