I am working on sql query where i want to Delete data older than 30 days. For this i used following query in master database.
USE Master
GO
IF EXISTS( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[MyBackgroundTask]')
AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MyBackgroundTask]
GO
CREATE PROCEDURE MyBackgroundTask
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- The interval between cleanup attempts
declare @timeToRun nvarchar(50)
set @timeToRun = '16:25:33'
while 1 = 1
begin
waitfor time @timeToRun
begin
execute [Tags].[dbo].[SP_DeleteOldData];
end
end
END
GO
-- Run the procedure when the master database starts.
sp_procoption @ProcName = 'MyBackgroundTask',
@OptionName = 'startup',
@OptionValue = 'on'
GO
And the stored procedure which will be accessed by SP_Procoption to delete the old data is
USE [Tags]
GO
/****** Object: StoredProcedure [dbo].[SP_DeleteOldData] Script Date:
02/12/2018 16:05:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[SP_DeleteOldData] --SP_DeleteOldData
AS
Delete from tblTag where DATEDIFF(DD,Date,getdate())>30
Here in the above stored procedure "SP_DeleteOldData" will data older by 30 days. And this Stored will automatically delete old data everyday at time "16:25:33'" using sp_procoption. But in this case the the data older than 30 days is not getting deleted. How can i solve this?
Actually I want to delete older data automatically everyday at specified datetime without using sql server agent . For that i went through many references. And the code i posted here was the reference i used. So my query was that is that possible to delete older data automatically everyday at specified datetime without using sql server agent?