DBCC SHRINKFILE do not shrink space at all

Question

DBCC SHRINKFILE do not shrink space at all

Carl B.

SSCertifiable

Points: 7931
More actions
September 11, 2015 at 12:32 pm

#307615

Hi everyone,
We are in a process of moving TEXT data from a production server to SharePoint. Basically we are putting a small 3 bytes value instead of the value that was previously there in each rows of a table containing approx. 1.7 millions records.
Now when we try to shrink that file, the shrink window of SQL Server notify us that almost 100GB can be reclaimed but when we launch the dbcc shrinkfile, no space is given back to the O.S. at all.
We tried to execute a ALTER INDEX ... reorganize WITH ( LOB_COMPACTION = ON ) but it do not help in shrinking the file.
Can someone shed some light on this?
Best regards.
Carl

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

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You probably need to rebuild the index (not reorganise, rebuild).

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Carl B. SSCertifiable Points: 7931 More actions · Answer 2

I Gail,

The index rebuild did not help for this command:

DBCC SHRINKFILE (N'big_db' , 0, TRUNCATEONLY)

But instead, I found that using this command was actually shrinking the file:

DBCC SHRINKFILE (N'big_db' , 311140)

But the later is extremely slow to give back even small chunks to the O.S. (10MB to 100MB : It once took more than 50 minutes to shrunk 100MB).

Any hint to speed theses shrink would be really appreciated.

Best regards.

Carl

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 3

Get faster disks. I don't know a better way to do this.

You could maybe create a new filegroup, move the table, then shrink the first file, then try to move it back. However not sure that's really quicker, or it would help.

Carl B. SSCertifiable Points: 7931 More actions · Answer 4

Steve Jones - SSC Editor (9/15/2015)
Get faster disks. I don't know a better way to do this.
You could maybe create a new filegroup, move the table, then shrink the first file, then try to move it back. However not sure that's really quicker, or it would help.

Hi Steve,

The disks are not the bottleneck at all here...

Your idea to "create a new filegroup, move the table, then shrink the first file, then try to move it back" was something I was thinking of yesterday but I was not sure if it would help at all.

Thank's for your input Steve.

Carl B. SSCertifiable Points: 7931 More actions · Answer 5

I am not 100% sure but I think that having rebuild all the indexes help us to shrink the database with this command:

DBCC SHRINKDATABASE(N'BIG_DB')

At the end, without the option to reorganize pages, only 800MB was left unused in the .mdf file.

Best regards.

Carl

Carl B. SSCertifiable Points: 7931 More actions · Answer 6

Rectification: The indexes were not rebuilt at all. They were rebuilt only in another database. It was a mistake. So the fact that the DBCC SHRINK DATABASE worked is not related to non fragmented indexes.

Regards.

Carl