Why Shrink DB is bad?

Question

Post reply

Why Shrink DB is bad?

Mike Levan

SSChampion

Points: 14201
More actions
November 12, 2008 at 8:57 am

#195782

Many DBa's say that shrinking is a bad idea, but that is what we do to gain disk space every time.
is there any alternative we can do for getting disk space back by getting rid of un wanted space in DB files.

Viewing 15 posts - 1 through 14 (of 14 total)

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

peanuts91 SSC Enthusiast Points: 187 More actions · Answer 1

If your log file growth a lot, you can schedule a log backup every 30 minutes for example.

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

Mike Levan (11/12/2008)
Many DBa's say that shrinking is a bad idea, but that is what we do to gain disk space every time.
is there any alternative we can do for getting disk space back by getting rid of un wanted space in DB files.

Why are you continually getting unwanted space in your DB files ?

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 3

You want to keep free space in the files. That is what is used as the server needs to allocate space for inserts/deletes. This is expensive, and you don't want to do this on a regular basis. It's not like a Word or Excel file or a log file for IIS. You want to preallocate the data space you need for 2-6 months and let the server use that space.

For logs, you want the PEAK space that you need between log backups.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 4

Mike Levan (11/12/2008)
Many DBa's say that shrinking is a bad idea,

in a word "fragmentation"

Mike Levan (11/12/2008)
but that is what we do to gain disk space every time.

i hope not, pre allocate the space well in advance.

if you're performing regular shrinks and then subsequent growths you would be well advised to take SQL down and defrag the disk drives then rebuild your indexes just as regularly

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Mike Levan SSChampion Points: 14201 More actions · Answer 5

does shriking the data files or log files gets the server downtime as it ido when we run an alter cmd on sql server.

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

Mike Levan (11/12/2008)
Many DBa's say that shrinking is a bad idea, but that is what we do to gain disk space every time.
is there any alternative we can do for getting disk space back by getting rid of un wanted space in DB files.

Databases tend to grow. It's in their nature. All you're doing by shrinking is badly fragmenting the indexes and forcing SQL to spend time and resources growing the data and log files again later on. If you're short of disk space, get more disks. If you're not short of disk space, leave the DB file alone.

What's the difference between a DB file with 10% free space on a drive that's 50% free and a DB file that's 50% free space on a drive with 10% free? (Assuming there's nothing else on the drive)

does shriking the data files or log files gets the server downtime as it ido when we run an alter cmd on sql server.

No, but it does slow things down. Also when the files grow again (as they will have to as soon as data is added) that slows things down as well.

http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/ Also read the articles linked at the end.

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

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 7

Are you running low on disk space? if so then DB shrink is good. Your DBA's are right the reason being it increases fragmentation on your tables..

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

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

The_SQL_DBA (11/12/2008)
Are you running low on disk space? if so then DB shrink is good.

Shrink DB is not good. Getting more drive space is good.

Shrink is what you do as a stop-gap manoeuvre while waiting for more drive space when you haven't planned storage requirements properly.

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

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 9

Yes Gail..you are right with that, I mean to say use Shrink DB as a emergency measure only..like when the log file grows too large after a one off bulk operation DBCC ShrinkFile can be used effectively to reclaim the log space to OS

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

The_SQL_DBA (11/12/2008)
..like when the log file grows too large after a one off bulk operation DBCC ShrinkFile can be used effectively to reclaim the log space to OS

These operations should be batched to reduce the scope of the transactions and reduce logging, or log backup frequency should be increased during the operations.

Aaron Griffith-643962 Grasshopper Points: 20 More actions · Answer 11

What about if you have a Data Warehouse and during the daily load it performs a bulk insert into a table that eventually gets truncated after the load?

How do I recover that space without DB Shrink?

Cheers,

Aaron

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 12

You can't, but if it's a daily load, why do you care? The space will get reused tomorrow.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 13

Steve Jones - Editor (11/12/2008)
You can't, but if it's a daily load, why do you care? The space will get reused tomorrow.

that's a very valid point

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Aaron Griffith-643962 Grasshopper Points: 20 More actions · Answer 14

Good point Steve.

I was having problems where the load would fall over half way through because it wouldnt release the disk space after the truncate earlier in the load, and would run out of disk space.

It is obvious I need to get more disk space and I wont have a problem.

Thanks,

Aaron