dslaby


I created a maintenance plan to remove files older than 2 weeks. The plan executes successfully, but does not work. I notice in the log that the single quotes are doubled. I ran one command which worked, but others did not. Is there a problem with the command Here is a copy of the log:

Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.1399
Report was generated on "SQL".
Maintenance Plan: Clean Up Backup Files
Duration: 00:00:12
Status: Succeeded.
Details:
Cleanup Master (SQL)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 2 Weeks
Task start: 9/12/2006 7:34 AM.
Task end: 9/12/2006 7:34 AM.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\SQLBackup\master'',N''bak'',N''08/29/2006 07:34:28''

Cleanup STS_SQL_1 (SQL)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 2 Weeks
Task start: 9/12/2006 7:34 AM.
Task end: 9/12/2006 7:34 AM.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\SQLBackup\STS_sql_1'',N''bak'',N''08/29/2006 07:34:39''

Cleanup LOG (SQL)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 2 Weeks
Task start: 9/12/2006 7:34 AM.
Task end: 9/12/2006 7:34 AM.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\SQLBackup\LOG'',N''bak'',N''08/29/2006 07:34:29''

Cleanup Refugeeweb (SQL)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 2 Weeks
Task start: 9/12/2006 7:34 AM.
Task end: 9/12/2006 7:34 AM.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\SQLBackup\RefugeeWeb'',N''bak'',N''08/29/2006 07:34:31''

Cleanup ReportServerTempDB (SQL)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 2 Weeks
Task start: 9/12/2006 7:34 AM.
Task end: 9/12/2006 7:34 AM.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\SQLBackup\ReportServerTempDB'',N''bak'',N''08/29/2006 07:34:34''

Cleanup MSDB (SQL)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 2 Weeks
Task start: 9/12/2006 7:34 AM.
Task end: 9/12/2006 7:34 AM.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\SQLBackup\msdb'',N''bak'',N''08/29/2006 07:34:30''

Cleanup Sharepoint (SQL)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 2 Weeks
Task start: 9/12/2006 7:34 AM.
Task end: 9/12/2006 7:34 AM.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\SQLBackup\Sharepoint'',N''bak'',N''08/29/2006 07:34:36''

Clean up RIMS (SQL)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 2 Weeks
Task start: 9/12/2006 7:34 AM.
Task end: 9/12/2006 7:34 AM.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\SQLBackup\RIMS'',N''bak'',N''08/29/2006 07:34:35''

Cleanup ReportServer (SQL)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 2 Weeks
Task start: 9/12/2006 7:34 AM.
Task end: 9/12/2006 7:34 AM.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\SQLBackup\ReportServer'',N''bak'',N''08/29/2006 07:34:32''

Cleanup BES (SQL)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 2 Weeks
Task start: 9/12/2006 7:34 AM.
Task end: 9/12/2006 7:34 AM.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\SQLBackup\BES'',N''bak'',N''08/29/2006 07:34:31''

Cleanup Model (SQL)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 2 Weeks
Task start: 9/12/2006 7:34 AM.
Task end: 9/12/2006 7:34 AM.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\SQLBackup\model'',N''bak'',N''08/29/2006 07:34:29''

Cleanup SWExchangeReports (SQL)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 2 Weeks
Task start: 9/12/2006 7:34 AM.
Task end: 9/12/2006 7:34 AM.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\SQLBackup\SSWExchangeReports'',N''bak'',N''08/29/2006 07:34:38''

Cleanup ScorecardServer (SQL)
Maintenance Cleanup on Local server connection
Cleanup Database Backup files
Age: Older than 2 Weeks
Task start: 9/12/2006 7:34 AM.
Task end: 9/12/2006 7:34 AM.
Success
Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\SQLBackup\ScorecardServer'',N''bak'',N''08/29/2006 07:34:35''




Re: xp_delete_file

Michael Hotek


This is a known bug that is fixed with SP1.





Re: xp_delete_file

scottdotnot

I have SP1 and have the same issu...





Re: xp_delete_file

CleverCoder

No, it's not fixed. Just checked.




Re: xp_delete_file

Richc12345

So has anyone got fix for this then




Re: xp_delete_file

Rik Clews

I've had the same problem (using sp2 ctp), I'm getting round it by setting up a separate job to call xp_delete_file. I didn't want to delete all the backups if the backup job was failing or stopped so I check how many files are there first. Ideally the cleanup task would do this so you can execute it after a successful backup but hey ho.

I must stress I've just done this in a hurry, and am leaving it to run on our test platform for the weekend to check it, so use with caution! (and you may have to enable xp_cmdshell)

DECLARE @ThreeDaysAgo VARCHAR(50)

SELECT @ThreeDaysAgo = CAST(DATEADD(d, -3, GETDATE()) AS VARCHAR)

-- NB this will delete any "bak" file not only the maintenance plan ones.

-- in case the backups are failing check we have at least 2 backups in the folder

-- check > 3 as we always have a final null record for the dir listing - note also that

-- its > 2 backups and not necessarily 2 backups from 3 days ago. Using 2 as its going

-- to run before the backup job, but the most likely cause of failure is lack of disk space

-- but we don't want a failure happening and end up with no backups

EXEC dbo.xp_cmdshell 'dir D:\Backups\db\*.bak /b'

IF @@ROWCOUNT > 3

BEGIN

