I want to reduce the transaction log file size of my Database frequently.
If I use transactional log backup frequently for example every 1 hour will it reduce the database log size or not? Does transactional log backup affect database log file size?
Is shrinking the Database log file frequently a good idea or not?
Please guide which approach should I use.
According to that article the transaction log backup is the only way how to reduce the size of the transaction log in the FULL recovery mode.
Shrinking will help only in a situation when you for example have forgotten to take transaction log backups and your transaction log file grows and then you take the backup and you need to shrink the physical log file size once so that it is not extra huge. But shrinking will not delete any transaction log data, only shrinks empty space allocated in the file.
So shrinking the transaction log file frequently is usually not a good idea.
See technet:
Shrinking database and log files can be set to occur automatically. However, we recommend against automatic shrinking, and the autoshrink database property is set to FALSE by default.
On the other hand taking transaction log backups frequently when your database is in FULL recovery mode is a good idea.
If you do not need FULL recovery mode with transaction log backups, you can also switch your database to SIMPLE recovery mode. In a SIMPLE recovery mode you do not need to backup transaction logs, but in case of disaster you need to use your last full backup.
You can, but you don't want to. Growing the transaction log is an expensive operation because not only does it need to allocate the space on disk, but it needs to write zeros to all of the space allocated. Any operation that needs space in the log and doesn't have it will need to wait for that zero filling to complete. Which kills performance.
Shrinking because there was a one-time operation that artificially expanded the log is fine. But if you find that it's growing to a certain size every day and you're shrinking it back down, just leave it; that's the size it needs. And keep taking those log backups (or put your db into simple recovery if you don't need point-in-time recoverability).
