DBA

Database Transaction log file full in the full recovery model

Advertisements

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/.

 

 

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

9 + 1 =

hacklink panel hacklink al hacklink sollet hdxvipizle resim yükleme onwin venüsbet ai nude 1xbet güncel giriş eskort yeşilköy cami avizesi seo paneli soma kömür bahis forum backlink al pgslot สล็อตเวตรง holiganbetvdsSakarya Escorthack forum hacklink cami halısı cami halısı cami halsı cami halısı cami halısı cami halısı cami halısı cami halısı cami halısı saricahali.com.tr cami halısı cami halısı cami halıs cami halısı cami avizesi cami avizesi cami süpürgesi cami süpürgesi cami ısıtma cami ısıtma evden eve nakliyat ofis taşıma seo hizmeti Onwin Rulet Casino Slot Oyna Bahigo 1xbet Deneme bonusu Bahis siteleri Maç özetleri Bahsegel Canlı Casino Siteleri Sweet Bonanza Blackjack Casino Rulet Oyna Canlı Casino takipçi satın al exeboost.com smm panel santenette.com google.com.tr <