xp_delete_file and other vendors

Question

xp_delete_file and other vendors

Adam Bean

One Orange Chip

Points: 26631
More actions
October 20, 2008 at 10:52 am

#192352

Hey all,
Without using xp_cmdshell or an outside process (vbscript/clr/etc.), is it possible to clean up file system backups staying with in TSQL? I can't seem to get xp_delete_file to work with LiteSpeed. Haven't tried RedGate nor Idera yet ...
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 1

For Litespeed, you can either use 'xp_procedure' or 'xp_slssqlmaint'. I use 'xp_slssqlmaint' and set it up as:

Execute master.dbo.xp_slssqlmaint '-MAINTDEL -DELFOLDER "%backupDirectory%%database% -DELEXTENSION "%extension%" -DELUNIT "%minutes%" -DELUNITTYPE "minutes" -DELUSEAGE';

Replace the %var% in the above with the appropriate information to delete the files in that directory.

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

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Thanks Jeff ... unfortunately getting the following:

EAccessViolation - Access violation at address 00403E7A in module 'slssqlmaint.exe'. Read of address 0000000F

D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\slssqlmaint.exe Process Exit Code: 1 (Failed)

Msg 50000, Level 19, State 1, Line 0

slssqlmaint failed

Any thoughts? I have sysadmin rights and LiteSpeed works without issue on this server ...

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 3

Adam,

xp_delete_file will not work for any files other than SQL Server backup files and SQL Server backup log files. It actually reads the header records of the files to verify that they are in fact SQL Server generated files prior to deleting. I started an article about this a while ago but never finished after some frustration with it.

Hope this helps.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 4

I seem to remember getting that error on an older version. I ended up using the SSMS plug in, setting it up for the deletion I wanted and then modifying the string it generated (use View T-SQL button to get the call).

Another thing I noticed is that you will get this kind of error if you pass in the interval incorrectly. Make sure you have the correct unit type (minute not minutes, hour not hours, etc...)

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