Maintenance Plan not deleting physical backup file

  • I've got a strange error. We have a network share we back up our servers to. About a month ago we got an error because the drive was full. Taking a look at it, none of the backup files had been deleted since we upgraded to 2005.

    After manually deleting enough files to get everything running smoothly i took a look at the maintenance plan and before the back up operation begins it is supposed to delete all jobs and backup files older than 4 days.

    So I continued to watch as the job ran nightly, but no physical files have been deleted.

    So I manually ran on one of the servers,

    declare @oldestDate datetime

    set @oldestDate = cast(dateadd(dd, -4,CURRENT_TIMESTAMP) as datetime)

    exec msdb.dbo.sp_delete_backuphistory @oldest_date=@oldestDate

    it deleted the backup history but of course not the physical files, i was debating writing a script that would delete them but figured i'd ask for help before I do, as I would prefer not to have to do this manually.

  • In 2005 - you have to add the Maintenance Cleanup Task to the maintenance plan and configure it. Sounds to me like that does not exist in the plan and that is why you are not seeing the files cleaned up.

    You will need two tasks - one for backup files and one for transaction log backup files.

    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

  • Also check to see what you are trying to delete - if you put .BAK in the window, it won't delete. Needs 'BAK' only, assuming that your backup is using the standard .bak extension.

  • Manually use this query and schedule it..

    declare @dt datetime

    select @dt=getdate() - N-- N is the files with Ndays old

    EXECUTE master.dbo.xp_delete_file 0,N'Backup Location',N'BAK',@dt

  • Hey Guys,

    Thanks for the reply's. I didn't have to try the script the first 2 options worked for me. It was funny the first time it didn't and then i realized that I had put the period in the .bak file extension just like you said.

    I had been waiting until the weekend to get it resolved and knocked it out quickly in several different environments!

    Thanks to all!

  • Hi, I'm having the same issue, I've got the cleanup job scheduled on the Maintenance Plan, it shows as it runs fine, finishes successfully but it doesn't delete the backups and the transaction logs. This was working fine but it started to work this way a couple days back.

    I tried this solution, I run the manual script:

    declare @dt datetime

    select @dt=getdate() - 1-- N is the files with Ndays old

    EXECUTE master.dbo.xp_delete_file 0,N'F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\',N'BAK',@dt

    It succeeds and it still doesn't remove the bak files.

    Any idea?

    I'm running:

    Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) Apr 14 2006 01:12:25 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    Thanks

    Lucas

  • Try N'.BAK' instead of N'BAK'.

  • MANU-J. (5/11/2010)


    Try N'.BAK' instead of N'BAK'.

    Same thing, completes successfully, but it does nothing.

  • Try N'*.*' instead of 'BAK' if there are no other files than backup files in that folder.

  • MANU-J. (5/11/2010)


    Try N'*.*' instead of 'BAK' if there are no other files than backup files in that folder.

    Same thing, completes successfully, and then it does nothing, the bak's are still there :s

  • Check the File type of files residing under that location.

    It must be 'BAK File'.

  • Wow this is an old thread from a Customer ago. funny where a little over a year will take you.

    I've used this cleanup task alot since I left so just to validate a couple things.

    1. In your backup plans you specify .bak for the backup files and .trn for the log files

    2. you will need 2 different maintenance cleanup tasks one for each file type extention

    3. double check that you are pointed to the folder where the backup files are contained

    4. if you have the option checked under your backup plans "create a sub folder for each database"

    ensure that in the clean up task that you have checked "include first level sub folders"

    Lastly, here is a script that i've used for a lot of things. It uses XP command shell to transvers a folder and delete the files within by a specified date that contains a particular bit of text in the file name.

    I added the file path that you had posted earlier, you will need to add the database backup file name where the [INSERTDATBASENAME] Blocks are. if you want to you could add the logic to check for an extention type. currently the -4 in the dd field will delete all items older than 4 days, you can adjust this to whatever you like

    --**********************************************************

    --Enable the ability to set advance settings and xp_cmdshell

    --**********************************************************

    exec sp_configure 'show advanced options', 1

    go

    reconfigure

    go

    exec sp_configure 'xp_cmdshell', 1

    go

    reconfigure

    go

    --******************************************************

    --Declare variable's to use in the code and set their values

    --******************************************************

    declare @myTable as table (

    [myID] [int] IDENTITY(1,1) NOT NULL,

    FileInfo varchar(max))

    declare @myTable2 as table (

    [myID] [int] IDENTITY(1,1) NOT NULL,

    FileNames varchar(max))

    declare @myTable3 as table (

    [myID] [int] IDENTITY(1,1) NOT NULL,

    FileNames varchar(max),

    FileCreationDate datetime)

    declare @myTable4 as table (

    [myID] [int] IDENTITY(1,1) NOT NULL,

    FileNames varchar(max),

    FileCreationDate datetime)

    declare @filecount int

    declare @i int

    declare @filepath varchar(100)

    declare @sqlcmd varchar(max)

    declare @sqlcmd2 varchar(max)

    declare @sqlcmd3 varchar(max)

    declare @filename1 varchar(100)

    set @filepath = '"F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\"'

    set @sqlcmd= 'master..xp_cmdshell ' + ''''+ 'dir ' + @filepath + ''''

    set @sqlcmd2= 'master..xp_cmdshell ' + ''''+ 'dir ' + @filepath + ' /b' + ''''

    set @i = 1

    --*********************************************************

    --insert the contents of the direcotry path into our table

    --variables. @myTable will hold the file names, @myTable2

    --will hold all of the file names and the size and date of

    --creation

    --********************************************************

    insert @myTable exec(@sqlcmd)

    insert @myTable2 exec(@sqlcmd2)

    --******************************************************

    --we only want to delete the database files delete all info

    --that is null or is not an a databasefile

    --******************************************************

    delete from

    @myTable2

    where FileNames not like '[INSERTDATABASENAME]%' or FileNames is null

    --******************************************************

    --Insert into @myTable3 the FileName and Date info from

    --the file directory, convert the char date text into a

    --real datetime field

    --******************************************************

    insert into @myTable3

    select

    mt2.filenames,

    (

    convert(datetime,left((select mt1.fileinfo from @myTable mt1 where mt1.FileInfo like ('%' + mt2.FileNames + '%')), 20))

    ) as FileCreationDate

    from @myTable2 mt2

    --*****************************************************

    --Reinsert all data into @myTable4 so the myID column

    --will incriment at the same rate as the counter we will

    --use to delete these files from the directory

    --*****************************************************

    insert into @myTable4

    select FileNames, FileCreationDate

    from @myTable3

    order by FileCreationDate

    --*****************************************************

    --Set the @filecount variable that we will use for our

    --loop

    --*****************************************************

    set @filecount = (select COUNT (*) from @myTable3 where FileCreationDate < DATEADD(dd, -4,getdate()))

    --*******************************************************

    --Begin loop, @i should match @myTable4.myID, so that way

    --we can select a filename from @myTable4 to delete from

    --the file structure.

    --*******************************************************

    while @i <= @filecount

    Begin

    set @filename1 = (select filenames from @myTable4 where myID= @i)

    set @sqlCmd3 = 'master..xp_cmdshell' + ''''+ 'del /Q ' + @filepath + @filename1 + ''''

    exec(@sqlCmd3)

    set @filename1=''

    set @sqlcmd3=''

    set @i = @i + 1

    end

    --*******************************************************

    --Re-Configure the server not to display advanced options

    --Re-Configure the server not to allow xp_cmdshell commands

    --to be run

    --*******************************************************

    exec sp_configure 'show advanced options', 1

    go

    reconfigure

    go

    exec sp_configure 'xp_cmdshell', 0

    go

    reconfigure

    go

    exec sp_configure 'show advanced options', 0

    go

    reconfigure

    I hope this helps

    P.S. This script does not transverse sub folders, it must be pointed to the folder containing the backups and run against it.

    if you comment out the code that performs the deletes you can query any of the tables variables to see that it is picking up the files that you want to delete.

  • Bradly,

    First of all thanks for the help.

    Now for all the first checkings, everything is set fine on the maintenance plans, actually it worked for over a year. I have the trans files on sub folders and there is a cleanup task for trn files checking sub folders, and the bak files are in the backup root folder and it has a cleanup task for bak files without checking sub folders. I tryied with the .(dot) and without it for bak and trn files. All with no luck.

    I've tried your script and get some errors:

    Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

    Configuration option 'xp_cmdshell' changed from 0 to 1. Run the RECONFIGURE statement to install.

    (34 row(s) affected)

    (25 row(s) affected)

    (20 row(s) affected)

    Msg 512, Level 16, State 1, Line 58

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    The statement has been terminated.

    (0 row(s) affected)

    Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.

    Configuration option 'xp_cmdshell' changed from 1 to 0. Run the RECONFIGURE statement to install.

    Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

    Thanks Again

    Lucas

  • Try:

    --**********************************************************

    --Enable the ability to set advance settings and xp_cmdshell

    --**********************************************************

    exec sp_configure 'show advanced options', 1

    go

    reconfigure

    go

    exec sp_configure 'xp_cmdshell', 1

    go

    reconfigure

    go

    --******************************************************

    --Declare variable's to use in the code and set their values

    --******************************************************

    declare @myTable as table (

    [myID] [int] IDENTITY(1,1) NOT NULL,

    FileInfo varchar(max))

    declare @myTable2 as table (

    [myID] [int] IDENTITY(1,1) NOT NULL,

    FileNames varchar(max))

    declare @myTable3 as table (

    [myID] [int] IDENTITY(1,1) NOT NULL,

    FileNames varchar(max),

    FileCreationDate datetime)

    declare @myTable4 as table (

    [myID] [int] IDENTITY(1,1) NOT NULL,

    FileNames varchar(max),

    FileCreationDate datetime)

    declare @filecount int

    declare @i int

    declare @filepath varchar(100)

    declare @sqlcmd varchar(max)

    declare @sqlcmd2 varchar(max)

    declare @sqlcmd3 varchar(max)

    declare @filename1 varchar(100)

    set @filepath = '"S:\MSSQL10.MSSQLSERVER\MSSQL\DATA\"'

    set @sqlcmd= 'master..xp_cmdshell ' + ''''+ 'dir ' + @filepath + ''''

    set @sqlcmd2= 'master..xp_cmdshell ' + ''''+ 'dir ' + @filepath + ' /b' + ''''

    set @i = 1

    --*********************************************************

    --insert the contents of the direcotry path into our table

    --variables. @myTable will hold the file names, @myTable2

    --will hold all of the file names and the size and date of

    --creation

    --********************************************************

    insert @myTable exec(@sqlcmd)

    insert @myTable2 exec(@sqlcmd2)

    --******************************************************

    --we only want to delete the database files delete all info

    --that is null or is not an a databasefile

    --******************************************************

    delete from

    @myTable2

    where FileNames not like '[INSERTDATABASENAME]%' or FileNames is null

    select * from @myTable2

    --*******************************************************

    --Re-Configure the server not to display advanced options

    --Re-Configure the server not to allow xp_cmdshell commands

    --to be run

    --*******************************************************

    exec sp_configure 'show advanced options', 1

    go

    reconfigure

    go

    exec sp_configure 'xp_cmdshell', 0

    go

    reconfigure

    go

    exec sp_configure 'show advanced options', 0

    go

    reconfigure

    what are your query results?

  • Query:

    --**********************************************************

    --Enable the ability to set advance settings and xp_cmdshell

    --**********************************************************

    exec sp_configure 'show advanced options', 1

    go

    reconfigure

    go

    exec sp_configure 'xp_cmdshell', 1

    go

    reconfigure

    go

    --******************************************************

    --Declare variable's to use in the code and set their values

    --******************************************************

    declare @myTable as table (

    [myID] [int] IDENTITY(1,1) NOT NULL,

    FileInfo varchar(max))

    declare @myTable2 as table (

    [myID] [int] IDENTITY(1,1) NOT NULL,

    FileNames varchar(max))

    declare @myTable3 as table (

    [myID] [int] IDENTITY(1,1) NOT NULL,

    FileNames varchar(max),

    FileCreationDate datetime)

    declare @myTable4 as table (

    [myID] [int] IDENTITY(1,1) NOT NULL,

    FileNames varchar(max),

    FileCreationDate datetime)

    declare @filecount int

    declare @i int

    declare @filepath varchar(100)

    declare @sqlcmd varchar(max)

    declare @sqlcmd2 varchar(max)

    declare @sqlcmd3 varchar(max)

    declare @filename1 varchar(100)

    set @filepath = '"F:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\"'

    set @sqlcmd= 'master..xp_cmdshell ' + ''''+ 'dir ' + @filepath + ''''

    set @sqlcmd2= 'master..xp_cmdshell ' + ''''+ 'dir ' + @filepath + ' /b' + ''''

    set @i = 1

    --*********************************************************

    --insert the contents of the direcotry path into our table

    --variables. @myTable will hold the file names, @myTable2

    --will hold all of the file names and the size and date of

    --creation

    --********************************************************

    insert @myTable exec(@sqlcmd)

    insert @myTable2 exec(@sqlcmd2)

    --******************************************************

    --we only want to delete the database files delete all info

    --that is null or is not an a databasefile

    --******************************************************

    delete from

    @myTable2

    where FileNames not like '[HFMSYSDB]%' or FileNames is null

    select * from @myTable2

    --*******************************************************

    --Re-Configure the server not to display advanced options

    --Re-Configure the server not to allow xp_cmdshell commands

    --to be run

    --*******************************************************

    exec sp_configure 'show advanced options', 1

    go

    reconfigure

    go

    exec sp_configure 'xp_cmdshell', 0

    go

    reconfigure

    go

    exec sp_configure 'show advanced options', 0

    go

    reconfigure

    Results:

    1DEVELOP1

    2DEVELOP1_backup_201005110400.bak

    3DEVELOP1_backup_201005120400.bak

    4DEVELOP_backup_201005110400.bak

    5DEVELOP_backup_201005120400.bak

    6HBIPLUSSYSDB

    7HBIPLUSSYSDB_backup_201005110400.bak

    8HBIPLUSSYSDB_backup_201005120400.bak

    9HFMSYSDB

    10HFMSYSDB_backup_201005110400.bak

    11HFMSYSDB_backup_201005120400.bak

    12HISTDATA_backup_201005110400.bak

    13HISTDATA_backup_201005120400.bak

    14Historical_backup_201005110400.bak

    15Historical_backup_201005120400.bak

    16HSSSYSDB

    17HSSSYSDB_backup_201005110400.bak

    18HSSSYSDB_backup_201005120400.bak

    19master

    20model

    21msdb

Viewing 15 posts - 1 through 15 (of 31 total)

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