To SHRINK or not to...

Question

To SHRINK or not to...

mpdillon

Hall of Fame

Points: 3566
More actions
August 2, 2015 at 6:52 pm

#300912

I am working with an ERP database. Its file size is 110 GB. There are two tables which account for 60GB of that space. These are two tables which are audit tables for the transactions in one module of the ERP system. These two files are not used by the ERP system for Reporting or any other function. They just log info and then the user does not have any access to that info. Yes, I know. This is very strange but I wanted to make the point that once I truncate those tables and set the ERP system to never write to them again, the overall database should never grow to that size again.
I have read a lot about not shrinking a database. But I think that once I do truncate these two tables and set the system not to repopulate them, that I should shrink the database to recover that file space.
What is your best advice?
The system is currently SQL 2000. We are migrating to 2008 R2. It there any reason to wait until we have completed the move to 2008 R2 to Shrink the database?
Thanks,
pat

Viewing 5 posts - 1 through 4 (of 4 total)

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

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

A single one-off shrink of the database just is not a big deal. What happens is that people hit issues with disk, transactions, etc., and they set up automated shrinks that run multiple times a day or week. This leads to massive fragmentation at the disk level. Doing it one time because you have a targeted situation is just not a big deal. Go for it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

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

... and don't forget to rebuild the indexes on EVERY table because even one-off shrinks are going to fragment the hell out of your database.

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 3

Jeff Moden (8/3/2015)
... and don't forget to rebuild the indexes on EVERY table because even one-off shrinks are going to fragment the hell out of your database.

+1

All too often I see people defragging and then shrinking. That makes no sense and is a huge waste of time. You always need to defrag after shrinking, so don't bother with doing that prior to the shrink.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Josh Murrah SSC Journeyman Points: 89 More actions · Answer 4

I always first try the TRUNCATEONLY option when shrinking a file for the reason given above, I don't want the disk churn and fragmentation of the full shrink if at all possible.