SQL Server Large Log Files
Posted on 2011-10-31
Basically I have some very large log files for my SQL Server Databases. We don’t have a DBA and I am in need of assistance.
I have two SQL Servers running Windows 2008 R2 64bit with SQL Server 2008 running Databases with mirroring enabled and have a witness server too.
I have 9 databases setup on the server some more heavily used than others. Our Main database is as follows...
MDF File = 478Mb (Autogrow by 10% Unrestricted)
LDF File = 233GB (Autogrow by 10% Unrestricted)
Last Database Backup 03:34
Last Log File Backup12:30
Recovery Model Full
Mirroring Status "Fully Synchronized"
In SQL Server Agent Manager there is a job scheduled that runs a maintenance plan at 19:00 daily which
Takes Full Backup of the DB to the local disk for all user databases.
Deletes all backup files older than 2 days with .bak or .trn file extension.
There is another job set to take Transaction log backups hourly every day.
In the backups folder there are transaction log backups that are between 200Mb and 700Mb every hour.
Please can you advise what I can do as I don’t believe the log file should be this disproportionate to the Database file?
Running “DBCC OPENTRAN” I get
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (219127:93:1)
Running “DBCC SQLPERF(Logspace)” I Get
Database Name Log Size (MB) Log Space Used (%) Status
Main_Multibrand 239051.6 96.17966 0
Running “select name, log_reuse_wait_desc from sys.databases” I get
If you need any further information then please shout.