T-SQL Tuesday #015: Automation:
I am very much interested to participate in the T-SQL Tuesday event party. I have seen the T-SQL Tuesday logo and article in one of my favorite SQL server blog Gail Shaw.
I found the Adam Machanic ( Blog | Twitter ) via Google but, I did not find the required information so, I wrote an email to him and he has sent me the link to topic http://sqlasylum.wordpress.com/2011/02/01/invitation-to-t-sql-tuesday-15-automation-in-sql-server.
Thanks to Adam Machanicfor doing this wonderful job. This is my first T-SQL Tuesday post. I have referred some of T-SQL Tuesday blog post.
This month Pat Wright is hosting and has given us the topic of Automation.
This Month Topic is Automation:
It’s a really great topic for DBA’s because, most of the DBA work is to automate everything (I mean 95% of DBA works).
What most of us think about DBAs?
They don’t have any work they are always enjoying their life. I can say if you don’t have work you are a good DBA 
because you automated/protected everything (No performance issues, No data loss, No application downtime …)
In this article I am going to write about “How to get the SQL server critical error via mail using native T-SQL”.
While doing DBmail test using a Gmail account on my laptop, I got number of errors So, I thought to write an article how to setup the DBmail using Google mail server account.
This is my first version. (I will post it an effective manner at my second version)
What I made in this procedure?
- I have collected all the critical errors from system stored procedure “EXEC DBA_test. DBO. SP_READERRORLOG”
- I have inserted the critical errors to “dba_all_errorlog_details” table.
- Create second stored procedure “usp_dba_critical_error_DBmail” to send an e-mail to DBA team.(Every two hours)
Note: You can insert the critical errors whatever, you thing as critical.
USE [DBA_test]
GO
/****** Object: Table [dbo].[DBA_ALL_ERRORLOG_DETAILS] Script Date: 02/05/2011 12:57:28 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[dba_all_errorlog_details](
[id] [INT] IDENTITY(1,1) NOT NULL,
[date] [DATETIME] NULL,
[processinfo] [SYSNAME] NOT NULL,
[text] [SYSNAME] NOT NULL
) ON [PRIMARY]
USE [DBA_test]
GO
/****** Object: StoredProcedure [dbo].[usp_dba_critical_error] Script Date: 02/05/2011 12:57:51 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author : Muthukkumaran Kaliyamoorthy
-- Create date : 06/02/2011
-- Description :?populate the critical errors to the table
-- =============================================
CREATE PROC [dbo].[usp_dba_critical_error]
AS
BEGIN
--TRUNCATE TABLE DBA_test.DBO.dba_all_errorlog_details
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,
'Setting database option OFFLINE'
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'killed by'
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'alter database'
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'setting database option recovery'
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'account is currently locked out'
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'sql server is terminating due to'
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'deadlock'
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'the log is out of space'
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'error: 9002'
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'severity: 13'
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'severity: 17'
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'severity: 18'
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'severity: 19'
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'severity: 20'
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'severity: 21'
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'severity: 22'
INSERT INTO DBA_test.DBO.dba_all_errorlog_details
EXEC DBA_test.DBO.SP_READERRORLOG 0,1,'severity: 23'
--SELECT * FROM DBA_test.DBO.dba_all_errorlog_details
END
-- Exec [usp_dba_critical_error_DBmail]
USE [DBA_test]
GO
/****** Object: StoredProcedure [dbo].[usp_dba_critical_error_DBmail] Script Date: 02/05/2011 12:57:59 ******/SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author : Muthukkumaran Kaliyamoorthy
-- Create date : 06/02/2011
-- Description :?Send the critical errors to a DBA email account
-- =============================================
CREATE PROC [dbo].[usp_dba_critical_error_DBmail]
AS
BEGIN
EXEC[DBA_test].[DBO].[usp_dba_critical_error]
--truncate table dba_all_errorlog_details
IF EXISTS(SELECT 1 FROM DBA_test.DBO.dba_all_errorlog_details )
BEGIN
DECLARE @processinfo VARCHAR(500)
DECLARE @text VARCHAR(5000)
DECLARE @date VARCHAR(200)
DECLARE @dateadd VARCHAR(200)
DECLARE @maxid int
DECLARE @minid int
SET @dateadd = REPLACE(CONVERT(CHAR(8),DATEADD(HH,-2,GETDATE()),108),':','')
select @minid=MIN(id) from DBA_test.DBO.dba_all_errorlog_details
select @maxid=MAX(id) from DBA_test.DBO.dba_all_errorlog_details
WHILE (@minid<=@maxid)
BEGIN
SELECT @date=[date],@processinfo=[processinfo],@text=[text] FROM DBA_test.DBO.dba_all_errorlog_details
WHERE id=@minid and REPLACE(CONVERT(CHAR(8),DATEADD(hh,-2,date),108),':','') < @dateadd
set @minid=@minid+1
END
DECLARE @body1 VARCHAR(2000)
SET @body1= 'server :following messagees aer crictical errors '+ CHAR(13)
+CHAR(13)
SET @body1= @body1 +
'DATE: '+@date+CHAR(9)+
'PROCESSINFO: '+@processinfo+ CHAR(13)+
'TEXT:'+@text+ CHAR(13)+CHAR(13)
EXEC MSDB.DBO.SP_SEND_DBMAIL @recipients='muthukumark1986@gmail.com',
@subject = 'server :following messagees aer crictical errors',
@body = @body1,
@body_format = 'text' ,@profile_name='test';
END
END
