delete old backup files

• Niyala

  SSCrazy

  Points: 2778

  More actions

  September 29, 2009 at 7:27 am

  #215838

  Hi List,

  I am running some sql server 2000 enterprise ed. and several MSDE in our production environment. Some of the servers are running out of disk space. Therefore, I would like to delete backup files (bak & trn) older than 3 days.

  I was wondering if any one has a precise script that I can run as SQL Server Agent Job once every three days to delete older files from the hard disk?

  Thanks a lot for your hint.

  Regards

  Niyala

• MANU-J.

  SSC-Dedicated

  Points: 31126

  More actions

  September 29, 2009 at 4:07 pm

  #1059755

  Try the below one(found it here only):

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE PROCEDURE [dbo].[usp_DeleteOldMaintenancePlanLogFiles]

  -- 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\', 17

  @basedir nvarchar(255),

  @days_old_to_allow int = 30

  --***Enable XP_CMDSHELL to make this work.***

  AS

  BEGIN

  SET NOCOUNT ON

  declare @mtime datetime

  declare @file nvarchar(255)

  declare @fullpath nvarchar(255)

  declare @daysold int

  declare @cmd nvarchar(255)

  CREATE TABLE #t_dir

  ( InLine varchar(250))

  -- Get a directory listing and Insert into #t_dir

  SET @cmd = 'dir "' + @basedir + '" /A-D' -- /A-D, no directories

  INSERT INTO #t_dir

  EXEC master.dbo.xp_cmdshell @cmd

  -- Clean up unwanted rows in the table

  DELETE FROM #t_dir

  WHERE InLine like ' %'

  or InLine = ''

  or Inline IS NULL

  -- Put the to-be-deleted filenames into cursor

  DECLARE c_files CURSOR FOR

  SELECT convert(datetime, substring(replace(replace(replace(InLine, ' ', '~'), ' ', ''), '~', ' '),1,18) ) as dtime,

  rtrim(substring(InLine, 40, len(InLine))) as filen,

  datediff(dd, convert(datetime, substring(replace(replace(replace(InLine, ' ', '~'), ' ', ''), '~', ' '),1,18) )

  , getdate()) as daysold

  FROM #t_dir

  WHERE

  rtrim(substring(InLine, 40, len(InLine))) like '%plan%'

  and

  datediff(dd,CONVERT(datetime,substring(ltrim(rtrim(substring(InLine, 40, len(InLine)))),

  LEN(ltrim(rtrim(substring(InLine, 40, len(InLine)))))-17,8)),getdate())

  >@days_old_to_allow

  --Delete the files--

  OPEN c_files

  FETCH NEXT FROM c_files INTO @mtime, @file, @daysold

  WHILE(@@fetch_status = 0)

  BEGIN

  SET @fullpath = @basedir + '\' + @file

  PRINT 'Going to delete old file: ' + @fullpath + ', daysold=' + cast(@daysold as nvarchar)

  SET @cmd = 'del /Q "' + @fullpath + '"'

  print @cmd

  -- no turning back now!

  EXEC master.dbo.xp_cmdshell @cmd, no_output

  FETCH NEXT FROM c_files INTO @mtime, @file, @daysold

  END

  CLOSE c_files

  DEALLOCATE c_files

  DROP TABLE #t_dir

  END

  MJ

• Vishal Singh

  SSCertifiable

  Points: 5830

  More actions

  September 29, 2009 at 10:52 pm

  #1059797

  Niyala (9/29/2009)

  ---

  Hi List,

  I am running some sql server 2000 enterprise ed. and several MSDE in our production environment. Some of the servers are running out of disk space. Therefore, I would like to delete backup files (bak & trn) older than 3 days.

  I was wondering if any one has a precise script that I can run as SQL Server Agent Job once every three days to delete older files from the hard disk?

  Thanks a lot for your hint.

  Regards

  Niyala

  Can you explain some more as to how your existing backup scheme looks like, how you are taking the backup (i.e through maintenance plan, SQL job, Script etc.)

  -Forum Etiquette: How to post Performance Problems[/url]

  -Forum Etiquette: How to post data/code to get the best help [/url]

• Niyala

  SSCrazy

  Points: 2778

  More actions

  September 30, 2009 at 12:28 am

  #1059806

  Hi,

  Thanks indeed for your information. As to how I am currently doing my backups; I am using T-SQL SQL Agent jobs that run once every night to take a full back up and every 60 minutes transaction log back up. I changed the switch "Retain" to 5 days, for example. However, it is not doing what it should be doing. Therefore, I wanted to implement a new T-SQL script in SQL Agent jobs to accomplish the deletion of bak and trn files older that three days, as my sever does not have free disk left.

  Thanks again.

  Regards

  Niyala.

• Vishal Singh

  SSCertifiable

  Points: 5830

  More actions

  September 30, 2009 at 12:35 am

  #1059807

  Niyala (9/30/2009)

  ---

  Hi,

  Thanks indeed for your information. As to how I am currently doing my backups; I am using T-SQL SQL Agent jobs that run once every night to take a full back up and every 60 minutes transaction log back up. I changed the switch "Retain" to 5 days, for example. However, it is not doing what it should be doing. Therefore, I wanted to implement a new T-SQL script in SQL Agent jobs to accomplish the deletion of bak and trn files older that three days, as my sever does not have free disk left.

  Thanks again.

  Regards

  Niyala.

  To what I understood is after adding the cleanup script its working now for you?

  -Forum Etiquette: How to post Performance Problems[/url]

  -Forum Etiquette: How to post data/code to get the best help [/url]

• MANU-J.

  SSC-Dedicated

  Points: 31126

  More actions

  September 30, 2009 at 8:06 am

  #1059966

  Did you try my script?

  MJ