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.

  • Are you sure you've taken a successful transaction log backup? Does the result of this query make sense: SELECT MAX(backup_finish_date) FROM msdb.dbo.backupset WHERE [type] = 'L' AND database_name = 'mydatabase' – Henry Lee Oct 20 '13 at 13:17
  • Yes Kenneth, the query makes sense. Thank you. I have multiple backups of both the data and transaction logs. The documentation tells me i need at least two database backups for log truncation to occur. If I run DBCC SQLPERF(logspace), I get 73% and 99.89, on two of the databases i'm working with, neither of these values decrease after database and transaction backups...i expect for them to decrease after a transaction log backup. – Jamie Holloway Oct 21 '13 at 3:05
  • Also, if i run DBCC OPENTRAN, there are no open transactions. i'm wondering if behavior is different because these databases are in high-availability mode...? – Jamie Holloway Oct 21 '13 at 3:12
  • What documentation indicates you need two backups for log truncation to occur? That statement is not correct. – Henry Lee Oct 22 '13 at 2:11
  • When you say "high-availability mode" do you mean Database Mirroring? Log truncation can be delayed if the mirror falls behind, but I think the log_reuse_wait_desc shows something different than what you're seeing. – Henry Lee Oct 22 '13 at 2:13

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.
| improve this answer | |
  • #1 -- no unusual activity, system is quiet, dbdc opentrans indicates no transactions, #2 and #4 -- yes, i just removed a rebuild index from a proc to see if this helps, #3 -- 2268 VLFs status == 2, #5 -- ok, makes sense, but if log-truncation occurs, i should expect VLF records w/o status=2, and 'available space in mb' from sys.database_files to show available space, correct? also, log_reuse_wait_desc should return to "nothing" – Jamie Holloway Oct 22 '13 at 22:03
  • Could you check for sessions/connections to the database. Use sp_who2 Also you can use adam machanic's script sqlblog.com/blogs/adam_machanic/archive/2012/03/22/… – Punter015 Oct 23 '13 at 8:20
  • the system is dead quiet... – Jamie Holloway Oct 23 '13 at 16:05
  • Please do not change the recovery model to SIMPLE just to shrink the logfile. – Twinkles Dec 23 '13 at 9:54

ok, two issues here. first, copy-only was checked on a transaction backup maintenance plan. It doesn't say it directly in the documentation, but this does not result in log truncation, and the log continues to fill, and take extents.

next, i used fn_dblog(null,null) to review one of the database logs, saw some 'old' log records there that appeared to have been residual, and not truncated, after a system crash resulting from space problem. so i went through the procedure to reallocate the log file.

One minor awareness point; i added a primary space allocation to the log, than shrank it...the allocation was then set to 20MB...so, don't forget to shrink, then set the primary back to where you want it before placing DB back into full recovery mode.

I'm obviously a newbie with sql server :)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.