Shrinking Databases

Question

Shrinking Databases

Viewing 12 posts - 61 through 71 (of 71 total)

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

liebesiech SSCrazy Points: 2610 More actions · Answer 1

If shrink should be available? Of course it should! There are situations where you need to have a tool to help you out. If any user decides to use it on a regular basis it is his responsibility and he should RTFM.

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

Remove autoshrink? No... I never use it and probably never will. I also can't think of a reason why I'd ever use it. But it takes MS time to remove features just like it takes time for them to add them. MS should concentrate on doing more useful things.

Make any of the DBCC functionality more difficult to use? Heh... no way!

Leave these things alone. I use DBCC Shrinkfile and the like to get a whole lot of people out of trouble like when someone has been running databases for 5 years with FULL recovery mode and NEVER doing a transaction log backup. I don't care how good the advice of "never" shrinking a file is, you just have to love it when you can recover 4.6 TeraBytes (no... I didn't mispell that) on an almost full 10 TeraByte system in less than 20 minutes. 😉

Making things more difficult to use is fixing the wrong problem. It's kind of like saying that no one can drive on a Friday night because there are too many stupid people who drive drunk on Friday nights... it's "fixing" the wrong bloody problem. 😉

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

Keith Langmead Ten Centuries Points: 1048 More actions · Answer 3

dfalso (12/10/2010)
3. The downside to Simple recovery model involves backup tools that want to read the transaction log and create diffs. Ours, for instance. Since I keep everything Simple, it ends up having to do a complete backup. This is a conscious tradeoff as tapes are cheaper than disk, but it's something to consider.
-Dom

If I remember correctly, Differential backups shouldn't require the transaction log at all, as they are done by backing up all of the changed extents since the last full backup, not the data from the transaction logs. That said, if someone told me a 3rd party backup app still wanted to see the transaction logs it wouldn't surprise me.

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

It's backing up the changed extents, but like full backups it'll need enough of the tran log to ensure a consistent restore, however I don't think dfalso was talking about SQL diff backups, but more like a 3rd party tool that creates a list of changes (diffs) by reading the transaction log.

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

SanDroid SSChampion Points: 10068 More actions · Answer 5

Jeff Moden (12/11/2010)
MS should concentrate on doing more useful things.
.... Make any of the DBCC functionality more difficult to use? Heh... no way!
Leave these things alone.
... it's "fixing" the wrong bloody problem. 😉

Jeff, Glad to see youre comments. I always enjoy them.

I think most of us agree that fixing the buggy utility with code changes and better documentation is the real way to resolve these problems with any software project.

Why do you think M$ not taking this approach?

Why did M$ decide that removing (sometimes without depricating) and then hiding (sometimes retarding) administrative functionality in SQL server was the path to resolving customer issues?

My best guess: Fixing something that is broken requires skilled resources with real knowledge about developing a solution for an application issue. Removing it only requires removing the code and removing the documentation of the procedure.

For example: The resolution for fixing customers issues with the truncate transaction log utility in the BACKUP LOG utility was to remove the ability and let the community find another way to do this. Unfortuantely the "new way" requires make a change to the actual database settings that requires a change control. Running the SQL Backup utilities did not. So now when the Tran Log backups fail due to a weekend long SAN or Backup device issue (someone didn't change a tape) it takes two production DB updates to do what a one line backup log script and re-run of the last full backup to resolve.

This method of resolution by management instead of development is currently a serious desease killing features instead of making them better. I feel lucky to curretly work somewhere that feels like I do about this.

dfalso Mr or Mrs. 500 Points: 511 More actions · Answer 6

GilaMonster (12/13/2010)
It's backing up the changed extents, but like full backups it'll need enough of the tran log to ensure a consistent restore, however I don't think dfalso was talking about SQL diff backups, but more like a 3rd party tool that creates a list of changes (diffs) by reading the transaction log.

Yes, that. Thanks for clarifying.

TravisDBA SSCoach Points: 15780 More actions · Answer 7

SanDroid (12/13/2010)
Jeff Moden (12/11/2010)
MS should concentrate on doing more useful things.
.... Make any of the DBCC functionality more difficult to use? Heh... no way!
Leave these things alone.
... it's "fixing" the wrong bloody problem. 😉
Jeff, Glad to see youre comments. I always enjoy them.
I think most of us agree that fixing the buggy utility with code changes and better documentation is the real way to resolve these problems with any software project.
Why do you think M$ not taking this approach?
Why did M$ decide that removing (sometimes without depricating) and then hiding (sometimes retarding) administrative functionality in SQL server was the path to resolving customer issues?
My best guess: Fixing something that is broken requires skilled resources with real knowledge about developing a solution for an application issue. Removing it only requires removing the code and removing the documentation of the procedure.
For example: The resolution for fixing customers issues with the truncate transaction log utility in the BACKUP LOG utility was to remove the ability and let the community find another way to do this. Unfortuantely the "new way" requires make a change to the actual database settings that requires a change control. Running the SQL Backup utilities did not. So now when the Tran Log backups fail due to a weekend long SAN or Backup device issue (someone didn't change a tape) it takes two production DB updates to do what a one line backup log script and re-run of the last full backup to resolve.
This method of resolution by management instead of development is currently a serious desease killing features instead of making them better. I feel lucky to curretly work somewhere that feels like I do about this.

I agree. Just "leaving things alone" is sometimes viewed by short-sided management as the easiest and most cost-effective approach, but in the long run,not always the smartest in software development, particularly, in this age of stiff competition. If fixing the problem means restricting or redesigning the functionality of a component that currently has undesirable side-effects, then that has to be considered sooner or later. Just ignoring or removing it and moving on to "other things" is kind of like ignoring the one loose nut on a rear tire. Yes, you can probably do that for a long while and get away it. But sooner or later it will come off when the car is doing 70 miles an hour and end up doing serious demage to something or someone else. Removing a feature really depends on what the feature is and where it is at. Ignoring this same feature that has undesirable side-effects is never a good idea in the long run IMHO. 😀

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"

SanDroid SSChampion Points: 10068 More actions · Answer 8

TravisDBA (12/13/2010)

SanDroid (12/13/2010)
Fixing something that is broken requires skilled resources with real knowledge about developing a solution for an application issue. Removing it only requires removing the code and removing the documentation of the procedure.
This method of resolution by management instead of development is currently a serious desease killing features instead of making them better. I feel lucky to curretly work somewhere that feels like I do about this.
I agree. Just "leaving things alone" is sometimes viewed by short-sided management as the easiest and most cost-effective approach, but in the long run,not always the smartest in software development, particularly, in this age of stiff competition.

I would not prefer the complete removal of a 15 years old administrative utility over leaving it alone.

I prefer leaving things alone, or replacing them with a better working variation, over the 3D process known as degregate, depricate, and delete.

SQLBill SSC Guru Points: 51440 More actions · Answer 9

Don't remove it - improve it! We use it quite a lot (unfortunately). I work in a business where there is a lot of manipulation of data which along with archiving leaves lots of free space. We frequently can have databases increase 50 - 100 GB in one operation only to end up with only 1 - 5 GB of that used. Leaving the space for the next operation isn't possible because that could be a once a year action. I have one database that when the archiving is fixed will have about 150 GB of unused space. Should I just leave that reserved by that database and not usable by any other one? I can't afford to have 150 GB of drive space become unusable, so I shrink the database.

Our databases get reindexed (defragged or rebuilt) based on their fragmentation. So that helps counter part of the issue caused by shrinking the database.

-SQLBill

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 10

I like it as is. There are purposes for shrinking. It is easy to do. It is a well-known fact that shrinking a database hurts performance. What more can you ask for.

Jamie

UMG Developer SSChampion Points: 13482 More actions · Answer 11

Jamie Longstreet-481950 (12/16/2010)
I like it as is. There are purposes for shrinking. It is easy to do. It is a well-known fact that shrinking a database hurts performance. What more can you ask for.

You mean like it is well know that you don't need to include both the index rebuild and index reorganize steps in your maintenance plans? 🙂

Jamie-2229 SSCrazy Eights Points: 8156 More actions · Answer 12

Correct. It is fairly common for someone to advise me to add shrink to my plan but it seems to me unnecessary and what I've read indicates I'm right. I'm okay with that. Yes. Microsoft software tends to assume we are all stupid. In this case, they assume differently. I like that.

Jamie