March 2, 2009 at 12:59 pm
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.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
March 2, 2009 at 1:49 pm
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
March 2, 2009 at 3:03 pm
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.
March 7, 2009 at 12:42 pm
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!
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 11, 2010 at 1:47 pm
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
May 11, 2010 at 2:10 pm
Try N'.BAK' instead of N'BAK'.
May 11, 2010 at 2:14 pm
MANU-J. (5/11/2010)
Try N'.BAK' instead of N'BAK'.
Same thing, completes successfully, but it does nothing.
May 11, 2010 at 2:27 pm
Try N'*.*' instead of 'BAK' if there are no other files than backup files in that folder.
May 11, 2010 at 2:35 pm
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
May 11, 2010 at 2:52 pm
Check the File type of files residing under that location.
It must be 'BAK File'.
May 12, 2010 at 7:02 am
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.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 12, 2010 at 8:32 am
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
May 12, 2010 at 8:37 am
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?
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
May 12, 2010 at 8:44 am
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