How to reduce physical size of database

  • I deleted few tables having millions of records, but when see the physical space, itt has not reduced.What needs to be done to reduce the physical size of database?

  • You deleted,/truncated a large amount of data, your database physical size does not autoshrink. Many suggest pre growing your data files. If you are certain that your database will not need to grow, you can shrink a data file

  • Cebisa wrote:

    You deleted,/truncated a large amount of data, your database physical size does not autoshrink. Many suggest pre growing your data files. If you are certain that your database will not need to grow, you can shrink a data file

    If you decide to shrink the data file - you must rebuild all indexes after the shrink.  The shrink operation will fragment the data as part of the operation.  Unless you are looking at recovering a significant amount of storage that will *never* be used again for that database - then don't bother shrinking the file.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeffrey Williams wrote:

    Cebisa wrote:

    You deleted,/truncated a large amount of data, your database physical size does not autoshrink. Many suggest pre growing your data files. If you are certain that your database will not need to grow, you can shrink a data file

    If you decide to shrink the data file - you must rebuild all indexes after the shrink.

    You only need to rebuild the ones that fragmented due to "index inversion" caused by the shrink-file.  Not all of the indexes will be inverted and so you might get lucky and only have a few to rebuild.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  •  

    Hi, I have the same problem. I have tried shrink mdf file, but my mdf file size does not decrease.

    I deleted a large amount of data in my database, about 190GB has been released.  The database will not grow back as it will be used as a query-only database, so the physical size must be minimized to release resources.

    The Initial size of my database is 240GB. I have tried the shrink database, also shrink mdf file with the "Release unused space" option and with "Reorgaize pages before releasing unused space" with the right size, but nothing reduces my mdf file from its 240 GB. I have tried rebuilding indices, and returning to shrink, but nothing.

    How can I remove those empty 190GB from the mdf file?

  • you need to shrink files, not database.

     

    example - replace test with your database name on the use statement, and with the logical name of your datafile on the shrinkfile command.

    the size below is in MB - you will need to determine what size should be on your case - I would leave 5-10% free.

    USE [test]

    GO

    DBCC SHRINKFILE (N'test' , 5100)

     

    after the shrink is finished you will need to rebuild the indexes - better normally to drop all non clustered indexes, rebuild clustered indexes and then recreate the non clustered ones.

    better yet is to drop the non clustered ones BEFORE the shrink.

    ideal flow of steps to maximize the space is

    • drop non clustered indexes
    • rebuild clustered indexes -- will save some space
    • shrink datafile(s)
    • rebuild clustered indexes - to avoid pitfall of index inversion
    • recreate non clustered indexes

    if your tables have lots of LOB objects then process should be slightly different but it does require you to have free space to hold another copy of the database (without non clustered indexes space)

  • To be sure, you don't need to rebuild ALL indexes... just the ones that became seriously fragmented because of the index-inversion the shrink.

    I also recommend temporarily moving your largest indexes to a temporary file group, do your shrink, rebuild the indexes that need it, and then move the indexes in the temporary file group back to the original.  This can help seriously increase the segment sizes but it can also help speed up your shrinks with tables that would probably need to be rebuilt anyway.

    If you can (depends on AG and forms of replication, etc), do it in the Bulk Logged Recovery Model if the database is normally in the Full Recovery Model.  Take at least a DIF backup before doing so and a Dif backup after you're done and have returned to the Full Recovery Model.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks, I'm going to try removing the nonclustered indices. But my problem, I think it is that it is not possible to shrink the mdf file below its initial size. Deleting the indexes, can it be reduced to a smaller size than the initial one?

  • You might want to try out table/index pagecompression to reduce the size

  • Jo Pattyn wrote:

    You might want to try out table/index pagecompression to reduce the size

    Totally agreed, especially for a database that is becoming "Read Only".  Still, row and page compression will not reduce the size of the files and they will still need to be shrunk at the file level to recover space.  Any indexes that become fragmented because of the index inversion caused by the shrinks will need to be defragged.  If a REBUILD is done on a large index, that can create an equally large amount of unwanted free space and you end up in a permanent "push-me/pull you" on that and so it's best if you rebuild the index to a temporary File Group and then rebuild it back to whatever the original File Group it came from and drop the temporary File Group.

    If the database is truly going to be used as a Read Only source of info, I strongly recommend setting the database to Read Only, take a final backup, and then remove it from your backup list if your backups don't automatically realize that it no longer needs to backup the database.  Same for index and stats maintenance routines (you might want to run them one final time prior to setting the DB to Read Only and follow the large index temporary file group thing I recommend above to prevent unwanted unused space).

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • epb-728487 wrote:

    Thanks, I'm going to try removing the nonclustered indices. But my problem, I think it is that it is not possible to shrink the mdf file below its initial size. Deleting the indexes, can it be reduced to a smaller size than the initial one?

    Make sure that you don't remove any indexes that have the UNIQUE attribute even if they're "only" non-clustered indexes.  Doing so can wreak a bit of havoc on foreign keys.

    And, yes... a database can be reduced below its initial size using DBCC SHRINKFILE.  You just can't (obviously but still needs to be said because some people think you can) shrink it to be smaller than the data it contains.

     

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • shoud hold a quote, it didn't 🙁

     

    • This reply was modified 1 day, 20 hours ago by  Johan Bijnens.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • epb-728487 wrote:

    Thanks, I'm going to try removing the nonclustered indices. But my problem, I think it is that it is not possible to shrink the mdf file below its initial size. Deleting the indexes, can it be reduced to a smaller size than the initial one?

     

    do not remove them, disable them if needed. ( so they stay in your documentation and you can easy re-enable them by a rebuild)

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

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

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