t-sql/xp_delete_file problem

Question

t-sql/xp_delete_file problem

Shabba

SSCertifiable

Points: 5007
More actions
April 18, 2007 at 10:36 am

#173086

Hi,
Anyone know why this doesn't work? I'm running SQL 2005 SE build 3052.
Which parameter is invalid?
declare
@dt datetime
select @dt=getdate()-1
EXECUTE
master.dbo.xp_delete_file 0,N'F:\MSSQLSERVER\MSSQL.1\MSSQL\backup',@dt,1

Msg 22049, Level 15, State 0, Line 0
Error executing extended stored procedure: Invalid Parameter

Viewing 11 posts - 1 through 10 (of 10 total)

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

David McFarland SSChampion Points: 11815 More actions · Answer 1

Since it's undocumented, I'm not sure, and it's been a while, but doesn't the date need to be an nvarchar datatype, instead of datetime? If not, I know there have been issues of "wonkiness" with maintenance plans lately, so second, I'd probably try to add a backslash as the end of your path, if the first doesn't fix it.

Edit: It looks like the datetime should work, but it looks like you're missing a parameter at second glance. I'm checking now and will respond back.

David McFarland SSChampion Points: 11815 More actions · Answer 2

Yep, you're missing the "extension", which goes between the path and the datetime. Use the extension without the period, such as N'bak'

David McFarland SSChampion Points: 11815 More actions · Answer 3

Also, isn't it master.sys.xp_delete_file for 2005, as opposed to dbo?

Shabba SSCertifiable Points: 5007 More actions · Answer 4

Hi David,

Thanks for the replies. I added the N'bak' parameter and now I get a different error.

EXECUTE

master.dbo.xp_delete_file 0,N'F:\MSSQLSERVER\MSSQL.1\MSSQL\backup',N'bak',@dt,1

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable "@dt".

If I run...

EXECUTE master.dbo.xp_delete_file 0,N'F:\MSSQLSERVER\MSSQL.1\MSSQL\backup',N'bak',N'2007-04-14T20:00:05',1

The command parses & executes successfully...

Cheers,

UPDATE I've just added the trailing backslash and now the command parses & executes successfully, however it doesn't remove the older bak files... back to the drawing board!

Mark

Anders Pedersen SSChampion Points: 11410 More actions · Answer 5

I just tested the following and it worked fine.

declare

@dt nvarchar(19)

set

@dt = N'2007-04-18 08:24:07'

EXECUTE

master.dbo.xp_delete_file 0,N'M:\Backups\Master',N'bak', @dt

Shabba SSCertifiable Points: 5007 More actions · Answer 6

Many Thanks Anders... worked a treat, if only I knew more about t-sql/scripting.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 7

For clarity here is the entire script I use for cleaning up all my backups. I posted the script I run for my backups somewhere else around here the other day... It will delete all files more than 2 days old at the time it runs, from the subdirectories for each database.

set quoted_identifier off

go

declare

@SQL varchar(max)

declare

@runtime varchar(22)

select

@runtime = convert(varchar(22),dateadd(dd,-2,getdate()),120)

DECLARE

DBNames CURSOR

READ_ONLY

FOR

select name from sys.databases

where

name not in ('anderstest' , 'tblCheck', 'Northwind','tempdb')

DECLARE

@name sysname

OPEN

DBNames

FETCH

NEXT FROM DBNames INTO @name

WHILE

(@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

select @SQL = "EXECUTE master.dbo.xp_delete_file 0,N'M:\Backups\" +@name +"',N'bak', N'" + @runtime +"'"

exec (@SQL)

--select @SQL

END

FETCH NEXT FROM DBNames INTO @name

END

CLOSE

DBNames

DEALLOCATE

DBNames

GO

Juny Laksmana SSC Journeyman Points: 75 More actions · Answer 8

I am wondering if someone can help me with the problem of executing "xp_delete_file".

I created a job to delete backup files that are older than 2 days old. No syntax error and the same script runs on SQL Server 2005 ver 9.02153, but it does not run on SQL Server ver 9.00.1399.06.

Is there a workaround for this? or do I need to upgrade SQL Server to make this script to run?

The script looks like the following:

DECLARE @currentdate datetime

DECLARE @olddate datetime

set @currentdate = CURRENT_TIMESTAMP

set @olddate= @currentdate - 2

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL2005\DATA\MSSQL.1\MSSQL\Backup',N'bak',@olddate,1

EXECUTE master.dbo.xp_delete_file 0,N'D:\MSSQL2005\DATA\MSSQL.1\MSSQL\Tran_Log Backup',N'trn',@olddate,1

Thank you.

JL

hvera SSC Enthusiast Points: 108 More actions · Answer 9

I had this problem for ages the issue I found out in the end was the profile that SQLServer was running didn't have premission to delete the file from the system.

The easiest way to determine that you have premission is to run the following command to delete exactly the file you want:

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL.2008\Archives\Db\Daily\Db1_backup_2009_06_16_191120_2202108.bak'

GO

If your getting Access Denied Error 5 then need go to the security settings on this folder in Windows Explorer and and change security for the machine users to delete read and write as well.

Once you change this you should notice you can delete the file straight away from SQL.

cheetah2399 Mr or Mrs. 500 Points: 567 More actions · Answer 10

We have found that have zipGenius installed on the server and using Hyperbac to do the SQL backups causing this issue as well. Once zipGenius was removed the problem went away.