January 8, 2009 at 11:28 am
Hi guys,
For some reason we are taking Tlog backups of 3 databases in this pattern: dbname_yyyymmdd_hhmmss.trn. With the native maintainence plan these log backups are not getting cleaned up as the pattern is different. Is there any way or script that you guys can share to make this process automated. Workaround for this in this time we are doing it manually deleting the log backups so that we can prevent the drive space from growing. Your exp and views will be highly appreciated..
Thanks
January 8, 2009 at 2:44 pm
this is really a simple t sql programming task. read the directory into a table, extract the date from the filename, delete where date > 3 days old. Check out the parameter options for DIR.
The proc expects a table to hold the base location for backups and backups to be held in a subfolder of the same name as the database.
It won't work if your database is called something like mydb20081212
/****** Object: StoredProcedure [dbo].[dbasp_LiteSpeed_GroomFiles] Script Date: 11/17/2008 10:49:00 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dbasp_LiteSpeed_GroomFiles]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[dbasp_LiteSpeed_GroomFiles]
GO
/****** Object: StoredProcedure [dbo].[dbasp_LiteSpeed_GroomFiles] Script Date: 11/17/2008 10:48:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[dbasp_LiteSpeed_GroomFiles]
@DbNameVARCHAR(128),-- compulsory parameter
@GroomAfterDaysINT = null,
@GroomPathVARCHAR(250)=null
-- ============================================================================
-- Stored Procedure: dbasp_LiteSpeed_GroomFiles
-- Written by: colin leversuch-roberts
--http://www.kelemconsulting.co.uk
--(c)
--
-- Purpose: Delete SQL Server Backup Files , LSBAK LSTRN LSDFF
-- Use either passed parameters or those set in ServerParameters Table
--Defaults to 7 days
--
-- System:DBA maintenance
-- Output Parameters: None
-- Return Status: 0 for success -1 or valid sql error for failure
--
-- Usage:EXEC @return = dbasp_LiteSpeed_GroomFiles 'master'
--EXEC @return = dbasp_LiteSpeed_GroomFiles 'master',4
--
-- Called By:agent job
-- Calls:master..sysdatabases
--sysobjects
--master..xp_regread
--master.dbo.xp_cmdshell
--serverlocal..serverparameters
--
-- Notes:This proc creates a table ( has to do this due to computed columns)
--Also creates a temporary table - yes this causes recompiles but you can't exec
--into a table variable or embed a function
--
-- DependenciesThe function fn_FileDate which is used as a computed column
--
-- VERSION HISTORY
-- Version NoDateDescription
-- 127 April 2006Initial Release
-- 2 nov 2008sql 2005 version
-- ============================================================================
AS
SET NOCOUNT ON
IF not exists(select * from master.dbo.sysdatabases with (nolock) where name=@DbName)-- database exists ?
BEGIN
RAISERROR ('The database %s cannot be found on this server ',16,1, @DbName)with log
return(-1)
END
--
if exists (select * from sys.objects where [object_id] = object_id(N'[dbo].[TMP_DIR]') and OBJECTPROPERTY([object_id], N'IsUserTable') = 1)
drop table [dbo].[TMP_DIR]
--endif
--
CREATE TABLE dbo.TMP_DIR-- table to hold result sets note use of computed columns hence perm table.
(
NumKeyInt identity(1,1),
DirResultsVARCHAR(255) default getdate(),
BKNameas substring(DirResults,len(DirResults)-(charindex(' ',reverse(DirResults))-2),charindex(' ',reverse(DirResults))) ,
BKDateAS (dbo.fn_FileDate(DirResults))
)
Create Table #Count (c1 int)-- used to check for existence of files for archive
Declare @GroomCmdVARCHAR(500)-- SQL Command string for files to delete
Declare@DirDBNameVARCHAR(255)-- Directory for the database files without [ ] and spaces
Declare@CleanDBNameVARCHAR(255)-- Database name with no [ ] brackets
Declare @Localevarchar(30)-- holds the regional country for the server
Declare @count int-- loop counter
declare @backuppath varchar(1000)
Declare @FileExistCmd_GROOM varchar(125)-- SQL Command string to test for .BAK files to groom
Declare @files int
Declare @return int
--
SET @DirDBName = replace(replace(replace(@DbName,' ',''),'[',''),']','')-- remove [ ] and spaces from passed database name
SET @CleanDBName = replace(replace(@DbName,'[',''),']','')-- remove [ ] from database name but leave spaces
SET @DbName = quotename(@CleanDBName)-- put [ ] brackets around the database name
--
-- Select and SET parameters
-- if we don't pass them then grab them from ServerLocal..ServerParameters
-- if ServerParameters doesn't have a value or parameter then assign a default value
-- ( that should cover all possibilities !! )
--
-- The directory path commands for the database being called
-- Note that we will only ever delete .BAK, .DFF and .TRN files
-- any file with _keep in it's name will be ignored
--
IF @GroomPath is null
SELECT
@backuppath = ParmValue+'\' + @DirDBName
FROM ServerLocal.dbo.ServerParameters WHERE ParmName='BackupPath'
ELSE
BEGIN
IF right(@GroomPath,1)='\'
set @GroomPath=left(@GroomPath,len(@GroomPath)-1)
--endif
SET @backuppath = @GroomPath+'\' + @DirDBName
END
--endif
--
-- The groom parameter .. how many days before we delete the files
--
IF (( SELECT Count(*) FROM ServerLocal.dbo.ServerParameters WHERE ParmName='BackupGrooming')>0)and(@GroomAfterDays is null)
SELECT @GroomAfterDays = CONVERT(INT,ParmValue) FROM ServerLocal.dbo.ServerParameters WHERE ParmName='BackupGrooming'
ELSE
SET @GroomAfterDays = isnull(@GroomAfterDays,7)-- leave a weeks backups if we don't specify a value
--endif
--
-- we need to know which regional SETtings are on the server
-- use the registry key ( w2k ) HKEY_USERS\.Default\Control Panel\International\sCountry
--
Exec @return = master..xp_regread 'HKEY_USERS','.Default\Control Panel\International','sCountry',@Locale output
IF @Locale='United Kingdom'
SET Dateformat dmy
ELSE
SET Dateformat mdy
--endif
--
-- check for files - only action if there are files.
-- have to test for files in existence to avoid throwing the date calc in the table
--
-- SQL Command string to test for .bak files to groom
--
SET @FileExistCmd_GROOM='DIR '+@BackupPath + '\ |find /I /C ".LSBAK"'
Insert Into #Count
Exec @return = master.dbo.xp_cmdshell @FileExistCmd_GROOM
--
SELECT top 1 @files = c1 from #Count order by c1 desc
IF @files>0
BEGIN
SELECT
@GroomCmd = 'DIR '+ParmValue + '\' + @DirDBName + '\*.LSBAK /A-D/-C/O-D |find /I /V "_keep"|find "/"',
@backuppath = ParmValue+'\' + @DirDBName
FROM ServerLocal.dbo.ServerParameters WHERE ParmName='BackupPath'
INSERT INTO TMP_DIR(DirResults)
EXEC @return = master.dbo.xp_cmdshell @GroomCmd
END
--endif
delete from #Count
-- SQL Command string to test for .dff files to groom
SET @FileExistCmd_GROOM='DIR '+@BackupPath + '\ |find /I /C ".LSDFF"'
Insert Into #Count
Exec @return = master.dbo.xp_cmdshell @FileExistCmd_GROOM
--
SELECT top 1 @files = c1 from #Count order by c1 desc
IF @files>0
BEGIN
SELECT
@GroomCmd = 'DIR '+ParmValue + '\' + @DirDBName + '\*.LSDFF /A-D/-C/O-D |find /I /V "_keep"|find "/"',
@backuppath = ParmValue+'\' + @DirDBName
FROM ServerLocal.dbo.ServerParameters WHERE ParmName='BackupPath'
INSERT INTO TMP_DIR(DirResults)
EXEC @return = master.dbo.xp_cmdshell @GroomCmd
END
--endif
delete from #Count
-- SQL Command string to test for .trn files to groom
SET @FileExistCmd_GROOM='DIR '+@BackupPath + '\ |find /I /C ".LSTRN"'
Insert Into #Count
Exec @return = master.dbo.xp_cmdshell @FileExistCmd_GROOM
--
SELECT top 1 @files = c1 from #Count order by c1 desc
IF @files>0
BEGIN
SELECT
@GroomCmd = 'DIR '+ParmValue + '\' + @DirDBName + '\*.LSTRN /A-D/-C/O-D |find /I /V "_keep"|find "/"',
@backuppath = ParmValue+'\' + @DirDBName
FROM ServerLocal.dbo.ServerParameters WHERE ParmName='BackupPath'
INSERT INTO TMP_DIR(DirResults)
EXEC @return = master.dbo.xp_cmdshell @GroomCmd
END
--endif
IF (select count(*) from dbo.TMP_DIR )>0
BEGIN
--
-- generate and execute the delete statements all in one go
-- assign and test for null as each row in the table has to at least test for condition
-- loop around setting @GroomCmd to null .. this avoids having to delete unwanted rows from TMP_DIR
--
SET @count=1
while @count<=(select max(NumKey) from TMP_DIR)
BEGIN
SET @GroomCmd=null
select @GroomCmd = 'DEL "' + @BackupPath + '\' + right(DirResults,len(DirResults)-(patindex('%'+@CleanDBName+'%',DirResults)-1)) + '" /f /q'
from dbo.tmp_dir
where NumKey = @count and DirResults is not null and DATEDIFF(DD, BKDate, GETDATE()) >= @GroomAfterDays
IF @GroomCmd is not null
BEGIN
PRINT @GroomCmd
EXEC @return = master.dbo.xp_cmdshell @GroomCmd, NO_OUTPUT
END
--endif
SET @count=@count+1END
--endwhile
END
--endif
--endif
Drop Table #Count
Drop Table dbo.tmp_dir
return(@return)
-- end proc
GO
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 8, 2009 at 2:50 pm
you'll need this function too, sorry forgot
CREATE function dbo.fn_FileDate (@p1 varchar(500))
-- =============================================================
-- Function: fn_FileDate
-- Written by: (c)Colin Leversuch-Roberts
--
-- Purpose: Extract/Create the backup date for a file . used in dbasp_GroomFiles
--computed column in a table
--
-- System:DBA maintenance
--
-- Input Paramters: @p1 varchar
--
-- Returns : smalldatetime
--
-- Usage: Computed column in table { select dbo.fn_FileDate(@p1) }
--
-- Notes:simplifies the code required to extract the backup time from the DIR result set within a select table
--Takes regional information from Server to ensure correct date format
--
-- VERSION HISTORY
-- Version NoDateDescription
-- 122 December-2003Initial Release
-- 2 21 January 2004Test to check that the returned string @FileTime can be resolved to a date
--If not then the file date is taken
-- =============================================================
returns datetime
as
begin
declare @FileTime char(8),@ReturnTime datetime,@Locale varchar(35),@p2 char(3)
exec master..xp_regread 'HKEY_USERS','.Default\Control Panel\International','sCountry',@Locale output
IF @Locale='United Kingdom'
Set @p2='dmy'
ELSE
Set @p2='mdy'
--endif
IF(select patindex('%[_]200_____%',@p1))>0
BEGIN
set @FileTime=substring(@p1,patindex('%[_]200_____%',@p1)+1,8) --yyyymmdd
IF isdate(@FileTime)=1-- modification for v2
BEGIN
set @ReturnTime=convert(datetime,@FileTime)
END
else
set @ReturnTime=convert(datetime,left(@p1,10)) -- native format xx/xx/xxxx
--endif
END
else
set @ReturnTime=convert(datetime,left(@p1,10)) -- native format xx/xx/xxxx
--endif
return(@ReturnTime)
end
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 8, 2009 at 3:20 pm
Thanks Colin for the reply to this thread. I was looking at the code and try to execute the proc but did not work. I am only concerned with the trn files and commented the other bak and diff.. Can you please make it simple ? Just requesting...
January 8, 2009 at 3:28 pm
Colin when I executed the proc it did not understood Serverlocal.dbo.serverparameters
IF @GroomPath is null
SELECT
@backuppath = ParmValue+'\' + @DirDBName
FROM ServerLocal.dbo.ServerParameters WHERE ParmName='BackupPath'
ELSE
BEGIN
IF right(@GroomPath,1)='\'
set @GroomPath=left(@GroomPath,len(@GroomPath)-1)
--endif
SET @backuppath = @GroomPath+'\' + @DirDBName
END
--endif
--
-- The groom parameter .. how many days before we delete the files
--
IF (( SELECT Count(*) FROM ServerLocal.dbo.ServerParameters WHERE ParmName='BackupGrooming')>0)and(@GroomAfterDays is null)
SELECT @GroomAfterDays = CONVERT(INT,ParmValue) FROM ServerLocal.dbo.ServerParameters WHERE ParmName='BackupGrooming'
ELSE
SET @GroomAfterDays = isnull(@GroomAfterDays,7) -- leave a weeks backups if we don't specify a value
January 9, 2009 at 11:24 am
Hi Colin,
Can you please tell me what this " ServerLocal.dbo.ServerParameters WHERE ParmName='BackupPath' " is basically is ? I simply executed the procedure and function and try to run the proc providing the parameters but did not work. Can you please provide me any workaround or anything I need to change either in the procedure or function or anything..
Thanks
January 9, 2009 at 12:46 pm
I did say the proc expected the location of the backup folder path to be in a table. I'd assume that you would be able to substitute the table lookup for the location. Procs I use take benefit of a local config table - that way the code becomes portable and configurable. Sorry I can't write code for you specifically - this is what I do for a living so you'd have to hire me for specifics.
Don't want to sound harsh but if you're used to T SQL it shouldn't be hard to figure it out, if you can't then maybe you shouldn't be attempting to this?
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
January 14, 2009 at 2:51 pm
Not a problem Colin.. I do the same. I actually did it and it was very simple. Just used cursor to loop through a table where the filepath is stored when the log backups takes place. Then I just deleted those backups whose date getdate()-3..
Actually worked. Thanks for your response though.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply