Automate Monitoring SQL Server Error Logs with Email Alerts

Automate Monitoring SQL Server Error Logs with Email Alerts

Get Alerts for Specific SQL Server Login Failed Events

Get Alerts for Specific SQL Server Login Failed Events

Script to configure SQL database Mail

USE master;
GO
exec sp_configure 'show advanced options', 1;
RECONFIGURE WITH OVERRIDE;
GO
exec sp_configure 'Database Mail XPs','1'
RECONFIGURE WITH OVERRIDE;
GO

use msdb
go
exec sysmail_start_sp

/*
exec msdb.dbo.sysmail_delete_profile_sp @profile_name = 'default' ;
EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = 'default' ;
*/  
 
DECLARE @profile_name sysname,
        @account_name sysname,
        @SMTP_servername sysname,
        @email_address NVARCHAR(128),
   @display_name NVARCHAR(128);

-- Profile name. Replace with the name for your profile
        SET @profile_name = 'default';

-- Account information. Replace with the information for your account.

SET @account_name = 'default';
SET @SMTP_servername = '10.11.150.25';
SET @email_address = REPLACE(@@servername,'-','')+'@csod.com';
        SET @display_name = REPLACE(@@servername,'-','');


-- Verify the specified account and profile do not already exist.
IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
  RAISERROR('The specified Database Mail profile () already exists.', 16, 1);
  GOTO done;
END;

IF EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )
BEGIN
 RAISERROR('The specified Database Mail account () already exists.', 16, 1) ;
 GOTO done;
END;

-- Start a transaction before adding the account and the profile
BEGIN TRANSACTION ;

DECLARE @rv INT;

-- Add the account
EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
    @account_name = @account_name,
    @email_address = @email_address,
    @display_name = @display_name,
    @mailserver_name = @SMTP_servername;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail account ().', 16, 1) ;
    GOTO done;
END

-- Add the profile
EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
    @profile_name = @profile_name ;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail profile ().', 16, 1);
ROLLBACK TRANSACTION;
    GOTO done;
END;

-- Associate the account with the profile.
EXECUTE @rv=msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = @profile_name,
    @account_name = @account_name,
    @sequence_number = 1 ;

IF @rv<>0
BEGIN
    RAISERROR('Failed to associate the speficied profile with the specified account ().', 16, 1) ;
ROLLBACK TRANSACTION;
    GOTO done;
END;

COMMIT TRANSACTION;

done:

GO

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @principal_name = 'public',
    @profile_name = 'default',
    @is_default = 1 ;
GO


EXEC msdb.dbo.sp_send_dbmail @recipients = 'palexander@csod.com', @subject='test', @body='test', @body_format = 'TEXT'

/*

http://technet.microsoft.com/en-us/library/ms177580(v=sql.90).aspx

*/

sp_job_steps_history : Job History for Steps

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[sp_job_steps_history]    Script Date: 02/08/2012 08:13:52 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_job_steps_history]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_job_steps_history]
GO


/*
--all failed steps
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = null, @runstatus = 0 , @stepid = null

--all failed specific steps
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = null, @runstatus = 0 , @stepid = 15

--outcome of the specific step for all jobs all dates
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = null, @runstatus = null , @stepid = 15

--outcome of the specific step for all jobs specific date
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = 20120208, @runstatus = null , @stepid = 15

--all failed steps for given day
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = 20120208, @runstatus = 0 , @stepid = null

--all failed steps for given day specific step
exec msdb.dbo.sp_job_steps_history @jobname = null, @rundate = 20120208, @runstatus = 0 , @stepid = 13

--all failed steps for given job specific date
exec msdb.dbo.sp_job_steps_history @jobname = '%AMERICANSUPPORT%' , @rundate = 20120208, @runstatus = 0 , @stepid = null

--all failed steps for given job all dates
exec msdb.dbo.sp_job_steps_history @jobname = '%AMERICANSUPPORT%' , @rundate = null, @runstatus = 0 , @stepid = null

--specific job status for given dae
exec msdb.dbo.sp_job_steps_history @jobname = '%AMERICANSUPPORT%' , @rundate = 20120208, @runstatus = null , @stepid = null

*/
CREATE PROCEDURE dbo.sp_job_steps_history
(
@jobname nvarchar(256) = null
,@rundate int = null
,@runstatus int = 0
,@stepid int = null
)
as
BEGIN
/*
@runstatus:

0 = Failed
1 = Succeeded
2 = Retry
3 = Canceled
*/

/*
set @jobname = null
set @rundate = null
set @runstatus = 3
*/

SELECT j.name JobName,h.step_id, h.step_name StepName,
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT('000000' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as ExecutionStatus,
h.message MessageGenerated
FROM sysjobhistory h inner join sysjobs j
ON j.job_id = h.job_id
where (j.name like @jobname or @jobname is null)
and (h.run_date = @rundate or @rundate is null)
and (h.run_status = @runstatus or @runstatus is null)
and (h.step_id = @stepid or @stepid is null)
ORDER BY j.name, h.run_date, h.run_time

END
GO

What's the Point of Using VARCHAR(n) Anymore?

What's the Point of Using VARCHAR(n) Anymore?