Primary datafile bloated with nothing - can't shrink

Question

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?

Answer 1

0

You shrunk with TRUNCATEONLY?

What if you create another file for PRIMARY and perform a DBCC SHRINKFILE with EMPTYFILE on the original file?

answered Dec 03 '10 at 07:07 AM

ozamora
1.4k ● 2 ● 3 ● 5

Can't actually "play" on a live system ... And I want to know what's causing this problem

Dec 03 '10 at 11:47 PM Wilfred van Dijk

(comments are locked)

Answer 2

0

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

answered Dec 03 '10 at 12:23 PM

Scot Hauder
5.7k ● 13 ● 15 ● 18

He is querying sysindexes - it wan't clear, so I reformatted is question

Dec 03 '10 at 01:02 PM WilliamD

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.

Dec 03 '10 at 11:47 PM Wilfred van Dijk

See Method 1 http://support.microsoft.com/kb/937531

Dec 04 '10 at 01:11 AM Scot Hauder

(comments are locked)

Answer 3

0

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.

answered Dec 05 '10 at 08:28 AM

Wilfred van Dijk
549 ● 13 ● 18 ● 20

(comments are locked)

Primary datafile bloated with nothing - can't shrink

Follow this question

Related Questions