Sending Mail using SQL Server Express Edition

SQL Server express edition,We either use CLR integration or configure SQL Mail using MSDB system database.

Here I am discussing sending mail using MSDB system database.By default the MSDB database installed when we install SQL Server. The below tables used to confiure sysmail account.

SELECT *FROM msdb.dbo.sysmail_account

SELECT *FROM msdb.dbo.sysmail_configuration

SELECT *FROM msdb.dbo.sysmail_principalprofile

SELECT *FROM msdb.dbo.sysmail_profile

SELECT *FROM msdb.dbo.sysmail_profileaccount

SELECT *FROM msdb.dbo.sysmail_profileaccount

First of all enable Database Mail XPs through below code to configure database mail.

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'Database Mail XPs', 1;

GO

RECONFIGURE

GO

And then to configure SQL mail we need to follow below steps.

1. Use sysmail_add_account_sp stored procedure of MSDB database to configure sysmail account.

EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'DBA',

@description = 'Sent Mail using MSDB',

@email_address = 'raghwendra.mishra@spluspl.com',

@display_name = 'DBA',

@username='raghwendra.mishra@spluspl.com',

@password='Admin@123',

@mailserver_name = 'smtp.Your Mail server.com'

2. Use sysmail_add_profile_sp stored procedure of MSDB database to configure Database Profile.

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'DBA',

@description = 'Profile used to send mail'

3. Use sysmail_add_profileaccount_sp stored procedure of MSDB database to map database mail account to Profile.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'DBA',

@account_name = 'DBA',

@sequence_number = 1

4. To Grants permission for a database user or role to use a Database Mail profile use sysmail_add_principalprofile_sp

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'DBA',

@principal_name = 'public',

@is_default = 1 ;

5.   Send Mail using Created Profile.

exec msdb.dbo.sp_send_dbmail @profile_name = 'DBA', 

@recipients = 'raghwendra.mishra@spluspl.com', 

@subject = 'Mail Test', 

@body = 'Mail Sent Successfully', 

@body_format = 'text'

After executing above query check your email Inbox you will get mail like:

SQL SERVER 2008 – Database Mail Configuration – Send Email From SQL Database

This post will help to configure Database Mail which is used to send the Email using SQL Server. In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out.  

1. Create Profile and Account 

2. Configure Email 

3. Send Email.

Step 1) Create Profile and Account:

We need to create a profile and account using the Configure Database Mail Wizard which can be accessed from the Configure Database Mail in Management Node. This wizard is used to manage accounts, profiles, and Database Mail global settings which are shown below:

Please follow the snapshots in sequence :-

Step 2) Configure Email:

After the Account and the Profile are created successfully, we need to configure the Database Mail. To configure it, we need to enable the Database Mail XPs parameter through the sp_configure stored procedure, as shown here:

SP_CONFIGURE 'show advanced', 1

GO

RECONFIGURE

GO

SP_CONFIGURE 'Database Mail XPs',  1

GO

RECONFIGURE

GO

Step 3) Send Email:

After all configurations are done, we are now ready to send an email. To send mail, we need to execute a stored procedure sp_send_dbmail and provide the required parameters as shown below:

USEmsdb

GO

EXEC sp_send_dbmail @profile_name='demoprofile',

@recipients='raghwendra.mishra@spluspl.com',

@subject='Test mail--Demo mail configuration,

@body='This post will help to configure Database Mail which is used to send the Email using SQL Server. In order to send mail using Database Mail in SQL Server, there are 3 basic steps that need to be carried out.  

1.Create Profile and Account 

2.Configure Email 

3.Send Email. '

Repair a suspect database In MS SQL

Incident:

Yesterday I got a mail from database alert (Automated mail alert) informing me that one of our databases located on the test server went into Suspect Mode. The version used was SQL Server 2005 Service Pack 3.

Solution:

The first step was to identify why this incident occurred and after investigation it was found that it was due to the corruption of the transnational log file of the database.

