4

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.

Kenneth Fisher
  • 24,307
  • 13
  • 63
  • 116
Jamie Holloway
  • 41
  • 1
  • 2

2 Answers2

1

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.
Punter015
  • 136
  • 1
0

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 :)

Jamie Holloway
  • 41
  • 1
  • 2