How to reduce physical size of database

Question

How to reduce physical size of database

coolchaitu

SSChampion

Points: 10334
More actions
October 25, 2020 at 6:16 am

#3800858

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?

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

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

Cebisa SSC Enthusiast Points: 151 More actions · Answer 1

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 3

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.

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

epb-728487 Newbie Points: 2 More actions · Answer 4

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?

frederico_fonseca SSCoach Points: 16162 More actions · Answer 5

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)

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 6

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.

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

epb-728487 Newbie Points: 2 More actions · Answer 7

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?

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 9

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.

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

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 10

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.

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 11

shoud hold a quote, it didn't 🙁

This reply was modified 4 years 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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 12

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