USE [msdb]
GO
/****** Object: Job [SQL Check] Script Date: 11/01/2011 14:26:43 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory Monitoring Script Date: 11/01/2011 14:26:43 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Monitoring' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Monitoring'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'SQL Check',
@enabled=1,
@notify_level_eventlog=2,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'This job perform some high level checks and sends out alerts based on results',
@category_name=N'Monitoring',
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Check SQL Error Log] Script Date: 11/01/2011 14:26:43 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check SQL Error Log',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'declare @Time_Start datetime;
declare @Time_End datetime;
set @Time_Start=getdate()-2;
set @Time_End=getdate();
create table #ErrorLog (logdate datetime
, processinfo varchar(255)
, Message varchar(500) )
insert #ErrorLog (logdate, processinfo, Message)
EXEC master.dbo.xp_readerrorlog 0, 1, null, null , @Time_Start, @Time_End, N''desc'';
create table SQL_Log_Errors (
[logdate] datetime,
[Message] varchar (500) )
insert into SQL_Log_Errors
select LogDate, Message FROM #ErrorLog
where (Message LIKE ''%error%'' OR Message LIKE ''%failed%'')
and processinfo NOT LIKE ''logon''
order by logdate desc
drop table #ErrorLog
declare @cnt int
select @cnt=COUNT(1) from SQL_Log_Errors
if (@cnt > 0)
begin
declare @strsubject varchar(100)
select @strsubject=''There are errors in the SQL Error Log on '' + @@SERVERNAME
declare @tableHTML nvarchar(max);
set @tableHTML =
N''
SQL Error Log Errors - '' + @@SERVERNAME + ''
'' +
N'''' +
N''Log Date | '' +
N''Message |
'' +
CAST ( ( SELECT td = [logdate], '''',
td = [Message]
FROM SQL_Log_Errors
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''
'' ;
EXEC msdb.dbo.sp_send_dbmail
@from_address=''test@test.com'',
@recipients=''test@test.com'',
@subject = @strsubject,
@body = @tableHTML,
@body_format = ''HTML'' ,
@profile_name=''test profile''
end
drop table SQL_Log_Errors',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Check Failed Jobs] Script Date: 11/01/2011 14:26:43 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check Failed Jobs',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'create table Failed_Jobs (
[Status] [varchar](6) NOT NULL,
[Job Name] [varchar](100) NULL,
[Step ID] [varchar](5) NULL,
[Step Name] [varchar](30) NULL,
[Start Date Time] [varchar](30) NULL,
[Message] [nvarchar](4000) NULL)
insert into Failed_Jobs
select ''FAILED'' as Status, cast(sj.name as varchar(100)) as "Job Name",
cast(sjs.step_id as varchar(5)) as "Step ID",
cast(sjs.step_name as varchar(30)) as "Step Name",
cast(REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,sjh.run_date)),102),''.'',''-'')+'' ''+SUBSTRING(RIGHT(''000000''+CONVERT(varchar,sjh.run_time),6),1,2)+'':''+SUBSTRING(RIGHT(''000000''+CONVERT(varchar,sjh.run_time),6),3,2)+'':''+SUBSTRING(RIGHT(''000000''+CONVERT(varchar,sjh.run_time),6),5,2) as varchar(30)) ''Start Date Time'',
sjh.message as "Message"
from sysjobs sj
join sysjobsteps sjs
on sj.job_id = sjs.job_id
join sysjobhistory sjh
on sj.job_id = sjh.job_id and sjs.step_id = sjh.step_id
where sjh.run_status <> 1
and cast(sjh.run_date as float)*1000000+sjh.run_time >
cast(convert(varchar(8), getdate()-1, 112) as float)*1000000+70000 --yesterday at 7am
union
select ''FAILED'',cast(sj.name as varchar(100)) as "Job Name",
''MAIN'' as "Step ID",
''MAIN'' as "Step Name",
cast(REPLACE(CONVERT(varchar,convert(datetime,convert(varchar,sjh.run_date)),102),''.'',''-'')+'' ''+SUBSTRING(RIGHT(''000000''+CONVERT(varchar,sjh.run_time),6),1,2)+'':''+SUBSTRING(RIGHT(''000000''+CONVERT(varchar,sjh.run_time),6),3,2)+'':''+SUBSTRING(RIGHT(''000000''+CONVERT(varchar,sjh.run_time),6),5,2) as varchar(30)) ''Start Date Time'',
sjh.message as "Message"
from sysjobs sj
join sysjobhistory sjh
on sj.job_id = sjh.job_id
where sjh.run_status <> 1 and sjh.step_id=0
and cast(sjh.run_date as float)*1000000+sjh.run_time >
cast(convert(varchar(8), getdate()-1, 112) as float)*1000000+70000 --yesterday at
declare @cnt int
select @cnt=COUNT(1) from Failed_Jobs
if (@cnt > 0)
begin
declare @strsubject varchar(100)
select @strsubject=''Check the following failed jobs on '' + @@SERVERNAME
declare @tableHTML nvarchar(max);
set @tableHTML =
N''Failed Jobs Listing - '' + @@SERVERNAME +''
'' +
N'''' +
N''Status | Job Name | '' +
N''Step ID | Step Name | Start Date | '' +
N''Message |
'' +
CAST ( ( SELECT td = [Status], '''',
td = [Job Name], '''',
td = [Step ID], '''',
td = [Step Name], '''',
td = [Start Date Time], '''',
td = [Message]
FROM Failed_Jobs
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''
'' ;
EXEC msdb.dbo.sp_send_dbmail
@from_address=''test@test.com'',
@recipients=''test@test.com'',
@subject = @strsubject,
@body = @tableHTML,
@body_format = ''HTML'' ,
@profile_name=''test profile''
end
drop table Failed_Jobs',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Check Missing Backups] Script Date: 11/01/2011 14:26:43 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check Missing Backups',
@step_id=3,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'create table Missing_Backups (
[DB Name] [varchar](100) NOT NULL,
[Type] [varchar] (5) NOT NULL,
[Last Backup] [varchar](100) NULL)
insert into Missing_Backups
SELECT d.name AS "Database",''Full'' as "Type",
ISNULL(CONVERT(VARCHAR,b.backupdate,120),''NEVER'') AS "Last Full Backup"
FROM sys.databases d
LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupset
WHERE type LIKE ''D''
GROUP BY database_name,type) b on d.name=b.database_name
WHERE (backupdate IS NULL OR backupdate < getdate()-1)
AND d.name <> ''tempdb''
UNION
SELECT d.name AS "Database",''Trn'' as "Type",
ISNULL(CONVERT(VARCHAR,b.backupdate,120),''NEVER'') AS "Last Log Backup"
FROM sys.databases d
LEFT JOIN (SELECT database_name,type,MAX(backup_finish_date) backupdate FROM backupset
WHERE type LIKE ''L''
GROUP BY database_name,type) b on d.name=b.database_name
WHERE recovery_model = 1
AND (backupdate IS NULL OR backupdate < getdate()-1)
AND d.name <> ''tempdb''
declare @cnt int
select @cnt=COUNT(1) from Missing_Backups
if (@cnt > 0)
begin
declare @strsubject varchar(100)
select @strsubject=''Check for missing backups on '' + @@SERVERNAME
declare @tableHTML nvarchar(max);
set @tableHTML =
N''Databases Missing Backups Listing - '' + @@SERVERNAME +''
'' +
N'''' +
N''DB Name | Type | '' +
N''Last Backup |
'' +
CAST ( ( SELECT td = [DB Name], '''',
td = [Type], '''',
td = [Last Backup]
FROM Missing_Backups
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''
'' ;
EXEC msdb.dbo.sp_send_dbmail
@from_address=''test@test.com'',
@recipients=''test@test.com'',
@subject = @strsubject,
@body = @tableHTML,
@body_format = ''HTML'' ,
@profile_name=''test profile''
end
drop table Missing_Backups',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Check Disk Space] Script Date: 11/01/2011 14:26:43 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check Disk Space',
@step_id=4,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'create table #DriveSpaceLeft (Drive varchar(10),
[MB Free] bigint )
insert #DriveSpaceLeft (Drive, [MB Free])
EXEC master.dbo.xp_fixeddrives;
create table DrivesWithIssue (Drive varchar(10),
[MB Free] bigint )
insert into DrivesWithIssue
select Drive, [MB Free] from #DriveSpaceLeft
where [MB Free] < 1000
drop table #DriveSpaceLeft
declare @cnt int
select @cnt=COUNT(1) from DrivesWithIssue
if (@cnt > 0)
begin
declare @strsubject varchar(100)
select @strsubject=''Check drive space on '' + @@SERVERNAME
declare @tableHTML nvarchar(max);
set @tableHTML =
N''Drives with less that 1GB Free - '' + @@SERVERNAME + ''
'' +
N'''' +
N''Drive | '' +
N''MB Free |
'' +
CAST ( ( SELECT td = [Drive], '''',
td = [MB Free]
FROM DrivesWithIssue
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''
'' ;
EXEC msdb.dbo.sp_send_dbmail
@from_address=''test@test.com'',
@recipients=''test@test.com'',
@subject = @strsubject,
@body = @tableHTML,
@body_format = ''HTML'' ,
@profile_name=''test profile''
end
drop table DrivesWithIssue',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Check Server Memory] Script Date: 11/01/2011 14:26:43 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check Server Memory',
@step_id=5,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'create table MemoryInfo (
[Total Memory MB] bigint NOT NULL,
[Available Memory MB] bigint NOT NULL,
[% Memory Free] decimal(5,2) NOT NULL)
insert into MemoryInfo
SELECT total_physical_memory_kb/1024 as "Total Memory MB",
available_physical_memory_kb/1024 as "Available Memory MB",
available_physical_memory_kb/(total_physical_memory_kb*1.0)*100 AS "% Memory Free"
FROM sys.dm_os_sys_memory
declare @memfree float
select @memfree=[Available Memory MB] from MemoryInfo
if (@memfree < 1000)
begin
declare @strsubject varchar(100)
select @strsubject=''Check memory usage on '' + @@SERVERNAME
declare @tableHTML nvarchar(max);
set @tableHTML =
N''Server Memory Information - '' + @@SERVERNAME +''
'' +
N'''' +
N''TotalMemory MB | Available Memory MB | '' +
N''% Memory Free |
'' +
CAST ( ( SELECT td = [Total Memory MB], '''',
td = [Available Memory MB], '''',
td = [% Memory Free]
FROM MemoryInfo
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''
'' ;
EXEC msdb.dbo.sp_send_dbmail
@from_address=''test@test.com'',
@recipients=''test@test.com'',
@subject = @strsubject,
@body = @tableHTML,
@body_format = ''HTML'' ,
@profile_name=''test profile''
end
drop table MemoryInfo',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Check Connection Count] Script Date: 11/01/2011 14:26:43 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check Connection Count',
@step_id=6,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'create table ConnectionCount (
[spid] bigint NOT NULL,
[blocked] int NOT NULL,
[dbname] varchar(250) NOT NULL,
[open_tran] int NOT NULL,
[status] varchar(250) NOT NULL,
[hostname] varchar(250) NOT NULL,
[cmd] varchar(250) NOT NULL,
[login_time] varchar(250) NOT NULL,
[loginame] varchar(250) NOT NULL,
[net_library] varchar(250) NOT NULL )
insert into ConnectionCount
select spid,blocked,d.name,open_tran,status,hostname,cmd,login_time,loginame,net_library
from sys.sysprocesses p
inner join sys.databases d on p.dbid=d.database_id
where status not like ''background%''
declare @connectioncnt float
select @connectioncnt=COUNT(1) from ConnectionCount
if (@connectioncnt > 500)
begin
declare @strsubject varchar(100)
select @strsubject=''Check user connection count on '' + @@SERVERNAME
declare @tableHTML nvarchar(max);
set @tableHTML =
N''Connection information - '' + @@SERVERNAME +''
'' +
N'''' +
N''SPID | Blocked | '' +
N''DBName | Open_Tran | '' +
N''Status | Hostname | '' +
N''cmd | Login_Time | '' +
N''Login_Name | Net_Library |
'' +
CAST ( ( SELECT td = [spid], '''',
td = [blocked], '''',
td = [dbname], '''',
td = [open_tran], '''',
td = [status], '''',
td = [hostname], '''',
td = [cmd], '''',
td = [login_time], '''',
td = [loginame], '''',
td = [net_library]
FROM ConnectionCount
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''
'' ;
EXEC msdb.dbo.sp_send_dbmail
@from_address=''test@test.com'',
@recipients=''test@test.com'',
@subject = @strsubject,
@body = @tableHTML,
@body_format = ''HTML'' ,
@profile_name=''test profile''
end
drop table ConnectionCount',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Check Batch Requests-Sec] Script Date: 11/01/2011 14:26:43 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check Batch Requests-Sec',
@step_id=7,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=3,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'declare @inibrps bigint
declare @brps decimal(38,2)
select @inibrps=cntr_value
from sys.dm_os_performance_counters
where counter_name LIKE ''Batch Requests/sec%''
waitfor delay ''000:00:10''
select @brps=(cntr_value-@inibrps)/10.0
from sys.dm_os_performance_counters
where counter_name like ''Batch Requests/sec%''
if (@brps > 1000)
begin
declare @strsubject varchar(100)
select @strsubject=''Check batch requests/sec on '' + @@SERVERNAME
declare @tableHTML nvarchar(max);
set @tableHTML =
N''Batch Request rate - '' + @@SERVERNAME +''
'' +
N'''' +
N''Batch Reqests/sec |
'' +
CAST ( ( SELECT td = @brps
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) ) +
N''
'' ;
EXEC msdb.dbo.sp_send_dbmail
@from_address=''test@test.com'',
@recipients=''test@test.com'',
@subject = @strsubject,
@body = @tableHTML,
@body_format = ''HTML'' ,
@profile_name=''test profile''
end
',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Send Completed Email] Script Date: 11/01/2011 14:26:43 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Send Completed Email',
@step_id=8,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'declare @strsubject varchar(100)
select @strsubject=''SQL check completed on '' + @@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@from_address=''test@test.com'',
@recipients=''test@test.com'',
@subject = @strsubject,
@profile_name=''test profile''',
@database_name=N'master',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20111101,
@active_end_date=99991231,
@active_start_time=60000,
@active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO