December 9, 2010 at 9:58 pm
Comments posted to this topic are about the item Shrinking Databases
December 10, 2010 at 12:18 am
The only time we use shrinking is when we are shipping databases around (by shipping, I mean moving databases to teams in different geographic locations, or even within the network). Other than that, we never use shrinking.
I would agree on making shrinking harder simply because without the knowledge and skill on when & how to use shrinking, it does more bad than good (as you rightly put it, an angel might loose some feathers).
I would propose that shrinking be made possible if the following is true:
1. A trace flag is enabled AND
2. Database is in a single-user mode
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins
December 10, 2010 at 1:52 am
Should it be removed? No.
There are valid uses for it. Two weeks ago I sat running shrink against every database on a server (disclaimer: it was a dev server). I sometimes get (large) copies of databases for tuning work and I'll often truncate tables that I'm not interested in and shrink the database. I don't have space for 300+ GB databases on my laptop.
What I would like to see is perhaps a warning on the shrink database/file dialog that mentions side effects.
Should it be removed from the maintenance plan wizard/designer? Absolutely yes. There is just about no justification in existence for a maintenance plan that shrinks databases.
Should autoshrink be removed? Hell, yes! It's the option that should never be enabled (but often is)
Should it be fixed and made smarter? Would be nice, but somehow I don't see it happening. According to Paul, he wrote a smart shrink while he owned that code, and it still didn't go into the product.
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
December 10, 2010 at 2:07 am
If you need that space back, then you need that space back. A warning on the side effects and gentle background automated 'heal'.
December 10, 2010 at 2:17 am
I almost completely agree with Gail's comments.
Shrinking should not be removed. There are situations where a database has suffered exceptional growth, and there is no need to keep that space allocated. For instance if you forgot to schedule log backups, or after a huge data import job, the log file can be huge. Or if you accidentally populated a table with a cross join instead of an inner join, both the data and the log files will be enormous. That is a legitimate situation for reclaiming space.
Shrinking should not, under any circumstances, be part of regular maintenance. Neither scheduled, nor manually. So it should be deliberately hard to execute. The autoshirink option and the shrink database task in the maintenance wizard should never have been introduced, since, as Steve writes, it is natural to want to reclaim space. Both people with no database background and people with an Access background are bound to think that shrinking is good.
I don't see much need for optimizing the algorithm. Even with an optimal algorithm, shrinking is still a bad idea. The fragmentation is not the only, nor even the biggest problem. The biggest problem (in my opinion) is that after a shrink, the database will always grow back to its normal working size. That results in many autogrow events - and those are not only slow, but also cause fragmentation at the file system level, which no SQL Server maintenance can undo.
I am perfectly fine with using shrink when absolutely needed, then reorganizing or rebuilding my indexes to remove the fragmentation. Sure, that will cause some autogrow events (unless I manually grow the files first). So what? The database will ALWAYS grow after a shrink, better to do that while still in the maintenance window and not during business hours.
December 10, 2010 at 3:32 am
Wow. I think I have to disagree for a part.. But that might have to do with the fact that we have different sort of environment than most. Once a month we do a huge transaction in which about a million and a half records are updated. This causes the DB to temporarily grow. No issues there.
Except that this transaction is only about 1% of the normal use of the DB. During the transaction (which is run during the weekend and is not suffering from time constraints) the DB grows with about 10 GB. We cannot change this as it is a third-party tool.
Now I could leave the DB running the rest of the month with 65% free space but that wouldn't be very effective in terms of fragmentation and ad-hoc query performance, not to mention the 10GB of space on the SAN that is unused.
In our case, we shrink the DB after this big transaction so the rest of the month the DB runs with better access times.
Like with all statements, procedures and features of SQL Server: You need to think before you act, not take out features just because some people abuse them. 😉
December 10, 2010 at 4:06 am
It should be removed from maintenance plan options as it has no place in day to day database maintenance.
I can just about see a case for autoshrink in dev environments, so leave it as a configurable database option but do not include it in the gui options, and add warnings in the BOL documentation.
Shrink should not be removed as there are legitimate scenarios for using it such as shrinking a log file if a database is set read only, reducing a data file after an archive operation. An ability to be able to reduce file sizes is a must.
Make it more intelligent? Yes would be nice. Perhaps just add a warning on completion informing you you should rebuild your indexes due to fragmentation caused.
In a nutshell, a shrink command is required but don't make it look like a standard practice option.
---------------------------------------------------------------------
December 10, 2010 at 4:10 am
bas de zwart (12/10/2010)
Now I could leave the DB running the rest of the month with 65% free space but that wouldn't be very effective in terms of fragmentation and ad-hoc query performance
Free space in a database affects neither query performance nor fragmentation.
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
December 10, 2010 at 4:12 am
GilaMonster (12/10/2010)
bas de zwart (12/10/2010)
Now I could leave the DB running the rest of the month with 65% free space but that wouldn't be very effective in terms of fragmentation and ad-hoc query performanceFree space in a database affects neither query performance nor fragmentation.
Perhaps not on the SQL server level, but surely on the OS level it does?
December 10, 2010 at 4:20 am
bas de zwart (12/10/2010)
GilaMonster (12/10/2010)
bas de zwart (12/10/2010)
Now I could leave the DB running the rest of the month with 65% free space but that wouldn't be very effective in terms of fragmentation and ad-hoc query performanceFree space in a database affects neither query performance nor fragmentation.
Perhaps not on the SQL server level, but surely on the OS level it does?
Only in the sense of inefficient use of hard drive space and maybe file-level fragmentation (which shrink-grow cycles do tend to cause more than just grow and leave does)
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
December 10, 2010 at 5:34 am
Shrinking should be left
And Scheduable
Why? I work, largely, in Development databases
On a BAD day there may be 4 users in a single DB - Most of the time it's 1 user
Usual workload is a couple of transactions (Maybe - 0.5gigs of transaction log)
Have roughly 50 DB, on a POS (Prime, Old, Sub-Optimal, or possibly acronims might apply...) box, with very limited hard drive space - I WANT every DB on it shrunk as much as possible
Many days - No usage
How do New ones get created? Either through an external program that leaves ~60% free space (Fine for production, but Development will NEVER hit that space), OR by a Backup/Restore (Again, LOTS of free space, and on a DB with a couple gigs, the default 10% growth is brutal, and needs to be shrunk)
So I have a script that explicitly shrinks em. Yes, turning on the "Truncate log on Checkpoint" helps (Script does that too), but the T-Log is still used.
Possibly more useful - DBA's with a clue, if they are gonna be idiots and use shrink in production, let em, they get to deal with the mess
December 10, 2010 at 5:56 am
I'll pile on to this one a bit.
Should it be removed? Hell no! As others have already pointed out, there are legitimate reasons to shrink databases, especially in some DR or troubleshooting scenarios.
The problem is having it built into the maintenance plans. That should be removed, absolutely. If it's not available as an all to easy to flip switch, we'll see a lot less of it around.
"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
December 10, 2010 at 5:58 am
My opinion - what needs to be "fixed" is the default option of FULL on model. There are a lot of non-DBAs supporting SQL Server. Databases are set to FULL, there are no logdumps, the log file grows, so the only way they know to fix it is to run a weekly shrink job. As the "new DBA" here, I'm seeing it all too frequently as I start looking into the departmental SQL Server instances.
Cindy
December 10, 2010 at 6:02 am
jims-723592 (12/10/2010)
Yes, turning on the "Truncate log on Checkpoint" helps (Script does that too), but the T-Log is still used.
Trying not to be too picky, but... Unless you're using SQL 7 or earlier, Trunc log on checkpoint is deprecated and should not be used any more. That means leave it at the default of false. The replacement is Simple Recovery Model (in simple recovery a checkpoint truncates the log)
From Books Online:
Starting with SQL Server 2000, setting the trunc. log on chkpt. option to true sets the recovery model of the database to SIMPLE. Setting the option to false sets the recovery model to FULL.
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
December 10, 2010 at 6:23 am
The shrink function is a must in SQL Server. From time to time I get a bundled app that has a logging type table.. the app captures a ton of info and pumps millions of rows into the table before anyone notices and that data is not needed. They scale back what goes into the table and we have either truncated the table to start fresh or delete millions of rows and put in a delete for X number of days to keep the table pruned.
OR
Do a mass delete over night/early hours and forget to run the trans log back up more often.. The trans log becomes 60 gig and on an older server were space is not a ton and then you get a request to copy the prod db down to a test SQL Server and the test server has even less space. This situation happens to me quite often
Should it be in a Maint plan? Probably not... but the ability to shrink a file needs to be there.
I remember getting a call at 11:30 PM and one of our older SQL Servers someone left a query run that was doing a huge sort and the TEMP DB extended to consume the entire E drive and the application transaction log too.
Viewing 15 posts - 1 through 15 (of 71 total)
You must be logged in to reply to this topic. Login to reply