I am seeing in more environment the database recovery model is set FULL and there is no transaction log backup scheduled and the log files are growing bigger, until it reaches a file system space.
Me: Hello sir, May I know the reason, why we set the database in full and not taking any transaction log backup.
John: I do not know, but it is good to have a database in full mode.
Me: No sir, We are having an issue whenever there is a high load in the transaction by application or re-indexing , the transaction log file gets full and we are running out of space.
John: So will there a problem with full mode.
Me: In full mode transaction file, i.e. logical transaction file (VLF) will be not truncated and reusable, until we take a transaction log backup. So it grows and keep grows.
John: So, Do you mean we need to run a transaction log backup.
Me: yes, Sir, it depends on your RPO and RTO. If you are ok with a data loss. we can put the database in simple and will take only full and differential backup. In simple SQL will automatically do that truncated and reusable.
John: If it keeps the space good. I am ok with that, It’s a non -production database and there will be a production DB is another server, that is critical and we need an up to date data.
Me: Sure Sir. I will schedule a regular and frequent transaction log backup for production and will change the recovery model to simple for non-production.
Bottom line:
There are many servers and application, I looked is almost like this. Either there will be no proper DBA support or lack of DBA knowledge. In another case, we do not know whom to contact for these changes.
You can check the “log_reuse_wait_desc” and for what it is waiting it reuse the file and most of the time it is a “log backup”. ” SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases name=DBname”
Please read the link for more details. Depends on your RPO and RTO, place the database recovery model and schedule the transaction log backup.
The only time, the full recovery helps in the tail log backup scenario. Make sure to get an RPO and RTO from your application team. http://www.sqlskills.com/blogs/paul/disaster-recovery-101-backing-up-the-tail-of-the-log/.
