Shrunk Database

Question

Shrunk Database

mlorek

SSC Eights!

Points: 906
More actions
April 4, 2018 at 11:06 am

#336956

I had a developer remove a bunch of data from a database and free up a big amount of space. He then ran a shrink on the database. I know shrinking actually causes more fragmentation and issues than it solves but it has been done. My question is now that it is done is there anything I can do to fix the damage? Can I some how reorder the database to remove the fragmentation? Down time is not a issue, just want the best performance on the box when it is back up. I have read about creating a new file group and migrating to that etc, but need some help on this one. Thanks.
April 9, 2018 at 11:55 pm

This was removed by the editor as SPAM

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

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

jasona.work SSC Guru Points: 50117 More actions · Answer 1

What you can do to "defragment" things is to rebuild (NOT re-organize) all the indexes on the various tables. Keep in mind, though, that this process will likely cause both the transaction log and the database file to grow (depending on how much free space either have.)

Not sure about the new filegroup method, some quick Googleing didn't turn up much on resolving fragmentation via that method.

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 2

Just rebuild all the indexes in the database. If you don't care about down time you can just do a ALTER INDEX ALL on every table in the DB.

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 3

Honestly, a single shrink of the database, I wouldn't sweat it too much. It's repeated, even scheduled, shrinks that lead to all sorts of problems. One time, one shrink, no big deal.

"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

mlorek SSC Eights! Points: 906 More actions · Answer 4

Ok thanks all for the responses. I will rebuild all the indexes at this point and see. I appreciate it!

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

mlorek - Wednesday, April 4, 2018 12:57 PM
Ok thanks all for the responses. I will rebuild all the indexes at this point and see. I appreciate it!

What is the largest index you have? If it's BIG, post back and we can do a trick to keep from causing crazy regrowth of the file you just shrunk.

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