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.

Improve this question
8
  • 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
    Commented Oct 20, 2013 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
    Commented Oct 21, 2013 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
    Commented Oct 21, 2013 at 3:12
  • What documentation indicates you need two backups for log truncation to occur? That statement is not correct.
    – Henry Lee
    Commented Oct 22, 2013 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
    Commented Oct 22, 2013 at 2:13

2 Answers 2

Reset to default
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.
Improve this answer
4
  • #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
    Commented Oct 22, 2013 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
    Commented Oct 23, 2013 at 8:20
  • the system is dead quiet...
    – Jamie Holloway
    Commented Oct 23, 2013 at 16:05
  • Please do not change the recovery model to SIMPLE just to shrink the logfile.
    – Twinkles
    Commented Dec 23, 2013 at 9:54
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 :)

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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