SQL SERVER – What is the Meaning of PREEMPTIVE_HTTP_EVENT_WAIT? How to Fix it?

While playing with my SQL Server in Azure VM, I faced an interesting issue.  I did some change in networking to avoid internet access by creating an outbound rule and then I started facing this issue. In this blog, I would explain one of the possible causes of PREEMPTIVE_HTTP_EVENT_WAIT.

THE SITUATION

I have a SQL Server in Azure Virtual Machine. For learning purpose, I wanted to block internet access on the Virtual machine. So, I created an outbound rule to block internet access with “deny” option – as shown below.

Above rule works fine and I was able to achieve what I wanted. I was unable to open any site, including google.com

THE PROBLEM

Since I blocked outbound internet access, my backups to URL started to give me trouble. Whenever I start back up, it runs for a long time (which used to finish in a few seconds). I executed below query to find out what is happening in SQL and found below.

The two wait types are

• BACKUPTHREAD
• PREEMPTIVE_HTTP_EVENT_WAIT

I could not find any documentation for PREEMPTIVE_HTTP_EVENT_WAIT but from the wait, it looks like this wait would not end by itself like they way SQL threads work. It also seems like its waiting for some http request which must have gone out to storage due to my backup to https. Here is the backup command.

BACKUP DATABASE SQLAuthDB TO  
URL = N'https://sqlauthority.blob.core.windows.net/backupcontainer/SQLAuthDB.bak'
GO

As you can see, I am taking backup of database using Backup to URL feature and it should go to my storage account. Since the backup would also go out of the machine, it would use the internet, which I blocked on this Virtual Machine. I waited for around 20 minutes and finally backup failed with below message.

Msg 3201, Level 16, State 1, Line 1
Cannot open backup device ‘https://sqlauthority.blob.core.windows.net/backupcontainer/SQLAuthDB.bak’. Operating system error 50(The request is not supported.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.

Now, I know that I broke backup to URL by blocking internet? Does it mean I must have internet access on the Virtual machine? Or there is something else I can do?

THE SOLUTION

After my search on the internet, I found two ways to solve this by keeping the existing rule.

1. Open specific port and put the rule priority lesser than internet rule. In our case, we are using https which uses 443 port.
2. Add an outbound rule for storage and allow connections to go out even though the internet is blocked.

Option 2 is better as it takes care of port number automatically. So, I change my internet rule priority to 200 and added a new rule with destination as “Storage”

…after this, google.com was not opening but backup to URL started working!

So now, whenever you see PREEMPTIVE_HTTP_EVENT_WAIT, start checking about what kind of query is going to the internet. If it’s a backup to URL then you know the answer now.

Reference: Pinal Dave (https://blog.sqlauthority.com)