Delete record from table every day at specific time using SQL Server Job Scheduler

sksandeep
 
on Feb 12, 2018 05:04 AM
5926 Views

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?

Download FREE API for Word, Excel and PDF in ASP.Net: Download
nagaraju60
 
on Feb 12, 2018 05:14 AM
Mudassar
 
on Feb 13, 2018 04:42 AM

Not possible.