In one of our DBs there is an audit job that runs every day and fills the log file really quickly. I'm not a DBA and we don't have one, so I'm looking at how to properly set up whatever it is i need to set up to make sure this doesn't fill up and crash processes.
I've done quite a lof reading up on all the ins and outs of log files, and really am not any wiser on what it is I need to do exactly.
I had a look using this query:
SELECT name , state_desc , recovery_model_desc , log_reuse_wait_desc FROM sys.databases
And the DB in question had "LOG_BACKUP" under the column "log_reuse_wait_desc". I'd read in quite a few places that taking a log back up would "flush" or "clear" the log, the last log back up was in May so I took another one, then re-checked the query above, which now said "NOTHING" rather than "LOG_BACKUP".
THe olg had also went from 97% to 1.6%. Proceeded to re-run the job for the audit trail, and the log size % went back to 63% and the "LOG_BACKUP" under the column "log_reuse_wait_desc" reappeared.
From this point I'm not sure what is the next step, how do I stop this growing so quickly? I'd read about setting up a regular log back up but then read it will fill your disks up quickly.
Not really sure where to go from here?
Thanks for any suggestions or advice.