Saturday, 29 April 2017

What is the cause of high transaction log file in SQL SERVER

As Salamo Alaikum wa Rahmatullah

You probably either have a long running transaction running (Index maintenance?  Big batch delete or update?) or you are in the "default" (more below on what is meant by default) recovery mode of Fulland have not taken a log backup (or aren't taking them frequently enough).
If it is a recovery model issue, the simple answer could be Switch to Simple recovery mode if you do not need point in time recovery and regular log backups. Many people, though, make that their answer without understanding recovery models. Read on to understand why it matters and then decide what you do. You could also just start taking log backups and stay in Full recovery.
There could be other reasons but these are the most common. This answer begins to dive into the most common two reasons and gives you some background information on the why and how behind the reasons as well as explores some other reasons.

Ma Asalaam
Passion 4 Oracle