Script to copy errors form error log and get it through mail .

This tim ei was asked to create job/SP that is capable to send a daily mail to DBAs with selected errors form error logs. Below are the steps to create a stored procedure to copy the errors from errorlogs to a table and then send the table as an attachment to our inbox .You might have to modify it as per your need .Or you can use WMI for SQL Server errorlogs ..I did not try that yet ...but will post it once done ...

for mailing you can also use CDO...will post that stuff soon :)






Steps included are :



1) Creating database mail account



2)Creating database mail profile



3) increasing the errorlogs to a default of 50 from 7



4) creating a table to store xp_readerrorlogs values



5) filtering the errors though a query



5) using database mail to send the output as an attachment to inbox



6) Finally recycling the errorlog











USE [msdb]

GO



/*Step 1:Creating a database mail account */

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'Test1',

@description = 'Test account for Database Mail',

@email_address = 'your_email.com',

@display_name = 'Test1',

@mailserver_name = 'smtp..com'



/*Step 2:Creating a database mail profile */

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'TestProfile',

@description = 'Test Profile for database mail'







/*Step 3: adding database mail account to database profile created earlier*/

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'TestProfile',

@account_name = 'Test1',

@sequence_number = 1



/*Step 4:To make the TestProfile we created a default public profile */



EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'TestProfile',

@principal_name = 'default',

@is_default = 1 ;







/* To increase the number of error logs to 50 :

1: regedit >> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\\MSSQLServer

2: create a REG_DWORD key 'NumErrorlogs' and set the value to 50

*/



/* Step 6:create a table for keeping the error log values */



USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO



CREATE TABLE [dbo].[errorlog](

[logdate] [date] NULL,

[processinfo] [varchar](20) NULL,

[line] [varchar](1000) NULL

) ON [PRIMARY]



GO



SET ANSI_PADDING OFF

GO







/* Step 7: Create a stored proc for :

-> Truncating the table we created above .

-> Inserting the values from error log into the table we created above

-> Sending the mail .

-> cycling the errorlog

*/



Create proc [dbo].[ErrorlogEmail]

as

truncate table errorlog

insert into errorlog exec xp_readerrorlog

DECLARE @SQL varchar(2000)

DECLARE @date varchar (2000)

DECLARE @File varchar(1000)

select @date= convert(date,GETDATE())

SET @SQL = 'select * from errorlog where line like (''%Severity%'') or line like (''15 seconds'') or line like (''%latch%'')or line like (''%BEGIN STACK DUMP%'')or line like (''%time-out%'')and logdate = convert(date,GETDATE())'

SET @File = 'Errorlog report'+@date+'.csv'



EXECUTE msdb.dbo.sp_send_dbmail

@profile_name = 'test',

@recipients = 'your email.com',

@subject = 'Errorlog report',

@body = 'Attached please find the Daily errorlog report',

@query =@SQL ,

@attach_query_result_as_file = 1,

@query_attachment_filename = @file,

@query_result_header = 1,

@query_result_separator = ' ',

@query_result_no_padding = 1,

@query_result_width = 32767



/* Step 8: recycle errorlog */

EXEC master.sys.sp_cycle_errorlog

print 'Error log recycled'



Hope this helps ...

Happy Learning ...