SQL SERVER – Script – Transaction Log Backups Based on Growth / Size

While doing a comprehensive health check of the SQL Server, one of the things I check is the backup plan for the production databases. Many times, I have run into a situation where a customer says ” Transaction log file of the database has grown more than expected. I want to know the reason why and how to reclaim space and how to avoid such situations”. Let’s break it down into two parts and then

Read More

SQL SERVER – Introduction to Log Space Usage DMV – sys.dm_db_log_space_usage

It has been quite a while since SQL Server 2017 has been introduced, however just recently I have been seeing interest for this project. Lots of users are just switching to SQL Server 2017 and I believe that is normal. In the year 2017 during my Comprehensive Database Performance Health Check, I encountered mostly SQL Server 2014 and SQL Server 2016. Recently, during the consultancy, I received a very interesting question about log space.

Read More

SQL SERVER – Huge Transaction Log (LDF) for Database in Availability Group

One of my blog readers posted on Facebook asked following question about Huge Transaction Log:

Hi Pinal,
We are using AlwaysOn availability group for our production database. The database is fairly large in size as its around 260 GB. The database transaction log file grows around 2.5gb every day. Over the weekend the DBA team noticed that the Log file for this database bloated up to around 230 GB and then seems to hold steady. While troubleshooting I noted an error that the rebuild Index failed due to “Availability_Replica”. Here is the error which we saw in ERRORLOG

Read More