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