none
Large MDF files whether to reduce

    Question

  • Hi I noticed our DB MDF is large over 500GB. I need to reduce the size. I am not sure its really required size or unnecessary space used. WHEN DB SHRIK  then lead to more fragmentation. I rebuild indexes . But MDF size remain same. any option do we have rather going to DB SHRIK to reduce MDF file size. Through Management Studio Shrink DB output as follows. But I don't need to do as heading to fragmentations. How to reclaim space alternatively while reduce size of MDF   

    many thanks

     
    • Edited by ashwan Monday, July 2, 2018 11:40 AM
    Monday, July 2, 2018 10:56 AM

All replies

  • Why do you really want to do this? 

    Make sure you read this

    https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, July 2, 2018 11:15 AM
  • <g class="gr_ gr_324 gr-alert gr_gramm gr_inline_cards gr_run_anim Punctuation only-ins replaceWithoutSep" data-gr-id="324" id="324">Hi</g> <g class="gr_ gr_10 gr-alert gr_spell gr_inline_cards gr_disable_anim_appear ContextualSpelling ins-del multiReplace" data-gr-id="10" id="10">ashwan</g>, there are a couple things you can do to reduce the <g class="gr_ gr_69 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="69" id="69">mdf</g> file:

    - check for unused indexes that can be dropped (look on the system <g class="gr_ gr_767 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="767" id="767">dms</g> index usage stats to view the least used indexes and their size)

    - check the largest tables in the database (get <g class="gr_ gr_388 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del" data-gr-id="388" id="388">a t</g>-<g class="gr_ gr_389 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="389" id="389">sql</g> script to list all tables by size and <g class="gr_ gr_1897 gr-alert gr_spell gr_inline_cards gr_run_anim ContextualSpelling ins-del multiReplace" data-gr-id="1897" id="1897">rowcount</g>), work with the application owners to identify unnecessary data/ tables

    - check for unnecessary large data sizes

    Shrink should be used only in an emergency response situation

    Monday, July 2, 2018 11:26 AM
  • Tiago not clear what it is. Can you repost it please
    Monday, July 2, 2018 11:41 AM
  • Hi I noticed our DB MDF is large over 500GB. I need to reduce the size. I am not sure its really required size or unnecessary space used. WHEN DB SHRIK  then lead to more fragmentation. I rebuild indexes . But MDF size remain same. any option do we have rather going to DB SHRIK to reduce MDF file size. Through Management Studio Shrink DB output as follows. But I don't need to do as heading to fragmentations. How to reclaim space alternatively while reduce size of MDF   

    You mentioned the MDF is 500 GB but the screenshot shows 222 GB..? Either way, a database is bound to grow so there is little point in shrinking it when you know it'll eventually grow again. Moreover, shrinking a data file causes pages to move to the front of the file and thus causing massive fragmentation. But if you know what you are doing and really want to reclaim space to OS then see my response to another thread below:

    if you really want to shrink your database, then you can either run DBCC SHRINKDATABASE or DBCC SHRINKFILE. SHRINKDATABASE would shrink all files but doesn't shrink below the minimum size that was set at the time the database was created (or the last time the file was shrunk). So, if you want to shrink smaller than that then go with SHRINKFILE. As a note of caution, shrinking a data file causes massive page movements and ends up with lots of fragmentation. 

    There's really no direct alternative to shrink. One way that I can think of is to create a new DB with less size and migrate all objects (if the list is relatively small) from the current to the new DB. 

    Hope this helps.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Monday, July 2, 2018 12:00 PM
  • You can reduce the physical file data file size with DBCC SHRINKFILE with the TRUNCATE_ONLY option (https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql), specifying the logical file name of your mdf file (which is the same name as the database by default) and the desired target size. For example, this command will shrink the file down to 30GB, if possible.

    USE YourDatabase;

    DBCC SHRINKFILE ('YourDatabase', 30720, TRUNCATE ONLY);

    This will release unused space at the end of the file up to the specified target size but will not move data pages (causing fragmentation). However, if there are used pages in the file greater than the specified size, the file size will not be fully reduced to the specified size.

    The open question is why there is so much unused space. It could be that the database was created much larger than needed initially, or that there were once very large tables that have since been dropped. Shrinking the file as described above is appropriate in these cases (assuming large tables will not be recreated), and using the TRUNCATE_ONLY option will not introduce fragmentation. 

    However, if significant additional data will be inserted, you should make sure the file is sufficiently large to accommodate the new data plus additional free space to allow index rebuilds (about 120%) of your largest table.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com


    Monday, July 2, 2018 12:19 PM
    Moderator
  • I have done several restructurings of the physical database. simply running DBCC SHRINKFILE can take forever, not to mention seriously fragmenting existing structures.

    What always do is create additional filegroups, rebuild (move) indexes, including the clustered index to other filegroups for the large (and even medium) tables. Shrink the file on what's left is quick.

    Depending on what the original purpose is, set the file size correctly, then rebuild the indexes again to the final desired FG.

    The usual reason that I have restructured the database physical architecture is so that large FG's have one file per volume and IO path. After this is done, the original single file FG has lots of empty space, hence the shrink 

    Monday, July 2, 2018 12:52 PM
  • Hi ashwan, there are a couple things you can do to reduce the mdf file:



    - check for unused indexes that can be dropped (look on the system dms index usage stats to view the least used indexes and their size)

    - check the largest tables in the database (get a t-sql script to list all tables by size and rowcount), work with the application owners to identify unnecessary data/ tables


    - check for unnecessary large data sizes

    Shrink should be used only in an emergency response situation or if you are sure that this is the only options (i.e.: A large amount of data has been purged from the data file and now you are required to release the space to the OS)
    Monday, July 2, 2018 1:11 PM
  • There's also a great shrink work-around here

    https://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

    Monday, July 2, 2018 1:20 PM
  • Thank you Visakh, Well DBs are used Data ware housing  and lot truncate and bulk load coming up. Assume If I need to migrate the DBs to different server will that  Shrik worth to do? As a result  require to do reorg indexes . But this exercise eventually DB grow faster than usual.IS that right ?Or else what is the best practise to keep fair amount of DB size .

    Monday, July 2, 2018 7:59 PM
  • Hi Mohsin I am not really needed for DBCC SHRINKDATABASE or DBCC SHRINKFILE. . My question is what is the best practise to follow to keep DB in fare size which fit to actual data size. This DB use for Data warehouse purpose. More truncates, deletes  and bulk data load is going on.  May  be more free space than needed. what is best practise advice for this situation .   
    Monday, July 2, 2018 8:05 PM
  • Hi Qdpma, Thank you for that, Well do you ask to create a new file group add datafile. then move tables and rebuild associate indexes?. Currently no any filegroups .Only on primary . Then DB will come to fare amount of size which fit to actual data size. is that you mean??  
    Monday, July 2, 2018 8:16 PM
  • Hi Qdpma

    Thank you for the  reply well . Creating a file group and move over to new file might be good option . I need to try on this .That mean after migrate how do I ensure no any data on particular data file?

    regards

    Monday, July 2, 2018 11:21 PM
  • Hi Mohsin I am not really needed for DBCC SHRINKDATABASE or DBCC SHRINKFILE. . My question is what is the best practise to follow to keep DB in fare size which fit to actual data size. This DB use for Data warehouse purpose. More truncates, deletes  and bulk data load is going on.  May  be more free space than needed. what is best practise advice for this situation .   

    You stated that the DB undergoes multiple operations such as deletes, bulk load etc. So even if you try to reduce the size, it will eventually grow again. Now, from your screenshot, it does look like a lot of free space and one might be tempted to shrink it. But you know that sooner or later the DB will grow up to that size and hence I would leave it alone unless I am severely short of space. That way, the data file internally has a good room to expand and won't eat up extra space from OS until it reaches the currently allocated size of 222 GB (after which it auto grows based on the autogrowth setting).

    As for the best practice on sizing etc, it depends. You will have to monitor the file usage and growth trends and see what the size would be like in the next few months and determine the auto growth accordingly. As an example, schedule something like this.

    You said you want the size of DB to fit the actual data and if you do that, there will be no extra room and SQL would have to keep relying on auto-growth which is bad for performance (especially if you don't have instant file initialization enabled). hence, there should be some free space.

    All that said, if you do want to release space then run DBCC SHRINKFILE with TRUNCATEONLY as this won't cause fragmentation. If you're lucky, you see space released and if not, run DBCC SHRINKFILE with NOTRUNCATE (this will move the pages from last to first so there's free space at the end of file) followed by SHRINKFILE with TRUNCATEONLY and accept fragmentation.

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    Monday, July 2, 2018 11:31 PM
  •  there will be no extra room and SQL would have to keep relying on auto-growth which is bad for performance (especially if you don't have instant file initialization enabled).

    Do you mean auto growth enabled  will be an issue on performance?  secondly can you expalain more on "if you don't have instant file initialization enabled"

    finally can we run this commands while others connected? . Basically not interrupted connected sessions? I know better to run offline.

    regards

     

    Tuesday, July 3, 2018 4:59 AM
  •  there will be no extra room and SQL would have to keep relying on auto-growth which is bad for performance (especially if you don't have instant file initialization enabled).

    Do you mean auto growth enabled  will be an issue on performance?  secondly can you expalain more on "if you don't have instant file initialization enabled"

    finally can we run this commands while others connected? . Basically not interrupted connected sessions? I know better to run offline. 

    Dont you think you have changed the question completely ? You started by asking about reducing mdf file size and came to Autogrowth, while you are free to do it but the mail question is lost. If discussion is what you need please create thread as discussion.

    What do you want to open Pandora's box by trying to reduce size of data file by shrinking, is this really a big concern. Is it not possible to add more space. There is other way to decrease the size and that is by page and row compression but again this is enterprise feature and bring lot of overhead and this all will come on your head. Plus you have DWH where lots of data manipulation occurs where lots of data are added and deleted I would never touch such database specially for shrinking. 

    The other way you can keep things under control is setting proper autogrowth, apart from this I do not think there is other way.

    You are talking about auto growth and also talking about keeping size intact well they both are apposite of each other, if you dont want AG at all size database much bigger so that AG has not to occur but then the DB size will bother you so you need to set your priorities straight.

    My Suggestion: Leave it as it is, 500GB is really not a that big database.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Tuesday, July 3, 2018 7:13 AM
    Moderator
  • shrinking a data file can be a painful exercise, and it should not be done without fully understanding the purpose, and having a clear strategy. 

    be sure to test the method on a QA system, observing time to complete key steps during which serious blocking will occur. 

    If there are no objects left in a filegroup, then DBCC SHOWFILESTATS (there a DMV for this now?) should show no usage. PRIMARY cannot be emptied?

    Also, I am only suggesting that the very large objects be moved out, as the index rebuild with move - is better at moving data that DBCC SHRINKFILE. hence the DBCC is not too painful when there is only a moderate amount of data in the filegroup.

    Saturday, July 7, 2018 5:26 PM