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
13 Comments
Anonymous
Good script. It's really helped.
Thanks
Anonymous
I don't have work…
🙂
Paul
vinoth
Muthu,
Excellent automation script.
Good to see this participation…
Muthukkumaran Kaliyamoorthy
@Anonymous & @Paul
Thanks.Me too don't have work (Always singing & dancing)
LOL…
Muthukkumaran Kaliyamoorthy
@vinoth,
Thanks buddy…
Anonymous
Hi,
Its good script for DBA's.
One suggestion the script not working for 2000 servers.You can insert all the SP resluts to one table and use 'where clause' in select statement.
jason
Muthukkumaran Kaliyamoorthy
jason Thanks,definitely i'll cover this in my next part.
Muthukkumaran Kaliyamoorthy
profile_name='DBA';
http://sqlserverblogforum.blogspot.com/2011/02/how-to-setup-database-mail-in-sql.html
Mark Willium
Good script. thnx for sharing.
Muthukkumaran Kaliyamoorthy
Thanks Mark.
Nancy Dorothy
Appreciated the share!
Nancy
Muthukkumaran kaliyamoorthy
Nancy Glad you liked it.
sql server masters
Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! Its always nice when you can not only be informed, but also entertained! Im sure you had fun writing this article.
Valuable information and excellent design you got here! I would like to thank you for sharing your thoughts and time into the stuff you post!!
http://sqlservermasters.com/