Unused space; shrink or not shrink

Question

Unused space; shrink or not shrink

rpeters76

Valued Member

Points: 59
More actions
August 2, 2018 at 1:41 am

#374111

Hi,
I have some experience with the SQL Server databases but I'm not trained as one..., so maybe this question is no-brainer?
We have SQL servers with many 'small' databases in which users regular deleting some data from tables and fill it with other data or even drop a complete schema with all tables etc. If I'm correct this deleting and dropping is creating the unused space inside a database.
I also that with some shrink action you can reclaim this unused space, although you can get defragmented indexes etc, so it is advised not to shrink.
My question is is this unused spaced used ever again if for example there is a limit set on the datafile size, or is this unused space 'lost' forever?'
Because if it it is reused you also will get defragmentation, or not?
Thank you,
Remko

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

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

coolchaitu SSChampion Points: 10334 More actions · Answer 1

rpeters76 - Thursday, August 2, 2018 1:41 AM
Hi,
I have some experience with the SQL Server databases but I'm not trained as one..., so maybe this question is no-brainer?
We have SQL servers with many 'small' databases in which users regular deleting some data from tables and fill it with other data or even drop a complete schema with all tables etc. If I'm correct this deleting and dropping is creating the unused space inside a database.
I also that with some shrink action you can reclaim this unused space, although you can get defragmented indexes etc, so it is advised not to shrink.
My question is is this unused spaced used ever again if for example there is a limit set on the datafile size, or is this unused space 'lost' forever?'
Because if it it is reused you also will get defragmentation, or not?
Thank you,
Remko

Remko,
Shrinking data file will produce fragmentation. Not the log file. Check log_reuse_space column in sys.databases, what does it say?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Don't shrink. The free space will be used for future inserts, future new tables, etc.

Only time I'd shrink is if more than half of the DB is empty space and I don't expect that space to be used within the next year

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

coolchaitu SSChampion Points: 10334 More actions · Answer 3

GilaMonster - Thursday, August 2, 2018 6:44 AM
Don't shrink. The free space will be used for future inserts, future new tables, etc.
Only time I'd shrink is if more than half of the DB is empty space and I don't expect that space to be used within the next year

Very true.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

Do you track the used space per database? I've found it helpful to keep info about it in a separate DBA only database so I can analyze what the average growth is of each database per month over time, and use that to determine how long it would take to fill the current unused space you have.

andycadley SSCertifiable Points: 5296 More actions · Answer 5

"Unused" space will be reused again as new data is added and the datafile won't grow until that space has been used up, so unless disk space becomes an enormous problem I would avoid shrinking databases. It's less of an issue to shrink log files, although if you are finding they regularly bloat to large sizes it's probably an indication you want to run more frequent log backups.

Above all else, whatever you do, don't set auto-shrink on or run maintenance plans to shrink databases on a schedule. It should be a very deliberate action if you have to do it.

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 6

If the tables are actually heaps - that is, they do not have a clustered index - then deleting data will leave unused space allocated to that table. You can recover this space using ALTER TABLE ... REBUILD or by adding a clustered index.

Shrinking the data file will not recover space that is allocated to the heap and will just cause additional fragmentation in the database.

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

rpeters76 Valued Member Points: 59 More actions · Answer 7

Thank you all!!
With the log file, I don't have an issue because we use the Simple recovery model.
I was wondering if this unused spaced was reused again, for inserts etc, and apparently, it does so there is almost no reason to shrink, only on very special occasions.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 8

rpeters76 - Thursday, August 2, 2018 11:54 PM
Thank you all!!
With the log file, I don't have an issue because we use the Simple recovery model.
I was wondering if this unused spaced was reused again, for inserts etc, and apparently, it does so there is almost no reason to shrink, only on very special occasions.

I hope that data isn't actually important, then. SIMPLE recovery means you're not doing any transaction log backups and you'll only be able to recover from the last full backup to the last DIF (if you do those). Are you even doing full backups or does the data in this database either truly not matter or exists somewhere else?

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