1

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

enter image description here

Memory Clerks at the time enter image description here

Log shows this
enter image description here


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.

  • You said it was blocked, which session or process was blocking it ?. I have a hunch this issue might not be directly related to SQL Server the wait type indicates that the call was transferred to windows and it was not able to finish its task and SQL Server is just waiting for it to finish its task. – Shanky Apr 23 '18 at 10:20
  • One more thing are you dumping the backups on local drive or file share or Azure BLOB storage ? – Shanky Apr 23 '18 at 10:40
  • Backups are saved to local drive – Santhoshkumar KB Apr 23 '18 at 11:54
  • The SQL Native backup was not blocked by any session. sp_WhoIsActive reported the percent_complete as 100. The other backup (AppAssure) on the other hand was blocked by SQL Native backup (VSS thing) – Santhoshkumar KB Apr 23 '18 at 11:58
  • What was the statement that was executing in DatabaseBackup? The BACKUP DATABASE command or something else? – Ola Hallengren Apr 24 '18 at 18:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.