I have very complicated situation here.

Application team was reporting that they see spikes and errors every 20 mins. We checked what was running and found log backups.

So we tuned log backups using different compression level available in idera third party software. They used to run for 5 mins and now started competing in 3 mins.

Application team saw little relieved but issues seems to be relevant and they are still concern on spikes.

To play little further, we changed log backup to run every 13 mins to find whats really going on. And here it is, mo more time outs when log backup completes or run other than every 20 mins schedule.

Checked everything on SQL Server and did not see any other stuff running every 20 mins.

The issue starts every 20 mins which last for about on avg 20 seconds. Unable to find any SQL queries or related process. So far no scheduled task at O/S level found as well.

Waits: LCK-XX being the higher no followed by Pageiolatch...

Snaphshot isolation for all db's: RCSI

I will try to add more info, but please suggest what can be checked here to find and resolve this performance issue occurring every x mins.

  • Your log backups take three minutes to run? What happens if you use native? Maybe the holdup is your vendor writing to an unoptimized log table that never gets cleared out? This kind of thing can happen with SQL Server Agent Jobs too... Commented Oct 3, 2018 at 19:18
  • 1
    When's the last time you cleaned out msdb tables? Commented Oct 3, 2018 at 19:33
  • 1
    Instead of once a month you might want to consider running that every day, removing entries more than a month old. That daily job will run more predictably and the tables will remain a consistent size instead of sawtooth. Commented Oct 3, 2018 at 19:48
  • 1
    @AaronBertrand and Erik, thanks for your comments. I will definitely try that. But i still did not get why that msdb records has anything to do with time outs which are now not colliding with log backups running anymore. Currently that msdb holds 8000 records when pulled for a month using query here sqlperformance.com/2015/07/sql-maintenance/msdb Commented Oct 3, 2018 at 19:53
  • 1
    Keep an eye on threadpool waits. Commented Oct 3, 2018 at 20:00

1 Answer 1


This is to help others as this was a very tricky solution to find:

We found below to be the problem where all the available core schedulers were not visible online

i.e we had SQL server enterprise edition because of which all 128 cores were not used and instead visible online were 40 only.

After upgrading to enterprise: core license , we were able to use all schedulers and viola!

Hope this helps as sometimes licensing could be tricky and we might skip

  • This will be useful sqlperformance.com/2012/11/system-configuration/…
    – Kin Shah
    Commented Oct 18, 2018 at 15:26
  • @Kin, thanks for the link. Whats the difference then changing alter server configuration properties to use those schedulers again and one i did like upgrading ? Upgrading is whole lot bigger process , so as mentioned in article can that change be safe and works as expected? Commented Oct 18, 2018 at 15:39
  • 1
    You have to test it out in your environment.
    – Kin Shah
    Commented Oct 18, 2018 at 16:22
  • Did you mean - you had standard edition before and then you upgraded to enterprise edition? In your sentence, both the places it is enterprise edition. Or is there any change within enterprise edition licensing? Commented Apr 25, 2022 at 6:12

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.