Xp_delete_file and Preemptive_Os_GetProcAddress waits
If you are noticing a number of waits showing up on your SQL Server of type PREEMPTIVE_OS_GETPROCADDRESS and it is linked to the system procedure xp_delete_file the you may want to look at your backup cleanup jobs.
Lets take a look at what you may actually be seeing – see image above
What is shown in the image above is that historic wait monitoring feature from Database Health Monitor, and it has been monitoring for a few days and the query with the most waits is xp_delete_file. When we drill down on the details we can see that the wait type is PREEMPTIVE_OS_GETPROCADDRESS.
Tech Deep Dive: Remembering back to my Windows programming from many years ago the procedure GetProcAddress was used to load a DLL, and to get a pointer to a function in that DLL so that it could call them be called dynamically. What that really means is that SQL Server is loading and then calling a function from a DLL to run and search your backup path to clean up SQL Server backup files. This is what SQL Server is going to call natively compiled code to search your drive and clean up the files. Interesting, but that doesn’t explain why we have so many waits associated with this.
On the server in question, it is a test server that I used when building by backup and restore training video, and I had configured maintenance plans to run backups, and to clean up those backup files over time. This server is running full backups, differential backups, and transaction log backups. The transaction log backups are running ever 25 seconds, so there are a whole lot of files to search through when looking to purge older backup files. Now that being said, that does not really explain why we are seeing this wait all day long. Typically you would want to purge the older files one or twice a day, or even less frequently.
As I start looking into the maintenance plans, I find the following schedule for the cleanup log files which shows that it is being run every 45 minutes. That schedule worked great for my backup demo, but didn’t work so good when I left it running.
So the fix is to set the cleanup script to run once a day during a slower time of the day. Making that change, and letting it run for a couple days then brings us to a point where the xp_delete_file procedure will no longer shows up as the query with the most waits on this system.
The way I tracked this down was to find the query with the most waits, then start investigating what could be causing that, and then adjust the bad schedule. All is good, and this server is in a much better position.
This example was not on a production system, but I have seen very similar things on production systems when you put too much into your backup and restore maintenance plans.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Leave a Reply