I have recently changed job and have inherited a large number of databases which I'm trying to analyse and ensure are set up to confirm with best practice as far as possible. One of these databases has a job that runs every 5 minutes that imports data from a different system. It then shrinks the transaction log file. I have noticed that this step occasionally fails. What I'm wondering is why the step to shrink the transaction log file is there. The person who wrote this has since left so I can't talk to them about it. The database used full recovery model, it takes a full backup daily and transaction log backups hourly. Can anyone explain why they think we need this step as I can't find a definitive answer. I know shrinks are bad with regard to the database and my understanding was that was the case with the log file aswell.
Your suspicion is absolutely correct. Shrinking the transaction log repeatedly is generally a bad idea. I shrink transaction logs if I run a one-off job which explodes the transaction log. But to do it regularly, especially on a database in full recovery mode is something I can't find any reason at all to justify. Shrinking and growing the transaction log will cause fragmentation on the log file. Since a transaction log is written to in a serial way, it might create performance issues if the transaction log is spread out on different parts of a physical disk. The general rule of thumb is that a transaction log file should be as large as it needs to be over a period of time. How long that period of time is depends, but five minutes is a far too short period of time. If you want to use less disk space for the transaction log, you should run the transaction log backup more often instead of shrinking the transaction log. Like I said in the first paragraph: If you are in full recovery mode, it's even more weird to truncate the log, since it probably won't have much effect, and the Little effect it has will immediately be reversed, because in the next transaction after a shrink operation, the transaction log will need to grow again (not necessarily 100% true in every occasion, due to the circular nature of the virtual log files. There's an Active and an inactive part of the transaction log, and if you have inactive virtual log files in the beginning of the log, those won't be removed in a shrink and can therefore be reused.). Perhaps a long answer. To put it shorter: Stop that job. :)
My first guess is that whoever set it up was concerned with the import process implementing either a large or long running transaction that would blow out the log and consume all of the disk space. Of course, without asking the creator we may never know. ;-) Disk space is probably the only reason that I can think of that I would have a step like that in the process. If you have the space to accommodate the log file, I'd remove the step to shrink it. The log file would have to re-grow which can be an expensive operation for SQL Server to do. Even with IFI enabled, it would still have to zero out the log file. Just my thoughts though. =)