dbcc shrinkfile does not shrink mdf

  • 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

  • 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,

    Thanks for that -

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

    Tim

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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

  • 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!

  • 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

  • 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.

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

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