SQL SERVER – New Parallel Operation Cannot be Started Due to Too Many Parallel Operations Executing at this Time

One of my clients contacted me via my On Demand (50 Minutes) offering and they were seeing below error message in ERRORLOG – New parallel operation cannot be started due to too many parallel operations executing at this time.

Here is the complete message which was seen in the SQL Server ERRORLOG file.

New parallel operation cannot be started due to too many parallel operations executing at this time. Use the “max worker threads” configuration option to increase the number of allowable threads, or reduce the number of parallel operations running on the system.

I asked for complete ERRORLOG and Event Logs to look it from all possible angles.

• Error: 18210, Severity: 16, State: 1.
• BackupIoRequest::ReportIoError: write failure on backup device ‘{BD27F651-5DB3-4CB4-9615-9FDEC4D8EECE}331’. Operating system error 995(The I/O operation has been aborted because of either a thread exit or an application request.).

At the same time, I found below in event log

• SQLVDI: Loc=TriggerAbort. Desc=invoked. ErrorCode=(0). Process=1724. Thread=2300. Server. Instance=MSSQLSERVER. VD=Global\{FFC0C1C0-25D9-4A90-82B3-3ABBCFEA9476}247_SQLVDIMemoryName_0.
• BACKUP failed to complete the command BACKUP DATABASE ReportingService_Alerting. Check the backup application log for detailed messages.
• SQLVDI: Loc=TriggerAbort. Desc=invoked. ErrorCode=(0). Process=1724. Thread=9844. Server. Instance=MSSQLSERVER. VD=Global\{FFC0C1C0-25D9-4A90-82B3-3ABBCFEA9476}45_SQLVDIMemoryName_0.
• SQLVDI: Loc=TriggerAbort. Desc=invoked. ErrorCode=(0). Process=1724. Thread=8176. Server. Instance=MSSQLSERVER. VD=Global\{FFC0C1C0-25D9-4A90-82B3-3ABBCFEA9476}264_SQLVDIMemoryName_0.

From above it was clear that they are not taking native backups of SQL Server database using a maintenance plan of T-SQL. The backups are being taken using SQLVDI via 3^rd party software.

WORKAROUND/SOLUTION

Looking at the messages, it sounded like the error appears at the same time when a backup is running. Further investigation showed that they had more than 1000 databases and they were getting backed up via 3^rd party tool at the same time. I suggested them to

1. Talk to back up team and find a way to stagger the backups. This would not cause too many parallel threads for backups at the same time.
2. Increase the number of worker thread to avoid message in ERRORLOG.

Once they reduce many concurrent backups, error stopped.

Have you seen such messages earlier? What did you do to solve? Please share via comments with others in the SQL community.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.