What if I have to shrink the database...:(

Question

What if I have to shrink the database...:(

Viewing 3 posts - 16 through 17 (of 17 total)

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

Jeff Moden SSC Guru Points: 1004223 More actions · Answer 1

SQLRNNR (2/10/2014)
Jeff Moden (2/10/2014)
SQLRNNR (2/10/2014)
jewel.sacred (2/10/2014)
@Keith Tate Thanks a lot for quick the reply. I already had this in my mind but I am more comfortable with Michelle Ufford's gem (No disrespect to Olla's excellent/great maintenance script). Just curious, this could lead to new execution plans? Hopefully, largely good ones but some bad plans as well. This could also mean a very large log file and sadly I have Standard edition to work with. I see some very hard days ahead where I would be explaining and convincing that some processes were slow for a reason :crazy:
Also what if I leave 100 GB space for future growth. Will it cause less fragmentation? OR the result would be the same? Any more ideas on this?
Moreover, what if I re-organize the indexes only?
You can kick off the index maint using her script and force more of a reorg than a rebuild by bumping the thresholds. Reorg will require less log space and is available in standard edition.
When you shrink, I would shrink to a size that leaves about 1.5x the largest table in free space.
Once the reorg is complete - you will probably want to update the statistics on key tables.
Reorg is fully logged even in BULK LOGGED mode. It's MDF space that it will save on because it doesn't create extra pages like a REBUILD usually will. You could do a CREATE WITH DROP EXISTING and get the best of both worlds. Don't forget that REORGANIZE doesn't reorg the B-TREE if that's important.
Yes Reorg is fully logged but is not a single transaction like in a rebuild (it's a whole bunch of smaller transactions). So if your tlog is properly sized, you should never see the log grow and the reorg should have minimal impact - compared to a rebuild.:cool:

"It Depends". It won't matter if you're using anything other than the SIMPLE recovery mode. All those smaller transactions still get logged and won't be cleared from the log until you do a logfile backup. Heh... MS didn't make figuring out what the easy tradeoff will be. If they did the DBCC SHRINKFILE thing right, this wouldn't be a concern at all.

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

Jeff Moden (2/10/2014)
SQLRNNR (2/10/2014)
Jeff Moden (2/10/2014)
SQLRNNR (2/10/2014)
jewel.sacred (2/10/2014)
@Keith Tate Thanks a lot for quick the reply. I already had this in my mind but I am more comfortable with Michelle Ufford's gem (No disrespect to Olla's excellent/great maintenance script). Just curious, this could lead to new execution plans? Hopefully, largely good ones but some bad plans as well. This could also mean a very large log file and sadly I have Standard edition to work with. I see some very hard days ahead where I would be explaining and convincing that some processes were slow for a reason :crazy:
Also what if I leave 100 GB space for future growth. Will it cause less fragmentation? OR the result would be the same? Any more ideas on this?
Moreover, what if I re-organize the indexes only?
You can kick off the index maint using her script and force more of a reorg than a rebuild by bumping the thresholds. Reorg will require less log space and is available in standard edition.
When you shrink, I would shrink to a size that leaves about 1.5x the largest table in free space.
Once the reorg is complete - you will probably want to update the statistics on key tables.
Reorg is fully logged even in BULK LOGGED mode. It's MDF space that it will save on because it doesn't create extra pages like a REBUILD usually will. You could do a CREATE WITH DROP EXISTING and get the best of both worlds. Don't forget that REORGANIZE doesn't reorg the B-TREE if that's important.
Yes Reorg is fully logged but is not a single transaction like in a rebuild (it's a whole bunch of smaller transactions). So if your tlog is properly sized, you should never see the log grow and the reorg should have minimal impact - compared to a rebuild.:cool:
"It Depends". It won't matter if you're using anything other than the SIMPLE recovery mode. All those smaller transactions still get logged and won't be cleared from the log until you do a logfile backup. Heh... MS didn't make figuring out what the easy tradeoff will be. If they did the DBCC SHRINKFILE thing right, this wouldn't be a concern at all.

+1:hehe:

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

jewel.sacred SSC-Addicted Points: 441 More actions · Answer 3

Sorry for responding late. There was an auditing process going on which meant that we are only done with the deletion of the data(No shrink). So I was not able to update the thread. But thanks to all of you for the great support so far. :Wow: