Hi All,

I do have an issue with deleting backup files through maintenance plan and t-sql. The XP_DELETE_FILE stored procedure doesn't work and the SQL agent jobs keep running and not doing anything, not even backup the databases.

EXECUTE master.dbo.xp_delete_file 0,N'K:\MSSQL\Backup',N'BAK',N'2015-07-15T18:22:01',1

EXECUTE master.dbo.xp_delete_file 0,N'K:\MSSQL\Backup',N'TRN',N'2015-07-15T18:22:25',1

And there is PREEMPTIVE_OS_GETPROCADDRESS wait type under master database with above command.

I don't want to restart the server as critical applications are running on this server.

This is so urgent.

Can you please let me know how to fix auto delete or any other solutions?

Thanks for your help in advance.

Regards,

So these calls to xp_delete_file never completes? The wait type PREEMPTIVE_OS_GETPROCADDRESS indicates that SQL Server is stuck in finding the address to the extended stored procedure. That would be a dreary situation that would see very few options to recover from without restarting SQL Server.

I found a blog post from 2010 that suggests that due to a bug, this wait state could possibly mean MSQL_XP, that would suggest that it is the delete operation that for some reason is stuck. In the latter case, the chances to resolve it seem slightly better. That is, rather than 0.001 it would be maybe 0.01.

In either case, you would need to be a real wizard of Windows live debugging to sort this out. If you have a good support contract with SQL Server so that you can get an escalation engineer working on the case within hours, try that. Else - I'm afraid that it is only a matter of finding the least bad occasion for a restart.

What does "SELECT @@version" report?

Do you have any antivirues running on the machine?

Hello

is xp_cmdshell enabled on your SQl Server instance?  How are you running the function; are you calling it directly or as a part of a SQL Server Agent Job?

maybe this?

https://blog.netnerds.net/2012/03/when-xp_delete_files-doesnt-work-as-expected/

Check this link out as well

http://stackoverflow.com/questions/21506219/xp-delete-file-not-removing-old-backups

Yes, XP_CMDSHELL is enabled.  as part of SQL Server Agent job through maintenance plan. I tried to kill the sessions but they are stucked in the Kill/Rollback stage.

Not sure what to do?

Hello,

The job was running okay till 15th July, 2015. From that day, it stuck with this PREEMPTIVE_OS_GETPROCADDRESS wait type under master database.

The SQL service account has local admin access and SYSADMIN access to SQL Server.

EXECUTE master.dbo.xp_delete_file 0,N'K:\MSSQL\Backup\',N'bak',N'2015-07-27T11:37:55',1
EXECUTE master.dbo.xp_delete_file 0,N'K:\MSSQL\Backup\',N'trn',N'2015-07-27T11:39:50',1
EXECUTE master.dbo.xp_delete_file 0,N'K:\MSSQL\Backup\',N'dif',N'2015-07-27T11:40:00',1

Not sure how to resolve it?

Regards,

Naman

Hello,

The job was running okay till 15th July, 2015. From that day, it stuck with this PREEMPTIVE_OS_GETPROCADDRESS wait type under master database.

The SQL service account has local admin access and SYSADMIN access to SQL Server.

EXECUTE master.dbo.xp_delete_file 0,N'K:\MSSQL\Backup\',N'bak',N'2015-07-27T11:37:55',1
EXECUTE master.dbo.xp_delete_file 0,N'K:\MSSQL\Backup\',N'trn',N'2015-07-27T11:39:50',1
EXECUTE master.dbo.xp_delete_file 0,N'K:\MSSQL\Backup\',N'dif',N'2015-07-27T11:40:00',1

Not sure how to resolve it?

Hello,

Select @@VERSION works okay.

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)   Jun 28 2012 08:36:30   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)