unused space in db of SQL server 2000

  • I am wondering why there is so much unused space in my db. The backup is only 230MB (zipped) but if restored it became almost 6GB. I realized there is a lot of unsed space hanging around.

    I checked with sp_spaceused and got following:

    database_name database_size unallocated space

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

    madison 6755.13 MB 326.26 MB

    reserved data index_size unused

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

    6581624 KB 845880 KB 363184 KB 5372560 KB

    After executing the following:

    EXEC sp_msforeachtable 'DBCC DBREINDEX (''?'')'

    DBCC SHRINKDATABASE ('MADISON')

    DBCC UPDATEUSAGE ('MADISON')

    Only minor size reduction gained:

    database_name database_size unallocated space

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

    madison 5614.69 MB 1100.02 MB

    reserved data index_size unused

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

    4622000 KB 675928 KB 237656 KB 3708416 KB

    So from 5,3 GB to 3,7 Gb it is not a lot freed unused space and the question is why is it not freed or is it just the way db handles space? What is the server hiding? Autoshrink is on in option tab. Anything else that could be done or checked?

    Thanks

    mipo

  • Try to run this query to see reserved/used per table. Then look for "text" fields (ntext, image).

    SELECT id, SUM(reserved) AS Reserved, SUM(used) AS Used, SUM(reserved - used) * 8 AS Unused,

    (SELECT name FROM sysobjects WHERE id = si.id) AS TableName

    FROM sysindexes si

    WHERE (indid IN (0, 1, 255))

    GROUP BY id

    ORDER BY Unused DESC

  • Thanks for the input.

    My concern is really the unused space and how it will be released.

    We run several times the script:

    EXEC sp_msforeachtable 'DBCC DBREINDEX (''?'')'

    DBCC SHRINKDATABASE ('MADISON')

    DBCC UPDATEUSAGE ('MADISON')

    and unused space came down below 1MB. The entire db size came down to 500MB!

    I do not understand why we can run several times this script to get a better result?

    Why not in one go?

    We use Auto-growth by 10% and unrestricted file growth for this db.

    I do not have a specified file size and so what would be the target size in this case?

    Can I chose a free value in MB maybe not realistic?

    It is not quite transparent what SQL server does behind and how he manages his dbs!!.

    Thanks

    mipo

  • I think you are backup up without overwriting old media....

    This means your bak file grows until a backup set expires (if the option is on)

  • ehh, sorry I didn't read good what you wrote...

    Try to take a look in the taskpad or try these two:

    sp_helpdb

    sp_spaceused

  • Check this article from Microsoft:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;324432

    Also, run DBCC SHOWCONTIG to see what's fragmented in your database.

  • With respect to that MS support note about blob extents being fragmented, would it help to shrink in Ent Manager with the "Move pages to beginning of file" check-box checked to get rid of this fragmentation & have near-full extents?

    On a related matter, why is the "Move pages to beginning of file" option not supported in DBCC SHRINKFILE or SHRINKDATABASE commands ?

  • From running Profiler you can see that when you check "Move pages" checkbox EM executes:

    DBCC shrinkdatabase('yourdb', 10).

    When you leave this box unchecked the command executed is:

    DBCC shrinkdatabase('yourdb', 10, TRUNCATEONLY )

  • Thank you, that's a good tip to know,using profiler.

    My question is, is there a simpler workaround to defrag blob extents that doesn't involve exporting data & dropping tables? Will a backup and restore do the trick?

  • No, backup and restore won't help.

    If you really don't want to drop and re-create the table you can try to create a clustered index if you don't have one already. If you do have a clustered index try to run dbcc dbreindex on it.

    Also, if it's SQL 2000 you can look at 'text in row' option. Read the limitations and side effects to make sure your application can work with that option.

  • To add to this... DBCC ShrinkDB does not recover space no longer used by text and image data if they are stored in the same filegroup as the data.

    The only way I have found to do this is by creating another filegroup, move the offending table to this new filegroup, shrink the database and then move it back.

  • 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

  • You an try these:

    use madison

    BACKUP LOG madison WITH TRUNCATE_ONLY

    go

    use madison

    go

    dbcc shrinkfile (madison_log,400)

    go

    Take the db backup after these commands.

    Thanks. Sanjay

  • not sure if this will be the answer to your question but i've just had a similar problem. essentially, i had heavily fragmented secondary non clustered indexes... use dbcc showcontig to check the extent of the fragmentation... i created a clustered index on the table to bring everything back into line...

  • Could you give me more details about how handled your problem and how you created the clustered index etc.

    Thanks mipo

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

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