dbcc shrinkfile does not shrink mdf

Question

dbcc shrinkfile does not shrink mdf

timwilliams

Ten Centuries

Points: 1007
More actions
August 5, 2002 at 11:09 am

#57860

I have seen several posts on log files that wont shrink - and remember reading an excellent article by Karen Delany on the subject.
However I now have an MDF that will not shrink
- over 95% of the used file space is free
- SQL 7.0
- Not trying to shrink to smaller than its original size
- Not trying to shrink to smaller than the size in model db
- dbcc shrinkfile says it has succeeded - but I see no change.
Any thoughts ?
Tim

Viewing 15 posts - 1 through 15 (of 25 total)

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

timwilliams Ten Centuries Points: 1007 More actions · Answer 1

I feel like Billy no mates by replying to my own thread!

I have just found out that this database was upgraded to SQL7 from 6.5 - could this be why it will not shrink? I can find nothing to support this - has anyone had a simelar experience with an upgraded database?

Thanks

Tim

Andy Warren SSC Guru Points: 119902 More actions · Answer 2

Shrinkfile often isnt enough, has to do with virtual log segments. We've got a script on the site that will force the shrink:

http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=26&CategoryNm=Maintenance%20and%20Management%20&CategoryID=1

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

timwilliams Ten Centuries Points: 1007 More actions · Answer 3

Andy,

Thanks for that -

But in this case it is not the transaction log that is the problem - its the data file

Tim

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 4

Have you tried running DBCC SHRINKFILE with first the NOTRUNCATE option to move all the data to the front of the file followed by running it again with TRUNCATEONLY to cause the unused space to be released?

K. Brian Kelley

bkelley@sqlservercentral.com

http://www.sqlservercentral.com/columnists/bkelley/

K. Brian Kelley
@kbriankelley

timwilliams Ten Centuries Points: 1007 More actions · Answer 5

Brian,

I just gave that a go --

DbId FileId CurrentSize MinimumSize UsedPages EstimatedPages

------ ------ ----------- ----------- ----------- --------------

7 1 298288 2520 1784 1784

(1 row(s) affected)

I give in - I am going to use DTS to create a copy of the database, delete the original and attach the new mdf

Thanks anyway

Tim

Andy Warren SSC Guru Points: 119902 More actions · Answer 6

I shouldnt answer posts early in the morning.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Andy Warren SSC Guru Points: 119902 More actions · Answer 7

I shouldnt answer posts early in the morning.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

pmoscattini SSC Enthusiast Points: 139 More actions · Answer 8

We have the same problem, unfortunately I can't take your approach i.e. new database and copy as used space = 19Gb, free = 15Gb. Does anyone have any other ideas.

I have tried dbcc shrinkdatabase, shrinkfile, with truncate without truncate and nothing seems to work

Thanks,

Paolo

DannyShu SSC Rookie Points: 43 More actions · Answer 9

I have encounter this problem before.

Backup your db first,

then turn on following database option

auto close

auto shrink

truncate log on checkpoint

then wait a monment. maybe this can solve your problem.

Edited by - dannyshu on 08/15/2002 12:44:27 AM

Greg Milner SSCrazy Points: 2425 More actions · Answer 10

I use a command in a regular job like this:

DBCC SHRINKFILE (MYDB_data, 1000)

... and it seems to work. The database (.mdf) in question routinely grows to over 2 gigs and can never shrink below about 1.4 gig even though my shrinkfile is set for 1 gig. It seems that it will only go as low as it can and that's it. As the users add mor data, I don't doubt it will grow to say 5 gigs and never shrink below, say, 3 gigs.

quote:
I have seen several posts on log files that wont shrink - and remember reading an excellent article by Karen Delany on the subject.
However I now have an MDF that will not shrink
- over 95% of the used file space is free
- SQL 7.0
- Not trying to shrink to smaller than its original size
- Not trying to shrink to smaller than the size in model db
- dbcc shrinkfile says it has succeeded - but I see no change.
Any thoughts ?
Tim

G. Milner

timwilliams Ten Centuries Points: 1007 More actions · Answer 11

A quick update...

Earlier this week - I made a copy of the database using DTS - compared the two databases with RedGate SQLCompare and DataCompare. Detached both databases, and reattached the new mdf with the old database name.

The new mdf expands and shrinks with no problem!

Thanks

Tim

pmoscattini SSC Enthusiast Points: 139 More actions · Answer 12

Tim,

You didn't mention if RedGate came back with any differences. My database is 34.2Gb total - 20.8G used and 13.3G Unused. I ran "DBCC UPDATEUSAGE(0)" and it came back with lots of errors. Tried to shrink again using shrinkfile/shrink database with and without truncate - still nothing happens. I am reluctant to use DTS to create a new copy as I guess it will take a very long time to copy ( comparing DTS speed to BCP ). Can you think of anything else other than your solution that might help. Also I am a developer not a DBA so please explain how you detach/attach the mdf to the database name. Do you mean updating the 'filename' column in sysfiles.

Many Thanks,

Paolo

P.S. - Glad to see someone else has had this problem, I have another 20+ databases and none of the others have this problem. I thought I was going Mad!

timwilliams Ten Centuries Points: 1007 More actions · Answer 13

Paolo,

Thanks for the reply-

To clarify the Redgate software found no differences between the two databases; '=' signs all over the place.

The amount of used space in the file was less than 20MB - which is why I used DTS - I would still use DTS even if the database had been far bigger.

An alternative could have been to generate scripts for all objects in the database - use this to create the new database and the BCP all data into this new database

More information on attach/detach:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_da-di_83fm.asp

and

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ae-az_52oy.asp

Tim

scotlinkon SSC Enthusiast Points: 179 More actions · Answer 14

I've had this problem on a 16GB DB here. I actually had to stop and restart the SQL Server Service to get it to shrink to a specific size. I had two data files and wanted to shrink the primary file that was on a seperate disk. I migrated the data over using the emptyfile option which made 9MB of space used in a 4GB file. I then tried to shrink it and it reported successful. However, it did not shrink. Once I restarted the service, it shrank. Weird but it worked.