I filled a file the other day and got a lot of database errors for one of the tables.
It turned out that my purging process had stopped running (and the notifications weren’t set up).
So I revisited that process, and made two stored procedures to facilitate the process.
Here is how to call the outer procedure:
Exec PurgeOldLogs 30 , 1
30 – number of days
1 – true – actually perform the delete. 0 means just do a select and return the counts that would be deleted.
The outer stored procedure:
-- =============================================
-- input a positive number of days
-- =============================================
CREATE PROCEDURE [dbo].[PurgeOldLogs]
@purgedays int = 60,
@runit bit = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
exec PurgeASingleLog @purgedays , 'Debugging' , 'dwhen' , @runit
exec PurgeASingleLog @purgedays , 'StatusLog' , 'logdate ' , @runit
exec PurgeASingleLog @purgedays , 'Events' , 'eventtime ' , @runit
END
The inner stored procedure. It generates some dynamic sql and executes it.
GO
/****** Object: StoredProcedure [dbo].[PurgeOldLogs] Script Date: 11/21/2008 07:53:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- input a positive number of days
-- =============================================
ALTER PROCEDURE [dbo].[PurgeASingleLog]
@purgedays int = 60,
@tablename varchar(50),
@fieldname varchar(50),
@runit bit = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
set @purgedays = -1 * @purgedays
declare @cmdcount nvarchar(500)
declare @cmddelete nvarchar(500)
set @cmdcount = 'SELECT count(*) as NumRecords , ' + char(39) + @tablename + char(39) + ' as NameTable '
set @cmdcount = @cmdcount + ' FROM ' + @tablename
set @cmdcount = @cmdcount + ' WHERE (' + @fieldname
set @cmdcount = @cmdcount + ' < DATEADD(d, ' + convert(varchar(12),@purgedays) + ' , GETDATE() ) )'
print @cmdcount
EXECUTE sp_executesql @cmdcount
if @runit = 1
begin
set @cmddelete = 'DELETE FROM ' + @tablename
set @cmddelete = @cmddelete + ' WHERE (' + @fieldname
set @cmddelete = @cmddelete + ' < DATEADD(d, ' + convert(varchar(12),@purgedays) + ' , GETDATE() ) )'
print @cmddelete
EXECUTE sp_executesql @cmddelete
end
END
