Delete and shrink data file

Question

Delete and shrink data file

WhiteLotus

SSChampion

Points: 12120
More actions
August 5, 2018 at 7:24 pm

#407579

Hi guys
I just deleted a lot of rows in a table to gain more disk space . and did shrink on DATA FILE afterwards but it turns out I can only shrink in a small chunk gradually . Sometimes I can get 1 GB or 2 GB in one process so it’s clear I will need to make lots of effort on this . Now the disk is only 70% full but I would like to see it 50 or 60% full because we will get an alert if it reaches 80%
Question : Will the new data sits on the space that has been deleted Or I need to shrink it so I can get the new space for new transaction coming ?
Appreciate your feedback !
cheers

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

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

sgmunson SSC Guru Points: 110639 More actions · Answer 1

WhiteLotus - Sunday, August 5, 2018 7:24 PM
Hi guys
I just deleted a lot of rows in a table to gain more disk space . and did shrink on DATA FILE afterwards but it turns out I can only shrink in a small chunk gradually . Sometimes I can get 1 GB or 2 GB in one process so itâ€™s clear I will need to make lots of effort on this . Now the disk is only 70% full but I would like to see it 50 or 60% full because we will get an alert if it reaches 80%
Question : Will the new data sits on the space that has been deleted Or I need to shrink it so I can get the new space for new transaction coming ?
Appreciate your feedback !
cheers

Unfortunately, the only way you can successfully SHRINK a database and gain back disk space is if you happen to have good-sized chunks of "unused" space in the database file that happens to be located at the end of the database file. Lacking that condition, you could shrink the database repeatedly for zero gain. As the shrink process tends to introduce fragmentation into database objects, it''s generally not a good idea. However, you can take a look and see how much free space is in the database and you can search for that technique on the web or this site. However, the larger question might be whether or not adequate planning for disk space usage has been done. Properly indexed data often occupies 10 times as much space as the data alone would require, so keep that in mind as you plan for future growth.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 2

WhiteLotus - Sunday, August 5, 2018 7:24 PM
Hi guys
I just deleted a lot of rows in a table to gain more disk space . and did shrink on DATA FILE afterwards but it turns out I can only shrink in a small chunk gradually . Sometimes I can get 1 GB or 2 GB in one process so itâ€™s clear I will need to make lots of effort on this . Now the disk is only 70% full but I would like to see it 50 or 60% full because we will get an alert if it reaches 80%
Question : Will the new data sits on the space that has been deleted Or I need to shrink it so I can get the new space for new transaction coming ?
Appreciate your feedback !
cheers

You WILL need to rebuild indexes that have become fragmented from the shrink process because the shrinks cause "index inversion", which is absolutely the worst fragmentation possible for anything that might need to do read-aheads.

--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)

ScottPletcher SSC Guru Points: 100942 More actions · Answer 3

After shrinks, I've had good luck very often with just a REORGANIZE overall ~80-85%, rebuild required ~15-20% of the time. Start with the smaller tables first, moving up in size as you reorg/rebuild.

In your case, though, you might want to create a new filegroup and rebuild the tables into that filegroup. That will get them completely out of the original so you can really shrink it. You could put the new filegroup on a different drive. Also, if you wanted, at the end, after the shrink, you could copy (i.e. rebuild) them back to the original filegroup. Of course that depends on the size of the tables, as a rebuild and move will take some time and quite a bit of I/O depending on table size.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

WhiteLotus SSChampion Points: 12120 More actions · Answer 4

Guys
Thanks for the response , Much appreciated
Actually I feel my question has not been answered yet
" Will the new data sits on the space that has been deleted Or I need to shrink it so I can get the new space for new transaction coming ?"

so apart from shrinking or rebuild index , I just want to know if new transaction will be able to fill the new row or space that has been deleted / cleared
in oracle , it is possible but not sure in SQL

Thanks all

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 5

Yes. The space within the data file for that database will be reused by that database to hold ne data without growing... provided that you've actually deleted whole pages OR you're adding new data to the same table you deleted from. If you've only deleted partial pages from a given table, defragging the indexes just for that table will free up space in the file for that database for other tables in that database to use.

--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)

WhiteLotus SSChampion Points: 12120 More actions · Answer 6

Jeff Moden - Monday, August 6, 2018 9:11 PM
Yes. The space within the data file for that database will be reused by that database to hold ne data without growing... provided that you've actually deleted whole pages OR you're adding new data to the same table you deleted from. If you've only deleted partial pages from a given table, defragging the indexes just for that table will free up space in the file for that database for other tables in that database to use.

Thanks for the reply Jeff
I did delete the row in a table , so it actually deleted whole pages or partial pages ?

Cheers

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 7

WhiteLotus - Monday, August 6, 2018 9:19 PM
Jeff Moden - Monday, August 6, 2018 9:11 PM
Yes. The space within the data file for that database will be reused by that database to hold ne data without growing... provided that you've actually deleted whole pages OR you're adding new data to the same table you deleted from. If you've only deleted partial pages from a given table, defragging the indexes just for that table will free up space in the file for that database for other tables in that database to use.
Thanks for the reply Jeff
I did delete the row in a table , so it actually deleted whole pages or partial pages ?
Cheers

The only way to tell would be to look at each page separately but that's overkill. Did you rebuild the indexes on the table you deleted from? If so, then you don't have to worry about what the delete did because the rebuild fixes it.

--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)