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.