Wednesday, December 17, 2014

The process could not execute 'sp_repldone/sp_replcounters

Error messages:
·          The process could not execute 'sp_repldone/sp_replcounters' on 'SCKNLSSI '. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
·          Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. If you executed a log-related procedure, drop the connection over which the procedure was executed or execute sp_replflush over that connection before starting the Log Reader Agent or executing another log-related procedure. (Source: MSSQLServer, Error number: 18752)
Get help: http://help/18752
·          The process could not set the last distributed transaction. (Source: MSSQL_REPL, Error number: MSSQL_REPL22017)
Get help: http://help/MSSQL_REPL22017
·          The process could not execute 'sp_repldone/sp_replcounters' on 'SCKNLSSI'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037


To clear the pending transactions, run the following against the published database
1. Stop the logreader  Agent 
2. Stop the distribution agent:

exec sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1

Turn on both agents.

Wednesday, December 10, 2014

DB Mail Configuration and Testing Mail Profiles

Below Information collected from peer blogs, and very usefull.

To Enable Database Mail execute the following block of code:
use master
exec sp_configure 'show advanced options', 1
exec sp_configure 'Database Mail XPs', 1
Deleting the Old Profiles :
SELECT * FROM msdb.dbo.sysmail_profileaccount pa       JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
      JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id WHERE = @ProfileName AND = @AccountName)
      PRINT 'Deleting Profile Account'
      EXECUTE sysmail_delete_profileaccount_sp       @profile_name = @ProfileName,       @account_name = @AccountName
SELECT * FROM msdb.dbo.sysmail_profile p WHERE = @ProfileName)
      PRINT 'Deleting Profile.'      EXECUTE sysmail_delete_profile_sp       @profile_name = @ProfileName
SELECT * FROM msdb.dbo.sysmail_account a WHERE = @AccountName)
      PRINT 'Deleting Account.'        EXECUTE sysmail_delete_account_sp       @account_name = @AccountName

Creating Accounts & Profiles for DB Mail:
--// Create a Database Mail account
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Test Mail Account',
    @description = 'Mail account for administrative e-mail.',
    @email_address = '',
    @replyto_address = '',
    @display_name = 'TEST Display',
    @mailserver_name = '',
    @port = 587,
    @username = 'xyz',
    @password = 'xxyyzz',
    @enable_ssl = 1

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp     @profile_name = 'Test Mail Profile',     @description = 'Profile used for administrative mail.'
 -- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp     @profile_name = 'Test Mail Profile',     @account_name = 'Test Mail Account',
    @sequence_number =1
 -- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp     @profile_name = 'Test Mail Profile',
    @principal_name = 'public',     @is_default = 1

Sending Mail and Check its status:
--Send mail
EXEC msdb.dbo.sp_send_dbmail     @recipients=N'',
    @body= 'Test Email Body',     @subject = 'Test Email Subject',     @profile_name = 'Test Mail Profile'

--Send mail with attachment
EXEC msdb.dbo.sp_send_dbmail      @profile_name = 'DBMail'
    ,@recipients = ''
    ,@from_address = ''
    ,@query = 'SELECT resource_type, resource_database_id,
                    request_mode, request_session_id
               FROM sys.dm_tran_locks
              WHERE request_mode IN (''IX'', ''X'')'
    ,@subject = 'Exclusive Locks'
    ,@attach_query_result_as_file = 1 ;

Important Tables used in configuring Database mail and check their status:
SELECT * FROM msdb.dbo.sysmail_profile
SELECT * FROM msdb.dbo.sysmail_account
--Profile Accounts
select * from msdb.dbo.sysmail_profileaccount
--Principal Profile
select * from msdb.dbo.sysmail_principalprofile
--Mail Server
SELECT * FROM msdb.dbo.sysmail_server
SELECT * FROM msdb.dbo.sysmail_servertype
SELECT * FROM msdb.dbo.sysmail_configuration
--Email Sent Status
SELECT * FROM msdb.dbo.sysmail_allitems
SELECT * FROM msdb.dbo.sysmail_sentitems
SELECT * FROM msdb.dbo.sysmail_unsentitems
SELECT * FROM msdb.dbo.sysmail_faileditems
--Email Status
SELECT SUBSTRING(fail.subject,1,25) AS 'Subject',       fail.mailitem_id,       LOG.descriptionFROM msdb.dbo.sysmail_event_log LOG
join msdb.dbo.sysmail_faileditems fail ON fail.mailitem_id = LOG.mailitem_id WHERE event_type = 'error'
 --Mail Queues
EXEC msdb.dbo.sysmail_help_queue_sp
--DB Mail Status
EXEC msdb.dbo.sysmail_help_status_sp

A. Sending an e-mail message
This example sends an e-mail message to Dan Wilson using the e-mail address The message has the subject Automated Success Message. The body of the message contains the sentence 'The stored procedure finished successfully'.
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = '',
    @body = 'The stored procedure finished successfully.',
    @subject = 'Automated Success Message' ;

B. Sending an e-mail message with the results of a query

This example sends an e-mail message to Dan Wilson using the e-mail address The message has the subject Work Order Count, and executes a
query that shows the number of work orders with a DueDate less than two days after April 30, 2004. Database Mail attaches the result as a text file.

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Adventure Works Administrator',
    @recipients = '',
    @query = 'SELECT COUNT(*) FROM AdventureWorks2012.Production.WorkOrder
                  WHERE DueDate > ''2004-04-30''
                  AND  DATEDIFF(dd, ''2004-04-30'', DueDate) < 2' ,
    @subject = 'Work Order Count',
    @attach_query_result_as_file = 1 ;

C. Sending an HTML e-mail message

This example sends an e-mail message to Dan Wilson using the e-mail address The message has the subject Work Order List, and contains an HTML document that shows the work orders with a DueDate less than two days after April 30, 2004. Database Mail sends the message in HTML format.


SET @tableHTML =

Work Order Report

' +
    N'' +
    N' Work Order IDProduct ID' +
    N'NameOrder QtyDue Date' +
    N'Expected Revenue
' +
    CAST ( ( SELECT td = wo.WorkOrderID,       '',
                    td = p.ProductID, '',
                    td = p.Name, '',
                    td = wo.OrderQty, '',
                    td = wo.DueDate, '',
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty
              FROM AdventureWorks.Production.WorkOrder as wo
              JOIN AdventureWorks.Production.Product AS p
              ON wo.ProductID = p.ProductID
              WHERE DueDate > '2004-04-30'
                AND DATEDIFF(dd, '2004-04-30', DueDate) < 2
              ORDER BY DueDate ASC,
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='',
    @subject = 'Work Order List',
    @body = @tableHTML,
    @body_format = 'HTML' ;

              Below Code We can use to send test email from all existing DB PROFILES

USE msdb
DECLARE  @Total int
 ,@cnt int  ,@ProfileName varchar(50)
 ,@Server varchar(50)  ,@Sub varchar(400)

SET @Total = (SELECT MAX(profile_id) FROM sysmail_profile)
 SET @cnt = 1
WHILE(@cnt <= @Total)
 SET @ProfileName = (select name from sysmail_profile where profile_id = @cnt)
SET @Sub = 'Testing Email From ' + @Server + ' from profile : ' + @ProfileName
 IF (@ProfileName is not null)
PRINT @ProfileName
EXEC msdb.dbo.sp_send_dbmail
   @recipients = ''
  , @subject = @Sub
  , @body = @Sub
  , @profile_name = @ProfileName
 , @body_format = 'HTML' ;
SET @cnt = @cnt + 1