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
DBCC OPENTRAN(), and don't ever useSHRINKDATABASEuseSHRINKFILEto target only the log file, and only in an emergency. Also, why are you on RTM? SP1 was released last May and there have been 5 important cumulative updates since then. – Aaron Bertrand Apr 04 '16 at 15:52