Skip to main content
Database Administrators

Return to Question

Incorporated comments
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

SQL 2014 Always ONOn log files bigger thenthan database files

We had SQL RAP and we got warning that Transaction log files larger than the database files. They

They advised me to remove databases from Always ON  , change recovery mode to SIMPLESIMPLE and then shrink log files and put it back to Always On. And change maintenance plans. T-LOG backup is running every hour. But again next day transaction log files are larger than the database files. As

As this is running SharePoint 2013 databases, they said it could be do to the migration of SharePoint 2010 databases to 2013, but I have same behavior on another Always onOn cluster, which is not SharePoint. I have tried also to implement Ola Hallengren's maintenance script with same results. On other machines without AO there is notno such problems. So

So I am not sure why is this happening, is it related to Always On? I know that Always On AG Databases Needneed bigger Log files, so would this be normal behavior in AO? Thanks

I have looked at Transaction log is growing very fast. I only have log_reuse_wait=2 in between log backups, everything looks to be working as expected but it is not.

I checked the log send queue and REDO queue size, and it looks good, as it only has "2 Log backup You are waiting for a log backup to occur." while it waits for next log backup. AOAG health is OK and bandwidth is more than enough. Log backup is every 15 minutes.

SQL 2014 Always ON log files bigger then database files

We had SQL RAP and we got warning that Transaction log files larger than the database files. They advised me to remove databases from Always ON  , change recovery mode to SIMPLE and then shrink log files and put it back to Always On. And change maintenance plans. T-LOG backup is running every hour. But again next day transaction log files are larger than the database files. As this is running SharePoint 2013 databases, they said it could be do to the migration of SharePoint 2010 databases to 2013, but I have same behavior on another Always on cluster which is not SharePoint. I have tried also to implement Ola Hallengren's maintenance script with same results. On other machines without AO there is not such problems. So I am not sure why is this happening, is it related to Always On? I know that Always On AG Databases Need bigger Log files, so would this be normal behavior in AO? Thanks

Always On log files bigger than database files

We had SQL RAP and we got warning that Transaction log files larger than the database files.

They advised me to remove databases from Always ON, change recovery mode to SIMPLE and then shrink log files and put it back to Always On. And change maintenance plans. T-LOG backup is running every hour. But again next day transaction log files are larger than the database files.

As this is running SharePoint 2013 databases, they said it could be do to the migration of SharePoint 2010 databases to 2013, but I have same behavior on another Always On cluster, which is not SharePoint. I have tried also to implement Ola Hallengren's maintenance script with same results. On other machines without AO there is no such problems.

So I am not sure why is this happening, is it related to Always On? I know that Always On AG Databases need bigger Log files, so would this be normal behavior in AO?

I have looked at Transaction log is growing very fast. I only have log_reuse_wait=2 in between log backups, everything looks to be working as expected but it is not.

I checked the log send queue and REDO queue size, and it looks good, as it only has "2 Log backup You are waiting for a log backup to occur." while it waits for next log backup. AOAG health is OK and bandwidth is more than enough. Log backup is every 15 minutes.

Source Link
mehnihma
mehnihma
  • 25
  • 7

SQL 2014 Always ON log files bigger then database files

We had SQL RAP and we got warning that Transaction log files larger than the database files. They advised me to remove databases from Always ON , change recovery mode to SIMPLE and then shrink log files and put it back to Always On. And change maintenance plans. T-LOG backup is running every hour. But again next day transaction log files are larger than the database files. As this is running SharePoint 2013 databases, they said it could be do to the migration of SharePoint 2010 databases to 2013, but I have same behavior on another Always on cluster which is not SharePoint. I have tried also to implement Ola Hallengren's maintenance script with same results. On other machines without AO there is not such problems. So I am not sure why is this happening, is it related to Always On? I know that Always On AG Databases Need bigger Log files, so would this be normal behavior in AO? Thanks