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.

ValidateDbMailSettings

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.

MigrateDbMailSettings

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