February 24, 2012 at 2:39 pm
Accidental DBA here.
I'm trying to redo out backup scheme. I've got a maintenance job setup to perform full backups, then I shrink the database. Then I have a separate maintenance job set up to perform backups of the transaction logs; should I also shrink the databases after that?
Kindest Regards, Rod Connect with me on LinkedIn.
February 24, 2012 at 2:42 pm
I would not shrink the database after either of those two events. You probably have an index rebuild/defrag job setup that is going to cause the database to grow. It will be better on the database system to not 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
February 24, 2012 at 2:50 pm
It's not a good idea to shrink the database regularly at all. The shrink after the full backup is probably playing havoc with index fragmentation as it is, forcing the file to regrow next time data is added, slowing down the database during that regrow, etc.
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
February 25, 2012 at 12:31 pm
I'll throw in with the others. The only time you should shrink the database is if there has been a catastropic event that caused some really crazy file growth. Even then, you might want to consider just leaving it all alone if you have the room for all the reasons the others have mentioned.
Much more important would be the growth factors for the files of the database. Since you're an accidental DBA, if you'd post what your current growth factors are for your user databases and TempDB are, I'm sure someone could give you some decent recommendations for helping the underlying files from becoming terribly fragmented due to the default settings of the growth factors.
Since you're new, here's one way to get the data I just asked you for. There's a whole lot more that would help us help you but let's peel on potato at a time.
SELECT database_id,
file_id,
type_desc,
data_space_id,
name,
state_desc,
size,
max_size,
growth,
is_percent_growth
FROM sys.master_files
;
--Jeff Moden
Change is inevitable... Change for the better is not.
February 25, 2012 at 12:55 pm
February 28, 2012 at 8:41 am
Thank you, Jeff, for that handy SQL script. I've saved it. Right now I ran it on our test database server. The growth precentages (the last column in your SQL query) are pretty small, but that's normal for that database, as we almost never hit against it. Only the other developer and myself, and only occasionally. Anyway, should I run it and save the results, and compare that over time?
Kindest Regards, Rod Connect with me on LinkedIn.
February 28, 2012 at 8:44 am
sengeet86 (2/25/2012)
Check this.http://www.sqlskills.com/blogs/paul/post/why-you-should-not-shrink-your-data-files.aspx
Thank you for this.
Kindest Regards, Rod Connect with me on LinkedIn.
February 29, 2012 at 6:07 am
Shrinking a database is a bit like letting the air out of your car tyres while you are parked because you do not see the need for them to have air when you are not using the car.
It takes time to let the air out, when you could be doing something more useful like eating donuts. When you want to use the car you have to spend time pumping the air in when you could be doing something more useful like eating more donuts or even driving the car.
In almost all situations, the database is the size it is because it needs to be that size. Any time spent shrinking the database is wasted time because the database will only grow back to its original size as it is used. And while it is being shrunk and growing, you are hurting the performance of people using that database. Repeated database growth causes disk level fragmentation that puts an upper limit on how well the database will perform.
For people coming from a MS-Access world, it can be hard to realise that shrinking a SQL Server database is a really Bad Thing, because a database compact and repair is such a Good Thing in MS-Access. You need to look under the covers at why a database compact is good for Access - it is to rebuild indexes and update statistics. Neither of these things happen when you shrink a SQL Server database. You still need to rebuild indexes with a SQL Server database, but this is done using explicit index rebuild commands.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 29, 2012 at 9:46 am
OK, it definately looks like shrink the database files is not a good idea. I get that.
How about shrinking the transaction logs? Even the linked to article by Paul Randal, suggests that maybe a good thing to do, especially if as he puts it, "... if you log has grown out of control."
Kindest Regards, Rod Connect with me on LinkedIn.
February 29, 2012 at 10:20 am
Same.
A once off shrink is ok, if something's happened to increase the file far beyond what's needed. Once off shrinks are ok, whether it's data or log. Scheduled shrinks of anything is a bad idea.
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
February 29, 2012 at 2:40 pm
No. Once off means just that, one time. A one time, not to be repeated shrink after some unusual activity or problem has caused a file to be far larger than it needs to be.
For a data file that might be an archiving operation that's left the DB with more free space than it will use in several months. For a log file it might be a period of failed log backups or an once-off import job or similar that's left the log file far larger than it needs to be.
In those irregular cases a once-off (as in one time) shrink is OK providing it's done sensibly.
Regular shrinks of any form are harmful, whether it be log or data.
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
February 29, 2012 at 6:42 pm
just to take the shrink / don't shrink discussion a little further, how about the following scenario:
You have a mirrored database that is reindexed say once per week, and the reindexing causes the log file(s) to grow from 5GB to 20GB. If there is insufficient disk space to leave the log file at 20GB, the log file would need to be shrunk back to 5GB. Given that this is a mirrorred database, temporarily switching recovery model from FULL to SIMPLE wouldn't be an option as it would break the mirroring.
Are there any alternatives to shrinking the log file?
March 1, 2012 at 2:50 am
1 - Leave it at 20 GB as it needs to be that size
2 - Change your indexing so that it doesn't need 20GB of log space (break up index rebuilds with log backups in between and rebuild only what needs rebuilding.
You don't need to switch to simple recovery to shrink the log, but in that state you're shrinking and regrowing the log weekly, if the autogrow settings aren't optimal that's fragmenting the log and slowing your mirroring down, and all the log records for the repeated shrinks and grows also have to be sent to the mirror, which is just overhead the mirroring doesn't need
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply