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

SQL
1 Comment

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.

SQL SERVER - What is the Meaning of PREEMPTIVE_HTTP_EVENT_WAIT? How to Fix it? prem-01

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

SQL SERVER - What is the Meaning of PREEMPTIVE_HTTP_EVENT_WAIT? How to Fix it? prem-02

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

SQL SERVER - What is the Meaning of PREEMPTIVE_HTTP_EVENT_WAIT? How to Fix it? prem-03

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”

SQL SERVER - What is the Meaning of PREEMPTIVE_HTTP_EVENT_WAIT? How to Fix it? prem-04

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

SQL SERVER - What is the Meaning of PREEMPTIVE_HTTP_EVENT_WAIT? How to Fix it? prem-05

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)

SQL Azure, SQL Backup, SQL Scripts, SQL Server, SQL Wait Stats
Previous Post
SQL SERVER – SQL Server Agent Missing in SQL Server Management Studio (SSMS)
Next Post
SQL SERVER – The OLE DB Provider “Microsoft.ACE.OLEDB.12.0” for Linked Server “(null)” Reported an Error. Access Denied

Related Posts

1 Comment. Leave new

  • We are facing the issue of PREEMPTIVE_HTTP_EVENT_WAIT, but we cannot solve it with the firewall settings because our outbound traffic is only allowed via a proxy. I assume that SQL server is not using the proxy when executing the backup to URL command. Is it possible to influence that?

    Reply

Leave a Reply