Error: Deleting Old Backup Files with xp_delete_file

Question

Error: Deleting Old Backup Files with xp_delete_file

apat

SSCertifiable

Points: 5929
More actions
January 25, 2012 at 2:33 pm

#249729

Hi All, I am trying to delete old backup files using below sql. I have SQL 2005 SP2 (9.00.3042.00).
DECLARE @currentdate datetime
DECLARE @olddate datetime
set @currentdate = getdate()
set @olddate = @currentdate - 4
EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\Backup\Test',N'bak',@olddate,1
I am getting below error and couldn't find what is causing this error.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Can anyone please suggest what's wrong here? Thanks in advance...

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

apat (1/25/2012)
Hi All, I am trying to delete old backup files using below sql. I have SQL 2005 SP2 (9.00.3042.00).
DECLARE @currentdate datetime
DECLARE @olddate datetime
set @currentdate = getdate()
set @olddate = @currentdate - 4
EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL\Backup\Test',N'bak',@olddate,1
I am getting below error and couldn't find what is causing this error.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Can anyone please suggest what's wrong here? Thanks in advance...

In sounds like it may be a privs problem. Are you running as "SA" or ???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

apat SSCertifiable Points: 5929 More actions · Answer 2

apat

SSCertifiable

Points: 5929

January 25, 2012 at 8:33 pm

#1438551

Yes I am running it as SA.

savethytrees SSCommitted Points: 1968 More actions · Answer 3

Try running Process Explorer and see what process is holding the lock on those files.

http://technet.microsoft.com/en-us/sysinternals/bb896653

Blog
http://saveadba.blogspot.com/

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 4

apat-

xp_delete_file is undocumented (try looking it up in Books Online and you won't find it)

Thus, you should be careful with it's use, or find another mechanism to accomplish your goals.

A bit more on the topic...

I've used it in the past, sometimes it works, sometimes not. Best not to use it if you need reliability...

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)

apat SSCertifiable Points: 5929 More actions · Answer 5

when I use my script, it even disconnects me from the server with the above error. I need to connect to the server again. I don't see why is that happening?

apat SSCertifiable Points: 5929 More actions · Answer 6

I tried all these options but doesnt work. I even tried adding Maintenance CleanUP task to delete old backup files.

Source: Maintenance Cleanup Task Execute SQL Task Description: Executing the query "EXECUTE master.dbo.xp_delete_file 0,N'M:\MSSQL\Backups\db1',N'trn',N'2012-01-25T13:00:00' " failed with the following error: "A severe error occurred on the current command. The results, if any, should be discarded. A severe error occurred on the current command. The results, if any, should ... The package execution fa... The step failed.

I guess its some server level setting issue? Any suggestions?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

The version you are on had issues with maintenance plans and there were also issues with xp_delete_file.

You really need to upgrade to SP3 - and if that is not possible, you want to get to 9.00.3054 at a minimum.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Todd Carrier Hall of Fame Points: 3951 More actions · Answer 8

In a twist of fate, I was sure I had used this procedure in a homegrown script. Works well on SQL 2008, 2008 R2, even SQL 2005 (if the latest service pack is applied).

How frustrating... in this case, I'm not so mad that this procedure is undocumented, but rather my company refuses to apply Service Packs. These SQL 2005 instances are at RTM. I pains me to no end as a DBA that the servers are not up to the latest SP. QA and Development apparently don't agree, perhaps they would like to support Microsoft Products.

Anyone have this issue? How to encourage, or even force the application of Service Packs? I am gaining no traction in the matter. Please let me apply the service packs!

Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)