February 1, 2018 at 6:04 am
Hi
I have a production database in Simple Recovery mode with around a 300mb log file. The developers want to add a column to a large table and populate it. Even after tweaking the query it's resulted in a 20GB log file in test systems.
There's room on the server to cope with this but my concern is that leaving such a huge log file will have a performance impact as large numbers of VLF's get created.
I know accepted wisdom is never shrink anything, ever, but in this case would it be sensible to shrink the log after the transactions have completed?
Cheers
Alex
February 1, 2018 at 7:00 am
If it's a once-off operation, then shrinking the log (and only the log) once at the end is fine.
The accepted wisdom is not to schedule shrink operations to happen regularly.
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 1, 2018 at 7:53 am
You should pre-allocate enough log space to handle the request. The alter should be much faster if the log does not have to dynamically expand. You should do the expansion in fairly large chunks, say 4GB to 10GB each, to keep the number of VLFs reasonable. You can still shrink the log at the end if you prefer to remove the added space.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 2, 2018 at 2:06 am
GilaMonster - Thursday, February 1, 2018 7:00 AMIf it's a once-off operation, then shrinking the log (and only the log) once at the end is fine.The accepted wisdom is not to schedule shrink operations to happen regularly.
Hello Gail,
Is there any good practice or other way that we can prevent the log to take too much space? Like we do prefer delete a large table in batches to prevent unexpected log file growth.
Regards
VG
February 2, 2018 at 2:22 am
Doing the update in small batches with intervals between them and very frequent log backups, or the database in Simple recovery mode, will minimise the quantity of log space required. If you use Simple mode, remember to take a full backup immediately after you switch back to Full. Of course, you require that the whole operation succeed or fail as one. If that's the case, you'll just have to do it as you have been doing it and accept the hit on the log space.
John
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply