如何通过T-SQL在SQL Server 2008中创建计划作业? [英] How to create a scheduled job in SQL server 2008 via T-SQL?

问题描述

我想创建一个在一段时间后从数据库中删除记录的作业.例如，我在新闻表Time Stamp中有一个字段，并且每个月对我的数据库执行一次SQL查询，就像排定的作业一样，并删除时间戳为两个月大的新闻.通常，我想删除2个月或更早的新闻，以免我的桌子变成大桌子.我该怎么做?

I want to create a job which deletes records from a database after a period of time has passed. For example I have a field in news table Time Stamp and each month a SQL query runs like a scheduled job against my database and deletes news where the time stamp is two month old. Generally I want to delete news for 2 month ago and older to not let my table become a large table. How can I accomplish this?

推荐答案

您应该创建工作 a>在SQL中 下面是通过SQL代理创建作业的示例T-SQL

you should create a job in SQL below is a sample T-SQL for create a job via SQL agent

USE msdb ;
GO
EXEC dbo.sp_add_job
    @job_name = N'Weekly Sales Data Backup' ;
GO
EXEC sp_add_jobstep
    @job_name = N'Weekly Sales Data Backup',
    @step_name = N'Set database to read only',
    @subsystem = N'TSQL',
    @command = N'ALTER DATABASE SALES SET READ_ONLY', 
    @retry_attempts = 5,
    @retry_interval = 5 ;
GO
EXEC dbo.sp_add_schedule
    @schedule_name = N'RunOnce',
    @freq_type = 1,
    @active_start_time = 233000 ;
USE msdb ;
GO
EXEC sp_attach_schedule
   @job_name = N'Weekly Sales Data Backup',
   @schedule_name = N'RunOnce';
GO
EXEC dbo.sp_add_jobserver
    @job_name = N'Weekly Sales Data Backup';
GO

这篇关于如何通过T-SQL在SQL Server 2008中创建计划作业?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！