Home Forums SQL Server 2008 SQL Server 2008 - General *MAY* need to switch DBs from Full Recovery to Simple, due to backup space issues... RE: *MAY* need to switch DBs from Full Recovery to Simple, due to backup space issues...

  • Occasionally I've seen stuck open transactions lead to unchecked log file growth. Try DBCC OPENTRAN on the DBs with oversized log files and see if there is an active open transaction that has been there a long time.

    If there is, and you can trace to a process that should have closed a long time ago, you can kill the process. Run DBCC OPENTRAN again, and once the stuck transactions are gone (know what they are before you kill them of course), the log file should start to reuse space and further growth will stop.

    The log file itself will not shrink by itself though, if you want that done you'll need to run the DBCC SHRINKFILE command. That may be a good idea, because if your log file is 3 times larger that the datafile, it is likely you have an excessive number of VLFs (Virtual Log Files) and this won't help anything.

    I've addressed log file issues on my instances a number of times without changing the Recovery Model, which carries its own risks.

    Naturally you should know what you're doing when running these commands on a live production database, so please do some research on DBCC OPENTRAN, SHRINKFILE and VLFs. Hopefully that will point you in the right direction.