July 7, 2015 at 4:33 am
Hi Experts,
For a few days now I have a discussion with a colleague about shrinking the transaction log as a daily maintenance job on an OLTP database. The problem is I cant figure out a way to convince her she is doing something really wrong. Its not the first discussion.. Maintenance Plans. :crazy:
She implemented this "solution" with a lot of customers as a solution against VLFs fragmentation and huge transaction log sizes. My thoughts about doing this is very clear and I have used the following arguments without success to convince her:
- To solve too many VLFs you have to focus on the actual size of the transaction log and the autogrowth settings in combination with regularly transaction log backups. Check the biggest transaction and modify the transaction log size based on this. Not use shrinking as a solution for solving many VLFs.
- Shrinking the transaction log file on a daily basis that is disk I/O intensive. When the transaction log file is too small for new transactions, the transaction log needs to grow and this will cause disk I/O, this can cause performance problems.
- It looks unprofessional.
These steps are used every morning at 6:00 AM and a transaction log backup is made every 30 minutes.
Step 1
DBCC SHRINKFILE (N'' , 0, TRUNCATEONLY);
go
Step 2
ALTER DATABASE
MODIFY FILE (NAME = N'', SIZE = 4098MB);
GO
My main purpose is making sure the customers have the best possible configuration and I cant accept this is being implemented. Are there any more arguments available for this issue?
Thanks!
July 7, 2015 at 4:47 am
You're absolutely correct about it.
Shrinking the log files generates massive file system fragmentation, which is hard to get rid of without downtime.
Shrinking is pointless: it will grow again, so let it grow to its maximum size, then presize the log to its maximum size in chunks of 4GBs to keep VLFs under control.
-- Gianluca Sartori
July 7, 2015 at 5:19 am
Her process is patently ridiculous. Period. She should be working to find/correct the "cause" of the "huge" tlogs. But if they get large for a given reason that is valid then they should be left at that size (preferably made that size in a controlled manner, to set VLFs appropriately).
Binoogle Kimberly Tripp transaction log to find several posts about optimizing tlog throughput and managing VLFs.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply