0

I have a SQL Server 2005 database in full recovery mode with the following stats:

  • name XX
  • state 0
  • state_desc ONLINE
  • size 2620152
  • max_size -1
  • growth 128

Log File

  • name XX_Log
  • state 0
  • state_desc ONLINE
  • size 517832
  • max_size 268435456
  • growth 64000

Log file stats

  • Database Name XX
  • Log Size (MB) 4045.555
  • Log Space Used (%) 0.7804911
  • Status 0

So I would like to reduce the size of the log - I think my target should be 50MB? with growth of 50MB - so I did a full back up - followed by log back up, twice. Without success

Having done some reading I check my [log_reuse_wait] and got a 2 result - back up log. So first question why is that, when I have just done two backups?

I have looked for open transactions and got the message

No active open transactions. DBCC execution completed. If DBCC printed error messages, contact your system administrator

And I have checked the Activity monitor and looked at Blocked Header and can not find at 1 in any session - I believe that indicates a long running transaction

Finally all I know is when I run DBCC LOGINFO I get a return of 390 lines which I believe indicates too high a VLF count?

I believe the count too high from the articles I have read (but maybe not fully understood) the articles are transaction-log-vlfs-too-many-or-too-few and a-busy accidental-dbas-guide-to-managing-vlfs

I appreciate that I could take the database offline and deal with the problem as detail here and other places, but I think the log file will just grow again and this is our main production database so downtime is too be avoided if possible

What do people suggest as next steps please?

Thanks Ian

| improve this question | |
  • 1
    For one, do not follow the advice you pointed out here - that's a really terrible idea, in fact. Also, what is actually "the problem"? Why do you think you need to shrink the log file? Why do you think 50MB is the right size? Isn't it just going to grow again? So what have you gained? Shrinking the file just so it can grow again is bad, particularly for log files. Please read this in full – Aaron Bertrand♦ Oct 8 '14 at 16:43
  • Ok agreed about info here. Hence for the question. The reason is to save disc space. My recovery mode is full with a maintenance plan for full backup followed by a log back up once a day. I can not see any opentransaction or any long running transactions I am only using about 315MB of my logfile space. The plan in mind is to shrink the log and then put a nightly job in to capture the growth daily and make a more informed decision about the correct log size, by finding the max growth occurring in the monitoring period – Ian W Oct 9 '14 at 11:06
  • Log backup once a day? What's the point? – Aaron Bertrand♦ Oct 9 '14 at 13:23
  • @Aaron Bertrand Yes I see what you mean but that is the maintenance plan installed by our erp provider, and if you want the funny point it occurs after a full backup??? So I am trying to unpick it but am trying to proceed with a bit of care. So I am seeking advice first – Ian W Oct 10 '14 at 19:28
  • Being in full recovery, backing up the database once a night, and backing up the log only once - and immediately after the full backup - is completely asinine and is a bunch of wasted effort with exactly 0 upside. You need to have a chat with your ERP provider and tell them exactly how they're doing it wrong. Or better yet, ask them how this topology protects your data any more than SIMPLE recovery would... – Aaron Bertrand♦ Oct 10 '14 at 19:30
1

You have a couple actual questions here, so let's address each one individually.

To answer the question of why the log_reuse_wait won't update, this field (along with other metadata within SQL Server) will only update after certain events occur. With this field, it will update after a database checkpoint occurs. To force this after taking a log backup, do the following:

BACKUP LOG [foo] TO DISK=N'X:\Backups\foo_20141008120000.trn';
CHECKPOINT;

The checkpoint will occur and the field in sys.databases will update.

For why your log file won't shrink, this is a MUCH larger question. You should read this question/answer by Mike Walsh on why your log file is growing and how best to manage it. I also provide some more detail in this answer.

Regarding VLFs, I would strongly recommend you not worry about that right now. Yes, you can see a performance impact over having to many VLFs (and understanding what a VLF is and how it works is fundamental for understanding the log file), but managing that should only be addressed when you're regularly managing your log with backups and it's not growing out of control.

Finally, as for the size of your log, this is a very open ended question. It very much depends on how much activity your database sees along with the scope of that activity. Chances are that you will shrink your log file, only to see it grow again to accommodate your regular database activity. Before considering shrinking your log file, you need to understand what's making it grow and whether or not you're running regular scheduled log backups against it.

| improve this answer | |
  • Another potential reason for log_reuse_wait to not clear: sqlskills.com/blogs/paul/… – FilamentUnities Oct 8 '14 at 20:27
  • Hi there - Ok your script caused the reset of the log_reuse_wait stat to 0, great however why is a checkpoint not being forced by my maintenance plan nightly which does a full backup and then a logbackup? – Ian W Oct 9 '14 at 11:24
  • On the log size point my properties state the initial log size is 6,546MB it's current size is 6,546MB and only 0.623% is in use (40MB) this info comes from DBCC SQLPERF(logspace). It is this data making me think I can reduce the log size down. Am I barking up the wrong tree? The overall object is I have 5 db like this with the same status and I could release about 3000MB x 5 = 15000MB of space - but thank you for the first bit of help – Ian W Oct 9 '14 at 11:34
  • @ZabadakGalorex Thank you that information makes a lot of sense in this cause as there is not masses of activity and it is likely that the information is all in the same VLF. – Ian W Oct 9 '14 at 11:52
  • Checkpoints happen periodically within the database engine, usually once every minute. They will resolve this, but once transactions happen within the engine, you'll see the same message, because now there will be new transactions in the log. This is just how the engine works. Regarding size, don't focus on how much is in use at a single point in time. Certain actions (ETL, index maintenance, etc) will use more of the log when they run, but you won't see that unless you watch the log at that moment. – Mike Fal Oct 9 '14 at 15:49

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.