2

I’ve implemented Ola Hallengren maintenance solution in one database, and I’m noticing that it’s getting blocked by itself, I don’t know why honestly.

What I have noticed, is that it seems to be run within a serializable isolation level, which is weird since the default isolation level is read committed, and the maintenance script does not change it explicitly.

So, the questions are:

  • Could the isolation level be related to this locking issue?
  • Assuming that indexes are rebuild in sequential way….why would the script block itself within the same SPID?

Sample

Blocked process report sample can be found here.

Output of @LogToTable='Y' can be found here.

The process does complete without errors. This is still a problem, due to the fact that all running user requests are put on hold while the reindex process is running. Duration would not be an issue if no locks were generated. Since a lot of locks are generated, then duration (2 hours) is indeed an issue on a heavily used database.

0

1 Answer 1

0

I'm not familiar with your implementation (ola-hallengren) of SQL Server, but I would hunt down the frameworks behind your implementation and read the code. It's likely it may use the ADO.NET class called System.Transactions.TransactionScope, which defaults to serializable. While the default isolation level for SQL Server is read committed (as you stated). The framework is likely overriding SQL Server. You will either need to find that configuration or reset it when the application starts up with your own script.

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.