Whether Logfile Shrinking is good or bad one for Performance in SQL?

Question

I am using SQL Server 2008 R2 database and on our SQL Server instance we have more than 70 databases. In a few databases I can see that the transaction log file size is more than 10 GB. I am shrinking the Tlog file size on a daily basis using the following script:

USE ABC;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE ABC
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (ABC_log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE ABC
SET RECOVERY FULL;
GO

Is there any recommended way to shrink the transaction log file size?

Answer 1

Shrinking of log file ON DAILY BASIS like one you do with query you posted is seriously bad and you should NOT do that. The main reason is that after you have shrinked the file it will grow again when new transaction comes and since INSTANT FILE INITIALIZATION is not there for Log files, its only for data files, when log file will grow DB engine will actually have to zero out space to write information so instead of overwriting free space it has to be overwritten with zero values (0×0) and then information can be written. This process takes time and thus delays processing of query

Nice explanation has been done in This link

Please note that instant file initialization is applied to some extent for Tempdb log files even though this is correct you should not shrink tempdb log files on daly basis.

A more simple thought, why to shrink if space has to eventually grow when log informations will be written and log will grow. Its a bad practice, internet is filled with advise not to shrink data files but shrinking log file is equally bad

EDIT: Needless to say( as already pointed out) you are breaking chain of logs by changing recovery model to simple. You loos epoint in time recovery. If point in time recovery is not required kep recovery model of database as simple and log truncations would be handled by DB engine if something is not holding logs and need it in transaction.

Answer 2

... on a daily basis ...

ALTER DATABASE ABC SET RECOVERY SIMPLE;

Yes, this is as bad practice as it gets. Whats the point of having a backup chain if you break it daily?

Now to your question: Does shrinking the log affect performance?

Yes. Indirectly, because you shrink to a way too small file, you are dramatically affecting performance as log needs to grow back to its operational size. Your customers experience painful random delays after your 'maintenance' runs as the database gets frozen to grow and zero-initialize the log file (no instant file growth for log!).

Stop it. Leave the backup chain intact. Determine the operational size of each log file, for each database, grow the log to that size in one operation, leave it so. Monitor log growth events, determine the cause and take appropriate action.

Answer 3

Any shrinking of files (data or log) should be done in coordination with your backup strategy - and as others have pointed out it can be a pretty bad idea in many cases (certainly if done regularly).

Side-stepping the exact question slightly: if your log files grow back to the same sort of size in a day or there-abouts then there is obviously something in your workload that requires them to be that size often. By shrinking them you are making what-ever those processes are do more work when they happen as they have to allocate new log space when they need it instead of it being sat pre-allocated and ready for immediate use.

Furthermore I might suggest (if those processes using lots of log space can not somehow be optimised out) setting those sizes as the minimum for the log files and allocating it all at once. This way there is less external (file-system level) fragmentation then there would be if the files each grow in bits over time. This reduction in fragmentation could give a small performance boost to any of the activities that cause a lot of log file use.