November 14, 2008 at 1:36 pm
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:
November 14, 2008 at 1:39 pm
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.
November 17, 2008 at 5:36 am
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
November 18, 2008 at 8:04 am
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>DATABASEI 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
November 18, 2008 at 8:27 am
Cheers for the feedback, very interesting article indeed! I have modified my plan accordingly, removing the shrinking altogether...
November 18, 2008 at 8:35 am
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
November 18, 2008 at 8:39 am
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
November 18, 2008 at 8:43 am
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.
November 18, 2008 at 9:42 am
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