x

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?
more ▼

asked Dec 03, 2010 at 04:39 AM in Default

Wilfred van Dijk gravatar image

Wilfred van Dijk
1.3k 19 23 30

(comments are locked)
10|1200 characters needed characters left

3 answers: sort oldest

You shrunk with TRUNCATEONLY?

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

answered Dec 03, 2010 at 07:07 AM

ozamora gravatar image

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, 2010 at 11:47 PM Wilfred van Dijk
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Dec 03, 2010 at 12:23 PM

Scot Hauder gravatar image

Scot Hauder
6k 13 15 18

He is querying sysindexes - it wan't clear, so I reformatted is question
Dec 03, 2010 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, 2010 at 11:47 PM Wilfred van Dijk
Dec 04, 2010 at 01:11 AM Scot Hauder
(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Dec 05, 2010 at 08:28 AM

Wilfred van Dijk gravatar image

Wilfred van Dijk
1.3k 19 23 30

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x126
x37
x10

asked: Dec 03, 2010 at 04:39 AM

Seen: 3804 times

Last Updated: Dec 03, 2010 at 01:01 PM