1

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.

5
  • Who is "they"? You also state your log backups are ever hour, and then at the end state every 15 minutes, which is it? SharePoint content databases are very active databases if your company uses SP heavily. As long as the log files are not growing out of control, I would not be all that concerned with the log file being larger than the data file.
    – user507
    Commented Apr 27, 2017 at 16:12
  • To elaborate slightly on what @ShawnMelton said: if the transaction log size reaches a certain point (let's say 200GB) and stays there, it's probably not a problem; that's just the size your DB needs for some activity. If, however, the transaction log was 200GB tow weeks ago, 210GB one week ago, and 220GB today? You might have a problem. You might also just be seeing a seasonal growth pattern.
    – RDFozz
    Commented Apr 27, 2017 at 16:36
  • NOTE: do not repeatedly shrink the t-log file (unless required for diagnosing the problem). If you shrunk it to 50 GB and it grew back to 200GB, it'll probably keep doing so until you locate and resolve the root cause (or, determine that you simply need a 200GB t-log file).
    – RDFozz
    Commented Apr 27, 2017 at 16:37
  • Have you checked the size on the tlogs that are being backed up to see if there are certain times when the files are larger than others to see if that coincides with a heavier workload, or database mainteance routine (such as reindexing)? Maybe adjust the log backups to every 10 minutes instead of 15?
    – Nic
    Commented Apr 27, 2017 at 16:37
  • @Nic has an excellent point. I had problems with my t-log consuming more disk space than desired during weekly maintenance job. I simply adjusted the frequency of the tlog backups from every 30 minutes to every 5 minutes for the duration of that weekly job (the rest of the time, they still ran every 30 minutes). Worked beautifully.
    – RDFozz
    Commented Apr 27, 2017 at 16:41

2 Answers 2

1

So I am not sure why is this happening, is it related to Always On?

It is and also isn't related to Always On. Always On Availability Groups requires that the database be in the full recovery model - which is why you need log management in the first place. So long as your secondary replicas are staying very close to the primary (i.e. no lagging secondary replicas for hours or days) log management shouldn't be affected very much due to AGs.

I know that Always On AG Databases need bigger Log files, so would this be normal behavior in AO?

Always On Availability Groups does not inherently need larger log files. Generally it ends up being the cause because most databases are in the simple recovery model before the change is made to Availability Groups and thus log managements is a new concept to that database.

If the database was in the Simple recovery model and the log grew to X GBs, then the minimum log size will be X GBs regardless of recovery model, Always On, etc. However, since log management isn't automatic anymore a random number (generally 30 minutes or an hour) is arbitrarily chosen and log management happens on those intervals. Now the log will need to grow to accommodate the changes in log management automation.

Much as @Shawn Melton and @RDFozz have stated in the comments - if you don't want the log growing too large and all of your replicas are properly in sync, then have your log management happen more frequently.

As this is running SharePoint 2013 databases ...

SharePoint is known for having their own timer jobs which run internal maintenance items and storing blob data. This, on its own, will cause poor log management and there is simply nothing you can do about it. Thus it doesn't surprise me that the log the this database is larger than the data files.

It's never a good sign when the datafiles are smaller than the log, but I do believe in this case - unless you change the log management automation to shorter intervals [And even then it depends on what SharePoint does to the DB] it'll most likely end up staying that way - and that might be normal. In that case we can mark it as normal and go about our day.

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

That's a great step in the right direction (I work for Microsoft) of having a risk and health check of your instance(s). This specific check is something that we us to look for databases that may be having improper or no log management. In some cases (I've personally dealt with) the DBA has said, "Ooops that shouldn't be in bulk-logged..." and we've changed it back to Simple because it didn't need any point in time or extended recovery options. That's ok! The check does, however, give everyone a opportunity to look at the databases in question and make sure that something isn't wrong and that's the key distinction. Like I said above, this might just be normal and that's ok - however we wouldn't be doing our jobs properly if we just left it alone and didn't say anything, it's important.

Anecdotally when I was a DBA for a large clothing company, we had a 3rd party application that used a cursor to loop through all employees every 15 minutes and run a stored procedure which deleted all of their history and then rebuilt it from scratch... in a single transaction. The database was 50 GB and the log file for it was.... 280 GB. That's how the app worked, I logged complaints and possible code changes with the 3rd party vendor and was summarily dismissed. It happens and some application just run that way.

-1

I followed these steps it worked for me:

From sp_Blitz Result: Transaction Log Too Large?

  1. Make sure you’re in the right recovery model
  2. Set up transaction log backups if you need point in time recovery
  3. Shrink your transaction log file (maybe)

More details in the linked post.

1
  • Thanks, I did this as I explained in my question, but after 1 day they get bigger again
    – mehnihma
    Commented Apr 27, 2017 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.