AlwaysON SQL server database blocking
I had two blocking case on one of my alwaysON databases.
- Backup is third party tool and it got blocked by each other sessions.
- SSIS package deployed in SSIS folder and it run long and had blocking as well.
The first one, tried to kill the session ID, it went in to rollback state and it took more than half day, no response.
Fix: Just suspend the database in the primary AG this will remove the killed SPID.
The second one, we can look into the Active Operations of packages.
Integration service catalog – right click SSISDB – Active Operations