missing storage space

Question

missing storage space

MVDBA (Mike Vessey)

SSC-Insane

Points: 21797
More actions
November 18, 2019 at 4:55 pm

#3698587

Hi
I have a particular drive on one of my sql servers, it's gotten very full - there are no log files involved, this is purely data
within the filegroup (primary) there are only 2 tables (everything else is in other filegroups)
dtproperties
sysdiagrams
These are tiny - when I do a dbcc shrinkfile, I get
currentsize = 6176832, minimumsize=63, usedpages =6176832
but I can't seem to reclaim the space - it looks like someone moved tables from the primary filegroup but we can't reclaim the space.. I've had this before with heaps and using dbcc cleantable,, but i'm unsure where to go now as it's just not reclaiming the space
any help is much appreciated

MVDBA

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

This is because of one of "those" attempts by Microsoft to fix performance issues. This particular issue is caused by a "performance enhancement that allocates complete extents (up to 4, IIRC). It shouldn't be confused with what Trace Flag 1118 used to do (now done auto-magically in all databases where it hasn't been disabled). It's different.

It has to do with "INSERT BULK", which comes from the front end.

To make a much longer story shorter, the extents are allocated anytime an "INSERT BULK" occurs (not to be confused with a BULK INSERT) or similar type of action. To make things go faster, MS made it so that the action doesn't check to see if there's any room in pages and extents... it just blindly allocates new extents for EVERY SUCH ACTION EVEN IF THAT ACTION IS A SINGLE ROW INSERT!

May the fleas of a thousand camels infest the nether areas of the "geniuses" that thought of and caused that "improvement" to be realized.

There's only two fixes that I know of...

Change all of the GUI code that causes this to happen. Obviously, there are several serious problems with doing that, so this isn't really an option.
Implement Trace Flag 692 (I've enabled it on my "big" production server as of 3 weeks ago and am going to enable it on everything else this week). Enabling that trace flag will turn that "improvement" off. Be advised that it's server-wide in scope but also be aware that's probably not a bad thing. I'll dig out the code (when I get home tnight) that I wrote that proves that it's actually a problem on most of your databases.

If you have the WhatsUp Gold monitoring product (and I suspect many other monitoring products just due to their nature), whatever database you're using to support that product (usually "WhatsUp" by default) is going through this in spades. You just might not notice if you have index maintenance running on it. It IS causing REORGANIZE (which you shouldn't be using except for very special reasons but certainly not as a general practice in index maintenance) to beat the crap out of itself and your log file trying to get rid of the extra space.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 2

thanks dude - that got me a lot back, but it's still not right - 155GB for for 2 system tables... any other clues ?

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 3

I found the issue

I had grabbed several scripts to find the objects in that filegroup - such as

SELECT OBJECT_SCHEMA_NAME(t.object_id) AS schema_name
,t.name AS table_name
,i.index_id
,i.name AS index_name
,p.partition_number
,fg.name AS filegroup_name
,FORMAT(p.rows, '#,###') AS rows
FROM sys.tables t
LEFT outer JOIN sys.indexes i ON t.object_id = i.object_id
LEFT outer JOIN sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
LEFT OUTER JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
LEFT OUTER JOIN sys.destination_data_spaces dds ON ps.data_space_id=dds.partition_scheme_id AND p.partition_number=dds.destination_id
LEFT outer JOIN sys.filegroups fg ON COALESCE(dds.data_space_id, i.data_space_id)=fg.data_space_id
ORDER BY FILEGROUP_NAME desc

the problem is it gave incorrect results and I trusted the internet TOO MUCH

a much better script is

SELECT DS.name AS DataSpaceName
,f.physical_name
,AU.type_desc AS AllocationDesc
,AU.total_pages / 128 AS TotalSizeMB
,AU.used_pages / 128 AS UsedSizeMB
,AU.data_pages / 128 AS DataSizeMB
,SCH.name AS SchemaName
,OBJ.type_desc AS ObjectType
,OBJ.name AS ObjectName
,IDX.type_desc AS IndexType
,IDX.name AS IndexName
FROM sys.data_spaces AS DS
INNER JOIN sys.allocation_units AS AU
ON DS.data_space_id = AU.data_space_id
INNER JOIN sys.partitions AS PA
ON (AU.type IN (1, 3)
AND AU.container_id = PA.hobt_id)
OR
(AU.type = 2
AND AU.container_id = PA.partition_id)
JOIN sys.database_files f
on AU.data_space_id = f.data_space_id
INNER JOIN sys.objects AS OBJ
ON PA.object_id = OBJ.object_id
INNER JOIN sys.schemas AS SCH
ON OBJ.schema_id = SCH.schema_id
LEFT JOIN sys.indexes AS IDX
ON PA.object_id = IDX.object_id
AND PA.index_id = IDX.index_id
WHERE AU.total_pages > 0 AND f.physical_name LIKE 'Q:%'
ORDER BY AU.total_pages desc

the original author (good script) posted it at https://www.sqlrx.com/list-all-objects-and-indexes-in-a-filegroup-or-partition/

I'm not sure what the issue is with the script, but for now I'm happy that I can start transferring indexes and tables to a different filegroup.

MVDBA

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 4

Just to confirm... Are you saying that the first script reported 155GB and that was what was incorrect?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

Uh... I just noticed... the first script doesn't even report sizes... so I'm confused as to what you found the problem to actually be.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 6

the first script didn't report any objects other than the system tables. (although it was fine for other file groups)

I haven't looked into it, but the objects it missed all have Large binary objects

don't use the first script 🙂

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 7

the database size was from dbcc shrinkfile and the size on disk through RDP

MVDBA