Archive

Posts Tagged ‘SQL Server Agent’

Validate SQL Server Database Mail Settings

November 13, 2017 1 comment

In my last post, I shared a script to automate the migration of SQL Server Database Mail settings. In this post, I show how to send test e-mails from all Database Mail profiles on an instance. The migration I was working on contained 21 Database Mail profiles. The following script will send a test e-mail from each profile to confirm successful configuration. I hope you can put this code to use in your migrations.

USE msdb;

SET NOCOUNT ON;

/* Drop and create temp table
   and declare local variables */
DROP TABLE IF EXISTS #profiles;

CREATE TABLE #profiles
(RowId INT IDENTITY(1,1)
,ProfileName SYSNAME);

/* Update @recipient 
   Set @noexec = 1 to print the EXEC sp_send_dbmail statements
   Set @noexec = 0 to execute the statements immediately */
DECLARE @profilename SYSNAME,
@sqlstr NVARCHAR(2000),
@loopcount INT = 1,
@looplimit INT,
@noexec INT = 1,
@recipient NVARCHAR(255) = N'fgill@concurrency.com';

/* Insert Database Mail profile names into temp table
   and initialize the loop limit variable */
INSERT INTO #profiles
(ProfileName)
SELECT name
FROM sysmail_profile;

SELECT @looplimit = MAX(RowId) FROM #profiles;

/* Loop through the list of Database Mail profiles and 
   execute sp_send_dbmail for each one */
WHILE @loopcount <= @looplimit
BEGIN

	SELECT @profilename = ProfileName FROM #profiles WHERE RowId = @loopcount;


	SELECT @sqlstr = CONCAT('EXEC msdb.dbo.sp_send_dbmail  
		@profile_name = ''',
	@profilename,''',  
		@recipients = ''', @recipient, ''',  
		@body = ''Database mail succeeded for ', @profilename, ' on SQLWEBDB-01.'',  
		@subject = ''Database mail test for ', @profilename, ';''');

	SELECT @loopcount += 1;

	IF @noexec = 1
	BEGIN

		PRINT @sqlstr;

	END
	ELSE
	BEGIN

		EXEC sp_executesql @sqlstr;

	END
		
END
Categories: SQL Server, SQL Server Database Mail, T-SQL Tags: , ,

Migrating SQL Server Database Mail Settings

November 6, 2017 3 comments

This week, I was working on a migration for a client.  The migration was moving databases from a stand-alone instance to a two-node Availability Group.  When it came to moving the Database Mail settings, I discovered they had 21 sets of profiles and accounts.  Not wanting to manually create 42 Database Mail profiles, I set out to automate the process.  A web search yielded this blog post by Iain Elder. This script does what I was looking for, but would only generate settings for a single Database Mail profile.  Using Iain’s code as a starting point, I modified it to create Database Mail settings for all profiles on an instance.  The script is listed below. I hope this simplifies your SQL Server migrations.

USE msdb;
GO

/*
	Author -		Frank Gill, Concurrency
	Date -			2017-08-18
	Description -	Script queries sys tables to generate dynamic SQL statements
					to recreate database mail accounts and profiles
					Code modified to generate multiple mail profiles from the script found here:
					https://basetable.wordpress.com/2012/04/03/script-out-database-mail-settings/
	Note -			Execute query in SQL Server Management Studio and output Results to Text (Ctrl + T)
					Copy the results to a separate query window 
					For mail acounts with passwords, the password will appear as 'NotTheRealPassword'
					You will need to change this to the real password
					Each result set will contain a header row of Text
					Do a group replace (Ctrl + H) of Text to --Text to comment the headers out
					
*/

SET NOCOUNT ON;

/* Drop and create temp table 
   Declare local variables */
IF OBJECT_ID('tempdb.dbo.#sysmail_info') IS NOT NULL
BEGIN

	DROP TABLE #sysmail_info;

END

CREATE TABLE #sysmail_info
(RowId INT IDENTITY(1,1)
,account_name SYSNAME
,email_address NVARCHAR(120)
,display_name NVARCHAR(128)
,replyto_address NVARCHAR(128)
,[description] NVARCHAR(256)
,servername SYSNAME
,servertype SYSNAME
,[port] INT
,credential_identity NVARCHAR(4000)
,use_default_credentials BIT
,enable_ssl BIT
,profile_name SYSNAME
,sequence_number INT
,database_principal_name SYSNAME NULL
,is_default BIT);

DECLARE @loopcount INT = 1,
@looplimit INT,
@SQLText VARCHAR(MAX),
@ProfileText VARCHAR(MAX),
@CrLf CHAR(2) = CHAR(13) + CHAR(10);

/* Insert the required database mail info into the temp table */
INSERT INTO #sysmail_info
(account_name
,email_address
,display_name
,replyto_address
,[description]
,servername
,servertype
,[port]
,credential_identity
,use_default_credentials
,enable_ssl
,profile_name
,sequence_number
,database_principal_name
,is_default)
SELECT a.name AS account_name,
a.email_address,
a.display_name,
a.replyto_address,
a.[description],
servername,
servertype,
[port],
c.credential_identity,
s.use_default_credentials,
s.enable_ssl,
p.name AS profile_name,
pa.sequence_number,
dp.name AS database_principal_name,
pp.is_default
FROM msdb.dbo.sysmail_profile AS p
INNER JOIN msdb.dbo.sysmail_profileaccount AS pa ON
  p.profile_id = pa.profile_id
INNER JOIN msdb.dbo.sysmail_account AS a ON
  pa.account_id = a.account_id
LEFT OUTER JOIN msdb.dbo.sysmail_principalprofile AS pp ON
  p.profile_id = pp.profile_id
LEFT OUTER JOIN msdb.sys.database_principals AS dp ON
  pp.principal_sid = dp.sid
LEFT OUTER JOIN msdb.dbo.sysmail_server AS s ON
  a.account_id = s.account_id
LEFT OUTER JOIN sys.credentials AS c ON
  s.credential_id = c.credential_id;

/* Set loop limit to max RowId value */
SELECT @looplimit = MAX(RowId) FROM #sysmail_info;

/* Generate commands to enable database mail */
SELECT @SQLText = '
EXEC msdb.dbo.sp_configure
	@configname = ''show advanced options'',
	@configvalue = 1;
RECONFIGURE;

EXEC msdb.dbo.sp_configure
	@configname = ''Database Mail XPs'',
	@configvalue = 1;
RECONFIGURE;';

SELECT @SQLText AS [Text];

/* Loop through each row in the temp table 
   build commands to recreate database mail accounts and profiles */
WHILE @loopcount <= @looplimit
BEGIN

	SELECT @SQLText = '

	EXECUTE msdb.dbo.sysmail_add_profile_sp
	  @profile_name = ''' + profile_name + ''',
	  @description  = ''' + ISNULL([description],'') + ''';

	EXEC msdb.dbo.sysmail_add_account_sp
	  @account_name = ' + CASE WHEN account_name IS NULL THEN 'NULL' ELSE + '''' + account_name + '''' END + ',
	  @email_address = ' + CASE WHEN email_address IS NULL THEN 'NULL' ELSE + '''' + email_address + '''' END + ',
	  @display_name = ' + CASE WHEN display_name IS NULL THEN 'NULL' ELSE + '''' + display_name + '''' END + ',
	  @replyto_address = ' + CASE WHEN replyto_address IS NULL THEN 'NULL' ELSE + '''' + replyto_address + '''' END + ',
	  @description = ' + CASE WHEN [description] IS NULL THEN 'NULL' ELSE + '''' + [description] + '''' END + ',
	  @mailserver_name = ' + CASE WHEN servername IS NULL THEN 'NULL' ELSE + '''' + servername + '''' END + ',
	  @mailserver_type = ' + CASE WHEN servertype IS NULL THEN 'NULL' ELSE + '''' + servertype + '''' END + ',
	  @port = ' + CASE WHEN [port] IS NULL THEN 'NULL' ELSE + '''' + CONVERT(VARCHAR,[port]) + '''' END + ',
	  @username = ' + CASE WHEN credential_identity IS NULL THEN 'NULL' ELSE + '''' + credential_identity   + '''' END + ',
	  @password = ' + CASE WHEN credential_identity IS NULL THEN 'NULL' ELSE + '''NotTheRealPassword''' END + ',
	  @use_default_credentials = ' + CASE WHEN use_default_credentials = 1 THEN '1' ELSE '0' END + ',
	  @enable_ssl = ' + CASE WHEN enable_ssl = 1 THEN '1' ELSE '0' END + ';

	EXEC msdb.dbo.sysmail_add_profileaccount_sp
	  @profile_name = ''' + profile_name + ''',
	  @account_name = ''' + account_name + ''',
	  @sequence_number = ' + CAST(sequence_number AS NVARCHAR(3)) + ';
	' +
	  COALESCE('
	EXEC msdb.dbo.sysmail_add_principalprofile_sp
	  @profile_name = ''' + profile_name + ''',
	  @principal_name = ''' + database_principal_name + ''',
	  @is_default = ' + CAST(is_default AS NVARCHAR(1)) + ';
	', '')
	FROM #sysmail_info
	WHERE RowId = @loopcount;

	WITH R2(N) AS (SELECT 1 UNION ALL SELECT 1),
	R4(N) AS (SELECT 1 FROM R2 AS a CROSS JOIN R2 AS b),
	R8(N) AS (SELECT 1 FROM R4 AS a CROSS JOIN R4 AS b),
	R16(N) AS (SELECT 1 FROM R8 AS a CROSS JOIN R8 AS b),
	R32(N) AS (SELECT 1 FROM R16 AS a CROSS JOIN R16 AS b),
	R64(N) AS (SELECT 1 FROM R32 AS a CROSS JOIN R32 AS b),
	R128(N) AS (SELECT 1 FROM R64 AS a CROSS JOIN R64 AS b),
	Tally(N) AS (
	  SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
	  FROM R128
	),
	SplitText(SplitIndex, [Text]) AS (
	  SELECT
		N,
		SUBSTRING(
		  @CrLf + @SQLText + @CrLf,
		  N + DATALENGTH(@CrLf),
		  CHARINDEX(
			@CrLf,
			@CrLf + @SQLText + @CrLf,
			N + DATALENGTH(@CrLf)
		  ) - N - DATALENGTH(@CrLf)
		)
	  FROM Tally
	  WHERE
		N < DATALENGTH(@CrLf + @SQLText) AND
		SUBSTRING(@CrLf + @SQLText + @CrLf, N, DATALENGTH(@CrLf)) = @CrLf
	)
	SELECT [Text]
	FROM SplitText
	ORDER BY SplitIndex;

	SELECT @loopcount += 1;

END
Categories: SQL Server, SQL Server Database Mail, T-SQL Tags: , ,

Reading the xp_readerrorlog Using a WHILE Loop

April 13, 2012 3 comments

Last week, I needed to search through all of the SQL Server error logs on an instance. I have used xp_readerrorlog before, using the information found here. We retain 99 log files, which is the maximum number of files that can be kept. I needed to search through all of these files, so I put together the following script. This is another example of using a WHILE loop in gathering information from SQL Server.

USE master
GO

-- Create temp table to hold the output of the error logs

CREATE TABLE #errorlog
(errdate DATETIME
,errproc SYSNAME
,errtext VARCHAR(4000))

-- Declare and initialize the @loopcount variables

DECLARE @loopcount INT
SET @loopcount = 1

-- Loop through the error logs
-- We keep the 99 logs, which the maximum
-- Change the loop limit accordingly

WHILE @loopcount <= 99
BEGIN

	-- Insert the results of xp_readerrorlog into the temp table
	
	INSERT INTO #errorlog
	
	-- You can find the parameters for xp_readerrorlog at the following link
	-- http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/
	-- The parms I use below are as follows:
	-- @loopcount - file number, with 0 being the current file
	-- 1 - indicates the SQL error log - a 2 would query the SQL Agent log
	-- 'DELETE' - a search argument
	
	EXEC xp_readerrorlog @loopcount, 1, 'DELETE'
	
	-- Increment your loop counter
	
	SET @loopcount = @loopcount + 1
END

-- Select the results of your loop
-- Alternately, you can dump the entire log file and then use WHERE predicates to 
-- select what you are interested in

SELECT * FROM #errorlog

-- Clean up your temp table
DROP TABLE #errorlog
Categories: SQL Server, SQL Server Agent, T-SQL Tags: , ,

Checking SQL Server Agent Status Using xp_cmdshell

March 6, 2012 1 comment

I work in a clustered environment running more than 200 instances of SQL Server 2008. Several years ago, we began having problems with the SQL Server Agent service failing after an instance failed over. We had already developed a process to check the health of the SQL Server service, so I set out to add an Agent check to it.

Doing some web searching, I came across a number of links that suggested running the following query to determine if SQL Server Agent was running:

SELECT COUNT(*) 
FROM master.dbo.sysprocesses 
WHERE program_name = N'SQLAgent - Generic Refresher'

In the tests I ran, this worked just fine. I put it into a procedure, rolled it out to our test environment and let it run for a week, where we had no issues. It caught a number of times that the Agent was down and provided no false positives. At this point I rolled the proc out to production.

Several weeks later, we started receiving messages that the SQL Agent was down on an instance. Management Studio and Windows Failover Cluster Management showed the process was up and running. We were able to access Agent jobs and metadata, and jobs were running successfully. I queried the dbo.sysprocesses DMO on the instance and did not get a hit for ‘SQL Agent – Generic Refresher’.

This led me to open a ticket with Microsoft to find out if they could tell me why there was no row in the sys.processes DMO when the process was running. If they couldn’t do that, I was hoping they could tell me a better way to check status programmatically. I was told that querying the sys.processes table was not recommended and that they could not provide me with any other suggestions.

At that point, other things took precedence until two weeks ago, when we started receiving messages the Agent was down on an instance. Taking a look at the sys.processes table, there was no row for ‘SQLAgent – Generic Refresher’.

The instance that was failing was excluded from the job and I worked on another method of checking the Agent service. Since the last time I looked at this issue, I gained experience using xp_cmdshell in T-SQL code. This led me to use the following command:

EXEC xp_cmdshell 'tasklist'
GO

This code returns a list of all of the processes running on the machine. By inserting the results of this code into a temp table, you can run a select to check for the existence of the SQL Agent process. That code looks like this:

USE master
GO

CREATE TABLE #processes
(processlist VARCHAR(4000))

INSERT INTO #processes
EXEC xp_cmdshell 'tasklist'

SELECT * FROM #processes
WHERE processlist LIKE '%sqlagent%'

DROP TABLE #processes

Here are the query results:

processlist
SQLAGENT.EXE 9896 Services 0 38,316 K

I’ve put that block of code into a procedure that loops through all of the instances in our environment checking the status of SQL Server Agent. If the check fails, an e-mail is sent out to the DBA team. I will cover this procedure in my next post.

Categories: SQL Server, SQL Server Agent Tags: , ,