定时调度让MSSQL安全运行-猿码集

1. MSSQL的定时调度

MSSQL是一款强大的关系型数据库管理系统，在业务运作中扮演着重要的角色。在使用MSSQL的过程中，保证其安全运行是至关重要的。MSSQL提供了很多实用的功能来防止数据泄漏、恢复数据等，定时调度就是其中之一。

1.1 什么是定时调度？

定时调度是MSSQL中一个非常重要的功能，通过该功能，可以定期、自动地执行一批即时查询、作业等相关任务。它可以自动从数据库中获取信息并执行定义好的任务，从而提高任务的准确性和效率。定时调度能够帮助DBA自动执行诸如备份、计划清理等任务，使得数据的安全性和可靠性得到了保障。

1.2 定时调度的作用

定时调度广泛应用于大型数据库中，其作用主要有以下几点：

定期备份数据库：DBA可以利用定时调度功能，每日自动备份数据库，并将备份文件存储在特定的位置，以此来保证数据库的安全性。

定期清理数据库：当数据库过于庞大时，定期清理成为一项必要的工作。定时调度功能可以帮助DBA自动进行周期性的数据库清理，以有效地管理数据库。

定期执行脚本作业：有一些任务需要在数据库上进行，DBA可以使用定时调度功能来自动执行相关脚本作业。

2. MSSQL定时调度的实现

2.1 创建定时调度

在MSSQL中，创建定时调度需要使用到SQL Server代理服务。由于该服务有很大的权限，因此需要保证该服务的安全性。

下面是一个使用T-SQL创建定时调度的示例：


USE msdb;
GO
EXEC dbo.sp_add_job
    @job_name = N'Sample Job',
    @enabled = 1,
    @description = N'This is a sample job.';
GO
EXEC sp_add_jobstep
    @job_name = N'Sample Job',
    @step_name = N'Step 1',
    @subsystem = N'TSQL',
    @command = N'SELECT * FROM sys.databases;',
    @retry_attempts = 5,
    @retry_interval = 5;
GO
EXEC sp_add_schedule
    @schedule_name = N'Sample Schedule',
    @freq_type = 4, -- Monthly
    @freq_interval = 1,
    @active_start_time = 220000;
GO

上述代码中，我们创建了一个名称为 “Sample Job” 的任务，该任务通过T-SQL来执行。我们还为这个任务设置了一个信息为“ This is a sample job”的描述。

接着，我们为这个作业设置了一个名为“ Step 1” 的步骤，“ Step 1” 步骤会执行一条查询语句。它将运行“ SELECT * FROM sys.databases;”语句。

最后，我们创建了一个名为“ Sample Schedule”的计划。该计划是一个月执行一次计划，具体来说是每月执行一次，并且在我们指定的活动开始时间（22:00:00）启动。

2.2 修改定时调度

如果需要修改定时调度，可以使用T-SQL语句进行修改。下面是一个修改定时调度的示例：


-- 修改作业
USE msdb;
GO
EXEC dbo.sp_update_job
    @job_name = N'Sample Job',
    @description = N'This is an updated sample job.',
    @enabled = 0;
GO
-- 修改作业步骤
EXEC sp_update_jobstep
    @job_name = N'Sample Job',
    @step_name = N'Step 1',
    @on_success_action = 2,
    @on_fail_action = 3;
GO
-- 修改计划
EXEC sp_update_schedule
    @schedule_name = N'Sample Schedule',
    @freq_type = 2, -- Weekly
    @freq_interval = 2,
    @active_start_time = 210000;
GO

在上述代码中，我们通过使用sp_update_job、sp_update_jobstep和sp_update_schedule存储过程来更新已经存在的作业、作业步骤和计划。这里需要注意，修改定时调度时需要谨慎，以免造成不必要的数据损失。

3. MSSQL定时调度的注意事项

3.1 避免重复执行

在创建或修改定时调度时，需要确保它们不会重复地运行。如果重复运行可能会破坏数据库的完整性，导致数据丢失。

要避免重复执行，您可以使用sp_add_jobschedule存储过程在创建计划时设置 @schedule_uid 选项。


EXEC sp_add_jobschedule
    @job_name = N'Sample Job',
    @name = 'Sample Job Schedule',
    @freq_type = 4, -- Monthly
    @freq_interval = 1,
    @active_start_time = 220000,
    @schedule_uid = 'FD8C125B-17F1-46A7-9A03-27942ED811D3';

上述代码中，我们指定了一个唯一的@schedule_uid，从而避免了定时调度的重复执行。

3.2 监测系统资源消耗

定时调度会占用系统资源，例如CPU、内存和磁盘空间等。为了保证数据库能够顺畅地运行，我们需要监测定时调度的资源消耗情况，并及时调整相关设置。

您可以使用系统监视器或查询DMV视图来查看资源消耗。下面是一个查询sys.dm_exec_query_stats视图的示例：


SELECT 
    qs.execution_count,
    qs.total_worker_time/1000000 AS total_cpu_time_sec,
    qs.total_elapsed_time/1000000 AS total_elapsed_time_sec,
    qs.total_logical_reads + qs.total_logical_writes AS total_logical_io,
    DB_NAME(qt.dbid) AS db_name,
    qt.objectid AS objectid,
    QUOTENAME(OBJECT_SCHEMA_NAME(qt.objectid, qt.dbid)) + '.' + QUOTENAME(OBJECT_NAME(qt.objectid, qt.dbid)) AS offending_object,
        
    SUBSTRING(qt.text, qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS offending_text,
        
    (qs.total_worker_time/qs.execution_count)/1000000 AS avg_cpu_time_sec,
    (qs.total_elapsed_time/qs.execution_count)/1000000 AS avg_elapsed_time_sec,
    (qs.total_logical_reads + qs.total_logical_writes)/qs.execution_count AS avg_logical_io
FROM 
    sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
ORDER BY 
    total_cpu_time_sec DESC;

3.3 监测定时调度状态

在创建或修改定时调度后，建议监测其状态。您可以使用下列 T-SQL 查询语句在 SQL Server Management Studio (SSMS) 中检查 SQL Server 代理作业的状态：


SELECT * FROM msdb.dbo.sysjobs

注意：该语句将返回 SQL Server 代理服务中所有作业的完整列表和状态。可以使用这些作业的名称查询作业的状态信息。

结论

在本文中，我们详细介绍了MSSQL定时调度的定义，作用以及实现过程。我们还讨论了如何避免重复运行、监测系统资源和监测定时调度状态的问题。定时调度是MSSQL中非常重要的一个特性，它对于数据的安全性和可靠性有着很大的帮助。DBA可以通过使用定时调度来执行周期性的、重复性的或其他预定的任务，具有高效、准确的特性。

定时调度让MSSQL安全运行