EXECUTE dbo.xp_delete_file 0,N'D:\Backups\db',N'bak',@ThreeDaysAgo

-- only do log if deleting the main backup

EXEC dbo.xp_cmdshell 'dir D:\Backups\db\log\*.trn /b'

-- for logs we do 3 backups a day so check > 9

IF @@ROWCOUNT > 9

EXECUTE dbo.xp_delete_file 0,N'D:\Backups\db\log',N'trn',@ThreeDaysAgo

END





Re: xp_delete_file

The Power

Hi guys,

I've found out that the maintance plan WONT work when the dir is NTFS compressed.

So i've made this

Declare @daysOld int,@deletedate nvarchar(19) ,@strDir varchar(250)
declare @cmd nvarchar(2000)
declare @mainBackupDir varchar(2000)
set @mainBackupDir = 'F:\SQLBACKUP'
create table #Dir_list (strDir varchar(250) )
set @cmd = 'dir ' + @mainBackupDir + ' /ad /b'
insert into #Dir_list
exec xp_cmdshell @cmd
set @daysold = 3
set @deletedate = convert(varchar,getdate()-@daysold,101) + ' ' + convert(varchar,getdate()-@daysold,108)
DECLARE DeleteBackup CURSOR FOR
select strDir from #Dir_list
OPEN DeleteBackup
FETCH NEXT FROM DeleteBackup
INTO @strDir
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = @mainBackupDir + '\' + @strDir
print 'Deleting files from ' + @cmd + ' , that are older than ' + @deletedate
/* Full backup */
set @cmd = 'EXECUTE master.dbo.xp_delete_file 0,N''' + @cmd +''' ,N''bak'',N''' + @deletedate + ''''
print @cmd
exec sp_executesql @cmd
/* Transaction log */
set @cmd = @mainBackupDir + '\' + @strDir
set @cmd = 'EXECUTE master.dbo.xp_delete_file 0,N''' + @cmd +''' ,N''trn'',N''' + @deletedate + ''''
print @cmd
exec sp_executesql @cmd
FETCH NEXT FROM DeleteBackup
INTO @strDir
END
CLOSE
DeleteBackup
DEALLOCATE DeleteBackup
drop table #Dir_list

/Morten Petersen
DBA - TopNordic.com





Re: xp_delete_file

fredar

I try this :

DECLARE @currentdate datetime
DECLARE @olddate datetime

set @currentdate = CURRENT_TIMESTAMP
set @olddate = @currentdate - 4

EXECUTE master.dbo.xp_delete_file 0,N'D:\SauvegardeBK\SQL2005',N'bak',@olddate,1

I found that under windows authentication ( I'm using a domain admin account ) xp_delete_file doesn't delete any "bak" files,

but with "sa" it works ( All my "bak" files are under directories ).

( I hope that you anderstand what I write, my english is not very good )

Fred

Progress DBA





Re: xp_delete_file

Satya SKJ

YEs it will work due to that SQL Service account is ADMIN in this case where it has privilege to perform that operation. If it doesn't work with any other account that is used to perform this operation then make sure to set that user with similar privileges on Windows.




Re: xp_delete_file

SilentAJ

I had the same problem (SP1 deleting from a compressed volume)

The problem is caused by the requirement for the path to end with a '\'.

If you browse for the path this is not included but appears to be required by the xp_delete_file procedure





Re: xp_delete_file

Sm00ches

I had the same problem and had to delete old backup files from about 20 subdirectories. In the job that the maintanence plan created, I added a second step and included this code:

SP_MSFOREACHDB @COMMAND1 = 'USE

IF '' '' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''Northwind'', ''pubs'')

BEGIN

DECLARE @OLDDATE DATETIME

SET @OLDDATE = GETDATE() - 1

EXECUTE master.dbo.xp_delete_file 0, N''E:\SQL_Backups\ '', N''bak'', @OLDDATE, 1

END

'

*******************************************************************************************

SP_MSFOREACHDB is a system stored procedure that has a cursor to go through ALL databases on the server. The ' ' is the variable that holds the name of the database.





Re: xp_delete_file

Charlie___XXX

i'm having the same problem with xp_delete_file. it deletes my database backups properly, but it won't delete my maintenance plan text reports.

how do you get the maintenance plan to show each command it executes in its log file my log files don't show the command. yours contains Command:EXECUTE master.dbo.xp_delete_file 0,N''F:\SQLBackup\ScorecardServer'',N''bak'',N''08/29/2006 07:34:35''





Re: xp_delete_file

Sm00ches

Charlie___XXX,

This may seem like a dumb question, but did you try changing 'bak' to 'txt' without a dot (.txt)

As for the maintenace plan showing the commands it executes, I'm not sure... But that's a good question... I'l do some research and let you know if I come up with anything...





Re: xp_delete_file

Maurice McIver

I'm running a 64 bit version, 9.00.1406.00

I have this problem, and when I paste the TSQL generated by the maintenance plan into a new query window and remove the period before 'bak', it works.

Next step is to find out if there are any service packs available for the 64 bit version of sql server.






Re: xp_delete_file

Maurice McIver

I'm using this code now instead, and it's working for me:

declare @DateString char(8)
set @DateString = CONVERT(CHAR(8), dateadd(d,-7,getdate()), 1)
EXECUTE master.dbo.xp_delete_file 0,N'C:\DatabasesBackup\',N'trn', @DateString