November 11, 2010 at 10:47 am
Just a comment here. This is one of the best threads I've seen here.
Thanks everyone.
November 17, 2010 at 7:04 am
Another question:
If a large amount of data (over 100Gb) are removed permanently from a db then this obviously frees up a lot of space within said db. How best to reclaim the space?
Regards to all
George25
November 17, 2010 at 8:17 am
george25 (11/17/2010)
Another question:If a large amount of data (over 100Gb) are removed permanently from a db then this obviously frees up a lot of space within said db. How best to reclaim the space?
Regards to all
George25
You can shrink in this case, but don't just shrink down to the data size. Look at how much space the remaining data needs, then add the pad for data growth over the next xx months along with space for maintenance operations.
After you shrink, your indexes will likely be more fragmented, so what you want to then do is rebuild the indexes.
Shrinking does need to be done, but it should be a relatively rare operation.
December 16, 2011 at 2:00 am
Good article - quick question though.
If rebuilding the indexes can cause the file to grow - why not rebuild the indexes then shrinkfile afterwards?
Dan
December 16, 2011 at 2:06 am
danielfountain (12/16/2011)
If rebuilding the indexes can cause the file to grow - why not rebuild the indexes then shrinkfile afterwards?
Because doing a shrink will fragment the indexes again, thus making the rebuild pointless.
December 16, 2011 at 2:10 am
paul.knibbs (12/16/2011)
danielfountain (12/16/2011)
If rebuilding the indexes can cause the file to grow - why not rebuild the indexes then shrinkfile afterwards?Because doing a shrink will fragment the indexes again, thus making the rebuild pointless.
DOH! of course!
December 16, 2011 at 2:35 am
Hi,
what about shrinking just the log file when the database is set to simple recovery model? Any potential 'fuckups'?
December 16, 2011 at 3:53 am
haovard (12/16/2011)
Hi,what about shrinking just the log file when the database is set to simple recovery model? Any potential 'fuckups'?
Guys,I'm bit late
If your log file grow in simple recovery model, Its because of
1. poor application design
2. Poor maintenance design etc
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 16, 2011 at 3:54 am
Does not answer the question.
December 16, 2011 at 4:31 am
muthukkumaran Kaliyamoorthy (12/16/2011)
If your log file grow in simple recovery model, Its because of1. poor application design
2. Poor maintenance design etc
Errr, no way!
Simple recovery does not in any way mean that the log will not grow. There are all sorts of reasons that the log will grow even in simple recovery that are not related to incompetence.
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 16, 2011 at 4:39 am
haovard (12/16/2011)
what about shrinking just the log file when the database is set to simple recovery model? Any potential 'fuckups'?
Depends on the circumstances and reasons behind the need to shrink.
If you've had some unusual operation that grew the log beyond what it normally is and you know that the log won't ever need to be that size again, then by all means, a once off shrink back to the previous size of the log is fine.
If the log is just growing slowly due to increased activity on the server, then shrinking will just mean it regrows. Growing a log is an IO intensive operation and ongoing data modifications on the server will have to wait for it. If the autogrow settings aren't configured appropriately then the grow could result in lots of VLFs, slowing down database recovery and backups.
In short, unless you need the space and know that the log won't grow again, rather don't shrink logs
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 16, 2011 at 5:14 am
muthukkumaran Kaliyamoorthy (12/16/2011)
haovard (12/16/2011)
Hi,what about shrinking just the log file when the database is set to simple recovery model? Any potential 'fuckups'?
Guys,I'm bit late
If your log file grow in simple recovery model, Its because of
1. poor application design
2. Poor maintenance design etc
I mean
1)
Without writing a batch statement and sometimes not closed the transaction.
2)
Most of the time doesn't have proper rebuild index maintenance.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 16, 2011 at 5:38 am
haovard (12/16/2011)
Does not answer the question.
sorry for that confusion.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
December 16, 2011 at 6:30 am
In all a well written and reasoned article and I agree wit the practices outlined as far as production goes. However, it's a regular practice at our shop to restore production databases on our Development, Unit, System and User Acceptance servers. A part of that process is to shrink both the log and database as a part of that restore. Disk space is cheap but not free (also non-production servers are virtualized). It's a luxury to have that many chances ro wring out problems before deploying code to production. I need to respect the NOC's need to efficiently manage all resources.
--Paul Hunter
December 16, 2011 at 10:17 am
haovard (12/16/2011)
Hi,what about shrinking just the log file when the database is set to simple recovery model? Any potential 'fuckups'?
There is no problem with shrinking a log file while in Simple recovery mode.
It is very common for that type of log file to be 1% used.
The issue is understanding why your transaction log grew larger than you intended.
Is it likely to happen again? Do you care if it does?
On most of my production databases that are 100+ GB, I give them a log file that is 8,000 MB and an autogrow increment of 8,000 MB. If I recall from my research on the VLF issue, 8,000 MB is the most efficient autogrowth size for the transaction log. The 8,000 MB log file and growing it each step in chunks of 8,000 MB is for maximum performance. That is for FULL databases.
My databases in Simple mode are mostly static without changes happening. I give them just a 1,000 MB log file and an autogrow increment of 1,000 MB if they need it.
Viewing 15 posts - 76 through 90 (of 109 total)
You must be logged in to reply to this topic. Login to reply