Sql-server – Log sizes unexpectedly large: log_reuse_wait_desc == “log_backup”

sql serversql-server-2012

I've been reading posts and manuals all day, I need help.

My problem: transaction log sizes after data and transaction log backups will not shrink, and grew to be unexpectedly large. The system is quiet; no programs/utilities updating the database.

What I expect to see is that after database file and transaction log backup, is to be able to shrink the log, that transaction log sizes decrease, and the log_reuse_wait_desc in sys.database, changes to == '0'

My environment:

  • SQL Server 2012 (SP1) – 11.0.3321.0 (X64), build 9200.
  • Configured w/high-availability mode; recovery set to full.
  • Database file size, 16Gb, Compressed backup 13GB.
  • 3x log files: 20MB, 10GB, 104GB (they all grew to 104GB, but I was able to shrink the first two)
  • Backups complete, no update jobs are running now … DBCC SQLPERF(logspace) == 112GB log size, 64% used
  • …and, ? Select from sys.database log_reuse_wait_desc == "log_backup"

I update the database with Sql stored procs, some integrated-services truncate/load jobs, and a few C# linq-to-sql programs. No transactions…

Any expertise and guidance would be appreciated. Thank you.

Best Answer

Few suggestions from my end, Kindly check and let us know

  1. Run SQL Profiler and check for any unusual activities.
  2. Check if the increase in size of the log files happens regulary at or around the same time (If yes, growht in log file might be the result of scheduled job)
  3. Run DBCC LOGINFO and check if the VLF's are active or inactive.
  4. I randomly GUESS the result of the log growth is due to index maintenance activities you might have scheduled.
  5. Lastly, Just for information: You need to change the database recovery model to SIMPLE and then perform the DBCC SHRINKFILE command.
Related Question