*.MDF is 36+gb but actual data in DB is around 15gb - why???

  • Looking for an explination as to why my *.MDF on a given DB would be around 36GB but the actual size of tables in the DB is about 15GB. Indexes are in a sepearte file group/file so that is not the reason why.

    Any help/suggestoins would be breately appreciated.

    Thanks :unsure:

  • There are several possible reasons for that condition.

    1. The size of the database when first created had an initial size set to the 36gb. So it is just filling as it goes.

    2. The growth setting on the file settings may be set to grow at some large amount (unlikely.. but you never know).

    3. The database contained more data at one point and data has been removed. Thereby the file grew to accomidate and after the delete it does not release the space.

    There are additional possible causes, but these would be the most likely.

  • If you want to recover this additional space taken, then check the db growth first (otherwise the space you recover may just get re allocated) I usually have this get to grow 10% unrestricted, but dependant on how much data you have going into your db you may want more or less growth. Then backup your db, and then shrink. To shrink you db; Right Click on DB you want to shrink>TASKS>SHRINK>DATABASE

    I usually do this as part of a daily checklist;

    1. Back Up DB

    2. Re-Index

    3. Shrink

  • Alasdair Thomson (11/17/2008)


    If you want to recover this additional space taken, then check the db growth first (otherwise the space you recover may just get re allocated) I usually have this get to grow 10% unrestricted, but dependant on how much data you have going into your db you may want more or less growth. Then backup your db, and then shrink. To shrink you db; Right Click on DB you want to shrink>TASKS>SHRINK>DATABASE

    I usually do this as part of a daily checklist;

    1. Back Up DB

    2. Re-Index

    3. Shrink

    DO NOT DO THIS!! Shrinking data files is a no-no. It causes LOTS of issues: external file fragmentation as the database grows back out with new data, processing delays during this growth, severe internal object fragmentation, no free space for index maintenance to lay down pages in sequential order, etc. Your plan is even worse in that it does the reindex first and THEN moves the pages all out of order with the shrink operation.

    See here for some more details: http://www.karaszi.com/SQLServer/info_dont_shrink.asp

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • Cheers for the feedback, very interesting article indeed! I have modified my plan accordingly, removing the shrinking altogether...

  • Being even more proactive what you should do (and what I advise all my clients to do as a sql server consultant) is to grow your database to the size it will be 12-18 months from now with expected data growth. Don't forget to take indexes into account! Then reevaluate every 3 months or so. Autogrowth should be an exceptional occurrence, not standard operating procedure.

    Oh, and you probably want to check your OS level file fragmentation. If you find data and log files are severely fragmented, find a maintenance window, shut down sql server and defrag the hard drive(s) that the files are on. You can get back significant throughput doing this!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

  • in addition to all the existing valid comments : -

    you should note that table sizes will be misreported by sp_spaceused if there is no clusted index on the table.

    one other possible (but unlikely) option is that if you have had tables that included a varchar/nvarchar column that was dropped then the space allocated to the varchar column is not released.

    this does not apply to char or text columns

    you can reclaim the space using DBCC Cleantable

    MVDBA

  • We do actually run some server maintenance tasks weekly, such as disk clean up, defrag etc, but ironic that the benefits that I get from a defrag were probably outweighed by the shrinking after re-index.

  • Alasdair Thomson (11/18/2008)


    We do actually run some server maintenance tasks weekly, such as disk clean up, defrag etc, but ironic that the benefits that I get from a defrag were probably outweighed by the shrinking after re-index.

    I wouldn't be surprised if that isn't the case.

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

Viewing 9 posts - 1 through 8 (of 8 total)

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