Full Backup started on 4/21 12 AM. While backing up one of the database, it is stuck.
sp_WhoIsActive shows following information
wait_info: (1208ms)PREEMPTIVE_OS_WAITFORSINGLEOBJEC
CPU - 188,528,7853 and Increasing
Also, I see another backup on the same database started 30mins later (Backup to VirtualDevice - I know this is AppAssure backup tool).
From SQLSkills preemptive_os_waitforsingleobject, I see This wait type is when a thread is calling the Windows WaitForSingleObject function for synchronization with an external client process that is communicating using that object.
And this is blocked for 2 days - I don't think one of the session will terminate itself until I kill the other.
However, LOG backups were happening without any issues. Only FULL backup and DIFF backup for that particular database was blocked.
I killed the AppAssure session and all the long queue went away from AppAssure.
I killed the DIFF backup as well. Now the only process left is this FULL backup with 100% complete with same wait_info and not willing to complete!!
I had no other options but to kill the FULL db backup and restart backup jobs.
And, the USER_DATABASES full refuses to start saying the job is already running but sp_WhoIsActive showed nothing. Also, there was nothing in killed/rolled back state.
CurrentJobActivity in msdb showed that the job was active. Had to right-click-stop under jobs and then start the job again!!
Any idea on how do we avoid this situation? (other than telling the IT-team to stop AppAssure?)
[EDIT]: Adding the version
Microsoft SQL Server 2012 (SP3-CU8) (KB4013104) - 11.0.6594.0 (X64)
Mar 15 2017 10:52:31
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
[EDIT2]: Another day - 4/26
Memory Clerks at the time
Log shows this
Server Config:
RAM: 24GB - 21.5GB for SQL Server
Processors: 8
Not much activity in this server - hardly 50 people access this SQL Server via Sharepoint.
