Can not re-set [log_reuse_wait] after log back up 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
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.