Shrinking Data Files

Question

Shrinking Data Files

coybernardo

SSC Rookie

Points: 25
More actions
February 14, 2012 at 7:03 pm

#250748

Hello,
We are trying to shrink our database from 2.4TB to 1.5TB to release space on our drives. Most of the data is on one data file. We ran the operation below and we specify using TRUNCATEONLY so that the shrink operation doesn’t get logged.
The operation completed but the OS or SQL Server has not given back the space where in My Computer the drive still reflects the original size.
Any feedback on why the SHRINK operation would not fully complete?
We’ve investigated many angles, but still haven’t been able to find the source of the issue. Any feedback would help.
Thanks,
Coy Bernardo
USE [UMG_GIMM_EDW]
GO
DBCC SHRINKFILE (N’UMG_GIMM_EDW_FG1_Dat1′ , 2331220, TRUNCATEONLY)
GO

Viewing 5 posts - 1 through 4 (of 4 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

coybernardo (2/14/2012)
Hello,
We are trying to shrink our database from 2.4TB to 1.5TB to release space on our drives. Most of the data is on one data file. We ran the operation below and we specify using TRUNCATEONLY so that the shrink operation doesn’t get logged.
The operation completed but the OS or SQL Server has not given back the space where in My Computer the drive still reflects the original size.
Any feedback on why the SHRINK operation would not fully complete?
We’ve investigated many angles, but still haven’t been able to find the source of the issue. Any feedback would help.
Thanks,
Coy Bernardo
USE [UMG_GIMM_EDW]
GO
DBCC SHRINKFILE (N’UMG_GIMM_EDW_FG1_Dat1' , 2331220, TRUNCATEONLY)
GO

From BOL

TRUNCATEONLY

Causes any unused space in the files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_size is ignored when TRUNCATEONLY is used.

So in effect its only trimming the amount of white space from the right side of the file, which probably isnt going to be alot if the file is full.

I would do this

USE [UMG_GIMM_EDW]

GO

select

name,

cast((size/128.0) as int) as TotalSpaceInMB,

cast((cast(fileproperty(name, 'SpaceUsed') as int)/128.0) as int) as UsedSpaceInMB,

cast((size/128.0 - cast(fileproperty(name, 'SpaceUsed') AS int)/128.0) as int) as FreeSpaceInMB

from

sys.database_files

This will see how much space is free/used then you can shrink accordingly, if there is no free space, then you will need to drop/delete tables data etc to reduce the amount of space then shrink

DBCC SHRINKFILE (N’UMG_GIMM_EDW_FG1_Dat1' , 1500000)

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

I hope you're aware of the negative effects of shrinking a data file?

If you must shrink a datafile you should rebuild all the indexes afterwards to get all the data unfragmented.

on those occasions I do shrink a file I just use dbcc shrinkfile(1,<size here>);

where 1 is the file number you require.

It's always best to put the database into simple recovery, run dbcc updateusage and checkpoint before trying to shrink. Then probably go and get a coffee, visit the gym, have lunch !! shrinks are never quick I find.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

coybernardo SSC Rookie Points: 25 More actions · Answer 3

Thanks Anthony, that really explains what's happened, where our process worked as it only released the space at the end of the file but it's stopped as there was no more space to release at the end of the file. We'll use the NOTRUNCATE option then the TRUNCATEONLY to rearrange the pages and then trim the new free space.

And thanks The GrumpyOldDBA on the input. I understand the negatives on shrinking but just don't have a choice. And good point on rebuilding indexes. We'll be planning that as well.

coybernardo SSC Rookie Points: 25 More actions · Answer 4

Thanks. It clearly explains what was happening.

We're going through the shrinking process now where we are doing a shrink with NOTRUNCATE in small increments. I've noticed that it is a very time consuming process where rearranging the data in small increments is really the only way to shrink a data file with NOTRUNCATE.

I've noticed that the TRUNCATEONLY goes rather quickly, which makes sense.

Thanks again.

Coy