1. 什么是定时作业?
定时作业是指在数据库中设置一些任务,这些任务可以在指定的时间自动执行,无需人为干预。数据库管理员可以通过编写SQL语句,将其保存成作业,然后设置作业执行的时间、频率和所使用的账号等信息,从而实现定时执行这些SQL语句的功能。开启定时作业可以有效地减轻管理员的工作负担,提高数据库管理效率。
2. 如何创建定时作业?
2.1 创建作业
在SQL Server Management Studio中,可以通过以下步骤创建作业:
在“对象资源管理器”中展开“SQL Server代理”节点,右键单击“作业”节点,选择“新建作业”。
在“新建作业”对话框中,输入作业名称和描述,选择作业的类别和所使用的账号。
切换到“步骤”选项卡,单击“新建”按钮。
在“新建作业步骤”对话框中,输入步骤名称和描述,并编写要执行的SQL语句。
如果要添加更多的步骤,可以单击“新建”按钮,重复以上步骤。
切换到“计划”选项卡,单击“新建”按钮。
在“新建作业计划”对话框中,输入计划名称和描述,选择计划类型、执行频率和执行时间等信息。
单击“确定”按钮,保存作业和计划信息。
需要注意的是,创建定时作业过程中需要提前设置SQL Server代理的启用状态,否则无法创建作业。可以通过“SQL Server代理”节点上下文菜单的“属性”选项卡中设置。如果SQL Server代理处于禁用状态,需要将其启用,否则作业无法执行。
2.2 启用定时作业
创建定时作业后,还需要将其启用才能让作业生效。启用作业可以通过以下步骤完成:
在“对象资源管理器”中展开“SQL Server代理”节点,展开“作业”节点。
右键单击要启用的作业,选择“启用”。
3. 定时作业的管理
3.1 停用定时作业
停用定时作业可以通过以下步骤完成:
在“对象资源管理器”中展开“SQL Server代理”节点,展开“作业”节点。
右键单击要停用的作业,选择“停用”。
3.2 修改定时作业
修改定时作业可以通过以下步骤完成:
在“对象资源管理器”中展开“SQL Server代理”节点,展开“作业”节点。
右键单击要修改的作业,选择“修改”。
在“作业属性”对话框中修改作业相关信息,例如作业名称、作业描述、作业类别、作业账号、作业步骤和计划等。
单击“确定”按钮,保存修改后的作业信息。
3.3 删除定时作业
删除定时作业可以通过以下步骤完成:
在“对象资源管理器”中展开“SQL Server代理”节点,展开“作业”节点。
右键单击要删除的作业,选择“删除”。
在“确认删除”对话框中确认删除操作。
4. 示例
下面是一个创建定时作业的示例:
-- 创建一个定时作业,每天晚上10点执行一次
USE msdb
GO
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'FIZZJOB' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'FIZZJOB'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'FIZZJOB',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'定时清理FIZZDB中的过期数据',
@category_name=N'FIZZJOB',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- 3. 添加作业步骤
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'清理过期数据',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_fail_action=2,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'delete from tbl_fizz where dateadd(day, -7, created_time) <= getdate()',
@database_name=N'FIZZDB',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- 4. 添加作业计划
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'每天晚上10点执行',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=1,
@active_start_date=20200101,
@active_end_date=99991231,
@active_start_time=220000,
@active_end_time=235959,
@schedule_uid=N'ec8c3272-e999-482d-bfe0-a56bed9364ce'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- 5. 启动作业
EXEC @ReturnCode = msdb.dbo.sp_start_job @job_id=@jobId
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
上面的示例代码将创建一个定时作业,名称为FIZZJOB,作业描述为“定时清理FIZZDB中的过期数据”,作业每天晚上10点执行一次,在作业步骤中将执行一个SQL语句,即删除FIZZDB中7天前的数据。