I connected to SSMS using the sa login credentials and located the SUSPECT database:

Other Possible Causes

• The database could have become corrupted.
• The database cannot be opened due to inaccessible files or insufficient memory or disk space.
• There was an unexpected SQL Server Shutdown, power failure or a hardware failure.

After some RND, I found a set of queries to get database back in normal mode.

EXEC sp_resetstatus 'DBname';

ALTER DATABASE yourDBname SET EMERGENCY

DBCC checkdb('DBname')

ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('DBname', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE yourDBname SET MULTI_USER

Below script will help you to get email notification for 'Suspected Database:

All you have to do is schedules this script in SQL Agent or maintenance plan.

if(select count(*) from sys.databases where state=4)>0

Begin

EXEC msdb.dbo.sp_send_dbmail

    @profile_name = 'SGDBA',--replace with your profine name

    @recipients = 'admi@spluspl.com', --Replace with your email for group email

    @query = '  set nocount off

    select name, state from sys.databases where state = 4',

    @subject = 'Suspected Database',

    @Body = 'Body' ,

    @importance='high',

    @attach_query_result_as_file = 0 ;

END

Else Print 'no suspect databases'

Reference taken from codeproject  and support managed.

SQL SERVER Monitoring Script

Problem

As  a SQL Server DBA, dedicated to monitoring and administering many busy OLTP production environments, I always find myself trying to answer questions like:

What was SQL Server doing last night when the end users were experiencing slow application response times? 
How much time the ABC job taken for execution?
Why the A job get failed?
Are the SQL services running?
What is the OLAP DB size?
Is the backup plan executed properly and what the size of the BKP files.

The answers to such questions are never easy to identify. So how can I figure this out? 


Solution


The following script comes in handy because it captures the code the SQL Server engine is processing at any point in time and delivers it on email with following information.  

Here is that query:

       

CREATE PROC ServerInfo         

AS         

SET NOCOUNT ON         

IF EXISTS(SELECT 1 FROM tempdb..sysobjects where name ='##sqlservice')         

DROP TABLE ##sqlservice         

CREATE TABLE ##sqlservice (details VARCHAR(100))         

IF EXISTS(SELECT 1 FROM tempdb..sysobjects where name ='##agentservice')         

DROP TABLE ##agentservice         

       

CREATE TABLE ##agentservice (details VARCHAR(100))       

   

         

WAITFOR DELAY '00:02'        

        

DECLARE @sname VARCHAR(100), @starttime VARCHAR(30)         

DECLARE @authmode VARCHAR(25), @subject VARCHAR(250)         

DECLARE @insname VARCHAR(50),@agentname VARCHAR(50)         

DECLARE @sqlstatus VARCHAR(100), @agentstatus VARCHAR(100)         

DECLARE @dbstatus VARCHAR(100), @dbdetail VARCHAR(2000)         

DECLARE @sctsql VARCHAR(200), @HTML VARCHAR(8000)  ,@tableHTML VARCHAR(8000)       

DECLARE @dbcount int   

   

select @dbcount=count (*) FROM sys.databases WHERE state_desc <> 'ONLINE'       

   

SELECT @sname = @@SERVERNAME         

SELECT @authmode=case SERVERPROPERTY('IsIntegratedSecurityOnly') when 1 then 'Windows' else 'Mixed' end         

SELECT @starttime=CONVERT(VARCHAR(30),create_date,109) from sys.databases where database_id=2         

IF (serverproperty('InstanceName')) IS NOT NULL         

BEGIN         

SET @insname='mssql$'+CONVERT(VARCHAR(40),serverproperty('InstanceName'))         

SET @agentname='sqlagent$'+CONVERT(VARCHAR(40),serverproperty('InstanceName'))         

END         

ELSE         

BEGIN         

SET @insname='mssqlserver'         

SET @agentname='sqlserveragent'         

END         

       

SELECT @sctsql='EXEC master.dbo.xp_cmdshell ''sc query '+@insname+' | FIND "STATE"'''         

INSERT ##sqlservice         

EXEC (@sctsql)        

        

SELECT @sctsql='EXEC master.dbo.xp_cmdshell ''sc query '+@agentname+' | FIND "STATE"'''         

INSERT ##agentservice         

EXEC (@sctsql)         

       

IF EXISTS(SELECT 1 FROM ##sqlservice WHERE details LIKE '%RUNNING%')         

SET @sqlstatus = 'Running'         

ELSE         

SET @sqlstatus = '<font color="red">Not Running</font>'         

IF EXISTS(SELECT 1 FROM ##agentservice WHERE details LIKE '%RUNNING%')         

SET @agentstatus = 'Running'         

ELSE         

SET @agentstatus = '<font color="red">Not Running</font>'         

IF EXISTS (SELECT 1 FROM sys.databases WHERE state_desc<>'ONLINE')         

BEGIN         

SET @dbstatus= '<font color="red">Some of the database(s) are offline</font>'         

SELECT @dbdetail = '<table border="1"><tr><th>Database Name</th><th>Database Status</th></tr><tr>'          

SELECT @dbdetail = @dbdetail + '<td  align="Center">' + name + '</td><td  align="Center">'+state_desc+'</td></tr>       

</table>' FROM sys.databases WHERE state_desc<>'ONLINE'         

END         

ELSE         

BEGIN         

SET @dbdetail = ''         

SET @dbstatus='All databases are online'         

END         

SET @subject=@sname+' : DB Monitor Alert'         

SET @HTML=       

N'<style type="text/css">       

#box-table       

{       

font-family: "Lucida Sans Unicode", "Lucida Grande", Sans-Serif;       

font-size: 12px;       

text-align: center;       

border-collapse: collapse;       

border-top: 7px solid #9baff1;       

border-bottom: 7px solid #9baff1;       

}       

#box-table th       

{       

font-size: 13px;        

font-weight: normal;       

background: #b9c9fe;       

border-right: 2px solid #9baff1;       

border-left: 2px solid #9baff1;       

border-bottom: 2px solid #9baff1;       

color: #039;       

}       

#box-table td       

{       

border-right: 1px solid #aabcfe;       

border-left: 1px solid #aabcfe;       

border-bottom: 1px solid #aabcfe;       

color: #669;       

}       

tr:nth-child(odd) { background-color:#eee; }       

tr:nth-child(even) { background-color:#fff; }         

</style>'+       

       

'<h3><font color="Green"> -:: '+@sname+' ::- </h3><br>'+'       

<table id="box-table">       

<tr><font color="Green"       

<th>SQL Server Startup time</th><th>SQL Server Service</th><th>SQL Agent Service</th> <th>Database(s) Status</th><th>Authentication Mode</th>       

</tr>       

<tr><td align="Center">'+@starttime+'</td><td align="Center">'+@sqlstatus+'</td><td align="Center">'+@agentstatus+'</td><td align="Center">'+@dbstatus+'</td><td align="Center">'+@authmode+'</td>       

</tr>       

</table>           

<br>'    

if (@dbcount >0 )   

begin      

set @HTML=@HTML + N'<H3><font color="Red"> -:: Off-Line Database Details ::- </H3>' +       

N'<table id="box-table" >' +       

N'<tr><font color="Green"><th>Database Name</th><th>Status</th><th>Created Date</th>       

</tr>' +       

CAST ( (        

SELECT td = CAST([name] AS VARCHAR(200)),'',       

td = [state_desc],'',       

td = CONVERT(VARCHAR(30),[create_date],120)        

FROM sys.databases WHERE state_desc <> 'ONLINE'        

ORDER BY [name]       

FOR XML PATH('tr'), TYPE        

) AS NVARCHAR(MAX) ) +       

N'</table>' +   

    

'<h3><font color="Green"> -:: Job Notification ::- </h3><br>'+'       

<table id="box-table">       

<tr><font color="Green"       

<th>Server Name</th>       

<th>Job Name</th>       

<th>Job Status</th>        

<th>Last Run Status</th>       

<th>Last Date Run</th>       

<th>Run Duration</th>       

</tr>' +        

CAST ( (        

SELECT td = CAST(CAST(Serverproperty('servername') as varchar(100)) AS VARCHAR(200)),'',      

td = j.name,'',       

td = CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END,'',       

td = CASE jh.run_status WHEN 0 THEN 'Error Failed'     

    WHEN 1 THEN 'Succeeded'     

    WHEN 2 THEN 'Retry'     

    WHEN 3 THEN 'Cancelled'     

    WHEN 4 THEN 'In Progress' ELSE     

    'Status Unknown' END ,'',     

         

td = isnull(convert(varchar(12),ja.run_requested_date,107),'-'),'',       

td = isnull(CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108),'-')     

     

FROM(msdb.dbo.sysjobactivity ja      

LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)     

join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id     

WHERE ja.session_id=(SELECT MAX(session_id)  from msdb.dbo.sysjobactivity)  and j.job_id in ( select job_id from msdb.dbo.sysjobs where enabled=1)     

ORDER BY j.name     

FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +       

N'</table>'        

end   

else   

set @HTML=@HTML + N'<H3><font color="Green"> -:: Off-Line Database Details ::- </H3>' +   

N'<table id="box-table" >' +       

N'<tr><font color="Green"><th>Database Name</th><th>Status</th><th>Created Date</th>       

</tr>'+       

CAST ( (        

SELECT td = 'No Database Available','',    

td = '-','',          

td = CONVERT(VARCHAR(30),getdate(),120)        

FOR XML PATH('tr'), TYPE        

) AS NVARCHAR(MAX) ) +       

N'</table>'  +   

    

'<h3><font color="Green"> -:: Job Notification ::- </h3><br>'+'       

<table id="box-table">       

<tr><font color="Green"       

<th>Server Name</th>       

<th>Job Name</th>       

<th>Job Status</th>        

<th>Last Run Status</th>       

<th>Last Date Run</th>       

<th>Run Duration</th>       

</tr>' +       

CAST ( (        

SELECT td = CAST(CAST(Serverproperty('servername') as varchar(100)) AS VARCHAR(200)),'',      

td = j.name,'',       

td = CASE j.enabled WHEN 1 THEN 'Enabled' Else 'Disabled' END,'',       

td = CASE jh.run_status WHEN 0 THEN 'Error Failed'     

    WHEN 1 THEN 'Succeeded'     

    WHEN 2 THEN 'Retry'     

    WHEN 3 THEN 'Cancelled'     

    WHEN 4 THEN 'In Progress' ELSE     

    'Status Unknown' END ,'',     

         

td = isnull(convert(varchar(12),ja.run_requested_date,107),'-'),'',       

td = isnull(CONVERT(VARCHAR(10),CONVERT(DATETIME,RTRIM(19000101))+(jh.run_duration * 9 + jh.run_duration % 10000 * 6 + jh.run_duration % 100 * 10) / 216e4,108),'-')     

     

FROM(msdb.dbo.sysjobactivity ja      

LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id)     

join msdb.dbo.sysjobs_view j on ja.job_id = j.job_id     

WHERE ja.session_id=(SELECT MAX(session_id)  from msdb.dbo.sysjobactivity)  and j.job_id in ( select job_id from msdb.dbo.sysjobs where enabled=1)     

ORDER BY j.name     

FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX) ) +       

N'</table>'        

       

print @HTML        

--print @tableHTML       

       

EXEC msdb.dbo.sp_send_dbmail         

@recipients ='dbaadmin@abc.com', --replace with your email id          

@subject=@subject,         

@body =@HTML,

@body_format ='HTML'