Post reply

unused space

  • May 15, 2003 at 4:27 am

    #159600

    I do have text in one file only (madison_data.mdf) and I run the following

    query and unused space came extremely down but file size remains around 4,5GB:

    USE MADISON

    DBCC SHRINKFILE('MADISON_DATA',EMPTYFILE)

    I checked the Microsoft note from bbychkov (see above) and according to this note:

    The DBCC SHRINKFILE and DBCC SHRINKDATABASE commands will not move data and reduce the file size if the EstimatedPages value equals the MinimumSize value that is reported when the command completes.

    In this artice there's a query you can run to find out if this is the case.

    For my db it was the case. I run the query above and unused space came dramatically down but as said above file size is still quite big. I wanted to run another query as described in this Microsoft article:

    When the DBCC SHRINKFILE query completes, run an ALTER DATABASE query to remove the old files from the database.

    But I can not remove an old file when there's only one file.

    I tried the following query:

    DBCC SHRINKDATABASE ('MADISON',10)

    DBCC SHRINKDATABASE (MADISON',10,TRUNCATEONLY)

    which did not succeed.

    Another engineer told that shrinking occurs on a deferred basis so it can take a couple of days including a couple of restarts of the SQL server before alle space colleage is freed.

    I think SHRINKING a db is a subject which never exhausts and many of us are urged to post there questions as it is never clear!

    Thanks

    mipo

  • May 15, 2003 at 7:24 am

    #457259

    EMPTYFILE is when you have multiple datafiles and want for example to delete one, so you use emptyfile and the data of that file is spread between al the other files.

    To remove unused space use TRUNCATEONLY

Viewing 2 posts - 1 through 1 (of 1 total)

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