003_作业_磁盘_参考
003_作业_磁盘_参考
----------------------------------------------------
--创建相关表
IF OBJECT_ID('dbo.AvailableDiskSpace', 'U') IS NOT NULL
DROP TABLE dbo.AvailableDiskSpace
GO
CREATE TABLE dbo.AvailableDiskSpace
(
Id INT IDENTITY (1, 1) NOT NULL,
LastUpdate SMALLDATETIME NOT NULL,
DriveLetter CHAR(1) NOT NULL,
FreeMB INT NOT NULL,
) ON NutsAndBolts_Data
GO
ALTER TABLE dbo.AvailableDiskSpace ADD CONSTRAINT DF_AvailableDiskSpace_LastUpdate DEFAULT GETDATE() FOR LastUpdate
GO
ALTER TABLE dbo.AvailableDiskSpace ADD CONSTRAINT PK_AvailableDiskSpace PRIMARY KEY
(
Id
) ON NutsAndBolts_Data
GO
--创建procedure getAvailableDiskSpace
CREATE PROCEDURE dbo.getAvailableDiskSpace
AS
SET NOCOUNT ON
DECLARE @l_DiskFreeSpace INT,
@l_FreeSpaceThreshhold INT,
@l_DriveLetter CHAR(1),
@l_AlertMessage VARCHAR(200),
@l_MailSubject VARCHAR(100),
@l_Recipients VARCHAR(100)
/* If free space is less than this (in MB), alert somebody */
SET @l_FreeSpaceThreshhold = 1024
SET @l_AlertMessage = NULL
SET @l_Recipients = 'Africa.Zhou@morningstar.com'
/* Create a temp table to hold disk space information */
CREATE TABLE #disk_free_space
(
DriveLetter CHAR(1) NOT NULL,
FreeMB INT NOT NULL
)
INSERT INTO #disk_free_space
EXEC master.dbo.xp_fixeddrives
/* Save results for trend analysis */
INSERT INTO dbo.AvailableDiskSpace (DriveLetter, FreeMB)
SELECT DriveLetter, FreeMB
FROM #disk_free_space
/* Examine free space of each drive and send email for those that are with low */
DECLARE cur_DriveSpace CURSOR FAST_FORWARD FOR
SELECT DriveLetter, FreeMB
FROM #disk_free_space
OPEN cur_DriveSpace
FETCH NEXT FROM cur_DriveSpace INTO @l_DriveLetter, @l_DiskFreeSpace
/* Examine free space of each drive */
/* Build text of email for each drive that is low */
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF @l_DiskFreeSpace < @l_FreeSpaceThreshhold
BEGIN
IF @l_AlertMessage IS NULL
BEGIN
SET @l_AlertMessage = @l_DriveLetter + ' has ' + CAST(@l_DiskFreeSpace AS VARCHAR) + ' MB free.'
END
ELSE
BEGIN
SET @l_AlertMessage = @l_AlertMessage + CHAR(13) + @l_DriveLetter + ' has ' + CAST(@l_DiskFreeSpace AS VARCHAR) + ' MB free.'
END
END
FETCH NEXT FROM cur_DriveSpace INTO @l_DriveLetter, @l_DiskFreeSpace
END
CLOSE cur_DriveSpace
DEALLOCATE cur_DriveSpace
DROP TABLE #disk_free_space
-- Send out email
IF @l_AlertMessage IS NOT NULL
BEGIN
SET @l_MailSubject = 'Free disk space is low on ' + @@SERVERNAME
EXECUTE msdb.dbo.sp_send_dbmail @recipients = @l_Recipients,
@subject = @l_MailSubject,
@body = @l_AlertMessage
END
GO
--新增作业
--1.执行 sp_add_job 来创建作业。-------------------------------------------
--
USE msdb
GO
BEGIN TRANSACTION
DECLARE @l_JobId BINARY(16),
@l_Err INT
SELECT @l_Err = 0
IF NOT EXISTS ( SELECT 1
FROM msdb.dbo.syscategories
WHERE name = N'[Uncategorized (Local)]' )
EXECUTE msdb.dbo.sp_add_category @name = N'[Uncategorized (Local)]'
-- Delete the job with the same name (if it exists)
SELECT @l_JobId = job_id
FROM msdb.dbo.sysjobs
WHERE (name = N'Track Free disk space')
IF (@l_JobId IS NOT NULL)
BEGIN
-- Check if the job is a multi-server job
IF ( EXISTS ( SELECT 1
FROM msdb.dbo.sysjobservers
WHERE (job_id = @l_JobId) AND (server_id <> 0) ))
BEGIN
-- There is, so abort the script
RAISERROR (N'Unable to import job ''Track Free disk space'' since there is already a multi-server job with this name.', 16, 1)
GOTO QuitWithRollback
END
ELSE
BEGIN
-- Delete the [local] job
EXECUTE msdb.dbo.sp_delete_job @job_name = N'Track Free disk space'
END
SELECT @l_JobId = NULL
END
BEGIN
-- Add the job
EXECUTE @l_Err = msdb.dbo.sp_add_job @job_id = @l_JobId OUTPUT,
@job_name = N'Track Free disk space',
@owner_login_name = N'sa',
@description = N'No description available.',
@category_name = N'[Uncategorized (Local)]',
@enabled = 1,
@notify_level_email = 0,
@notify_level_page = 0,
@notify_level_netsend = 0,
@notify_level_eventlog = 2,
@delete_level = 0
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
--2.执行 sp_add_jobstep 来创建一个或多个作业步骤。-------------------------------------------
-- Add the job steps
EXECUTE @l_Err = msdb.dbo.sp_add_jobstep @job_id = @l_JobId,
@step_id = 1,
@step_name = N'Step 1',
@command = N'EXECUTE dbo.getAvailableDiskSpace',
@database_name = N'NutsAndBolts',
@server = N'',
@database_user_name = N'',
@subsystem = N'TSQL',
@cmdexec_success_code = 0,
@flags = 0,
@retry_attempts = 0,
@retry_interval = 1,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 1,
@on_fail_step_id = 0,
@on_fail_action = 2
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
EXECUTE @l_Err = msdb.dbo.sp_update_job @job_id = @l_JobId,
@start_step_id = 1
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
--3.执行 sp_add_schedule 来创建计划。-------------------------------------------
-- Add the job schedules
EXECUTE @l_Err = msdb.dbo.sp_add_jobschedule @job_id = @l_JobId,
@name = N'Schedule 1',
@enabled = 1,
@freq_type = 4,
@active_start_date = 20041119,
@active_start_time = 50000,
@freq_interval = 1,
@freq_subday_type = 1,
@freq_subday_interval = 0,
@freq_relative_interval = 0,
@freq_recurrence_factor = 0,
@active_end_date = 99991231,
@active_end_time = 235959
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
--4.执行 sp_attach_schedule 将计划附加到作业。
--5.执行 sp_add_jobserver 来设置作业的服务器。-------------------------------------------
-- Add the Target Servers
EXECUTE @l_Err = msdb.dbo.sp_add_jobserver @job_id = @l_JobId,
@server_name = N'(local)'
IF (@@ERROR <> 0 OR @l_Err <> 0)
GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
--启动作业
exec msdb.dbo.sp_start_job @job_name='JOB_CYCLE_ERRORLOG'
--停止作业
exec msdb.dbo.sp_stop_job @job_name='JOB_CYCLE_ERRORLOG'
-- 启用或禁用作业
EXEC msdb.dbo.sp_update_job
@job_name = N'JOB_CYCLE_ERRORLOG',
@enabled = 0 ; --0 禁用作业、 1启用作业
--删除作业
EXEC msdb.dbo.sp_delete_job @job_name = 'JOB_CYCLE_ERRORLOG';
--查询所有作业的相关信息
select b.job_id,b.name,a.step_id,a.step_name,b.description,a.command,a.database_name,c.next_run_date,c.next_run_time,a.last_run_date,a.last_run_time
from msdb..sysjobsteps a,msdb..sysjobs b,msdb..sysjobschedules c
where a.job_id=b.job_id and a.job_id=c.job_id
order by c.next_run_date,c.next_run_time,b.job_id,a.step_id
--查看当前会话的作业活动
use msdb
exec sp_help_jobactivity
--将当前作业活动插入到临时表
select * into #test_liwz from openrowset('SQLOLEDB','SERVER=172.16.0.214;uid=fpf;pwd=214fzgj;database=chisdb_bjrm_mz','exec msdb..sp_help_jobactivity') a
