Paul Winans, At Your Service

What to do if database mail is not working.

Mail was not being sent. First I queried

SELECT * FROM msdb.dbo.sysmail_event_log

and saw in the description field

“The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-03-31T12:24:10). Exception Message: Could not connect to mail server. (A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond [ip address]:[port number]). )”

Running

EXEC msdb.dbo.sysmail_help_status_sp ;

returned “inactive”

Some websites suggested that the people trying to send mail need to be in the msdb role DatabaseMailUserRole, so I checked that

EXEC msdb.sys.sp_helprolemember ‘DatabaseMailUserRole’ ;

but then I remembered I am a sysadmin and am not able to send mail.

Then I practically ran this

select name , is_broker_enabled from sys.databases

and this and then could send mail:

alter database msdb set single_user with rollback immediate
alter database msdb set enable_broker
alter database msdb set MULTI_USER

These procedures may be helpful too:

exec msdb..sysmail_help_principalprofile_sp
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = ‘mail’ ;
exec msdb..sysmail_stop_sp
exec msdb..sysmail_start_sp

Except the jobs still could not send mail. For some reason sql server agent did not have database mail set up. After pointing and clicking to get that together I thought I was done, except now I got this error:

The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 1 (2009-07-06T14:10:00). Exception Message: Cannot send mails to mail server. (The specified string is not in the form required for an e-mail address.). )

This error was because the operators were defined with mapi names and not full email addresses.

Whew done!

I was asked to trace department’s access to a certain sql server here.

To create a trace that is run by sql server, open profiler and define your trace. Then, while it is not running, you can script it by going to the File-Script Trace menu and save the sql.

Either make a stored procedure which contains the sql or just paste in into a job step. Have your job run when sql server agent starts or set your procedure as a startup procedure with sp_procoption.

Once the trace is running you can check on it with:
SELECT * FROM ::fn_trace_getinfo ([the trace id])

Stop it with:
exec msdb..sp_trace_setstatus [the trace id] , 0 — stop the trace
exec msdb..sp_trace_setstatus [the trace id] , 2 — close and delete the trace defination

I also added the following so if there was an existing output file it would be renamed.
exec master..xp_cmdshell ‘ren [path to your trace output] “%date:~10,4%%date:~4,2%%date:~7,2%_%time:~0,2%%time:~3,2%%time:~6,2%-[some common name]”‘

Also, I increased sp_trace_create’s parameters @options to rollover to a new file and @maxfilesize to a much larger file size just in case.

Later I will import the results into sql server and total the cpu, duration, and reads and writes by workstation.

In my combing through of the sql servers I found a startup stored procedure on one of the sql servers here. I didn’t recognize the tables it created or its name, so I asked around: no one else knew anything either. How to gracefully deal with this? Simply remove its startup property creates a timebomb. I decided to alert the production services manager, who gets all the issue emails, send what to do if there is a complaint and I am not available, and then drop what the procedure creates and hold my breath.

use tempdb
go
drop table <<table name>>
drop table <<table name>>
 
— remove it as a startup procedure
use master
go
exec sp_procoption @procname = ‘<<proc name>>’, @optionname = ‘startup’, @optionvalue = ‘false’
go
 
— shows that there are no startup procedures
select name from sysobjects where type = ‘p’ and OBJECTPROPERTY(id, ‘ExecIsStartup’) = 1

— So, if people complain, then run 
exec master..<<proc name>>
exec sp_procoption @procname = ‘<<proc name>>’, @optionname = ‘startup’, @optionvalue = ‘true’

I configured 3 of the development sql servers to use the available memory last night. 2 had 8gb; 1 had 4. The one with 4 just required adding the /3GB and a restart; for the other two I modified the boot.ini, restarted the server, and then ran sp_configure to set the max memory and to enable awe. They only claimed 3gb of the available memory though.

After some searching I found hotfix 899761 is needed for sql server 2000 sp4.  If the sql server version is 8.00.039 and it isn’t using all the available memory, apply the hotfix and get to 8.00.040. How long has sp4 been out and where have I been? A story for another time.

I’ve been referring to this article for years to find what combo of PAE and 3GB to use.

The first time I did this on Windows Server 2003 I remember crapping in my pants because I couldn’t figure out why sql server didn’t grab the memory like it did with Windows 2000.