We are facing problems related to transaction logs becoming full.
The transaction log for database is full due to 'ACTIVE_TRANSACTION'
My database logs are becoming full after every 4-5 days & after that
for sometime all queries start failing. This state remains for sometime(2-3 min max). After that log space gets freed up & everything comes back to normal again. This has become a recurring issue which happens randomly.
Initially we thought that's its a temporary issue occurred because of some temporary SQL job & did SHRINKDATABASE for 1-2 times. It worked for that time. But nowadays we are facing this issue again & again & its so random that we are not able to identify what query is causing this issue.
We tried looking for expensive queries which might have caused this issue & tried to rerun again to reproduce the same issue but it didn't worked. I tried searching for this problem but every answer is about shrinking log files. I know that doing SHRINKDATABASE every time is not the correct solution. I want to find the exact cause of this issue.
How can I find the exact query which is causing the issue? Or what approach I should adopt to get solution for this problem?
Server details :
- Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
- Recovery Model : Simple
- OS : Windows Server 2012 R2
- Running on Intel Xenon CPU E5-2630 v3 @2.40GHz, 16 cores
- Installed memory - 128 GB
