I want to get the alert message when the sqlserver restarted.

Question

I want to get the alert message when the sqlserver restarted.

charipg

SSCrazy Eights

Points: 9888
More actions
May 20, 2010 at 7:01 pm

#140467

I want to get the alert message when the sqlserver restarted.
How to configure this?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

you can create a job that starts when the sql agent starts; it's one of the options, after you create the job, to determine the schedule....there is a selection for running it when the service starts.

So you can just create a job that emails you with a standard message "the server has restarted" with the date and time;

have you configured your database mail already with a profile?

are you familiar with sp_send_dbmail?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 2

Hi Lowell,

Could you please explain the steps for 'send mail' in SQL Jobs. Im confused about the SMTP server and I doubt whether I can do this in my system at home.

Thanks & Regards,
MC

Lowell SSC Guru Points: 323518 More actions · Answer 3

MC here's some basic steps; the first thing you need is access to an actual mail server , and the email/username/password used to login to the mail server; i believe you can set up GMail as your server.

With that you what you need to set up database mail on your server. mail from the server will see to be coming from the email account we use above, so If you can, you might want to set up a new account, notifications@yourdomain.com or something.

in SSMS,expand the "Management Folder" and find Database Mail. you have to know the account settings of the mailbox that will SEND the data for this; the recipients of the email are later:

after that is set up, and you can send a test email, the code to send an email with a query as the attachment looks like this; you'll need to adapt it to your needs, but use this as a model:

declare @body1 varchar(4000)

set @body1 = 'Sample Email from SQL2008 ' + CONVERT( VARCHAR( 20 ), GETDATE(), 113 ) +

' '

EXEC msdb.dbo.sp_send_dbmail

@profile_name='TestAccount',

@recipients='lowell@somesite.net;donato1026@anothersite.net',

@subject = 'SQL 2008 email test',

@body = @body1,

@body_format = 'HTML',

@query = 'SELECT top 3 * from sysobjects where xtype=''U''',

@query_result_header = 0,

@exclude_query_output = 1,

@append_query_error = 1,

@attach_query_result_as_file = 1,

@query_attachment_filename = 'qry.txt',

@query_result_no_padding = 1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

charipg SSCrazy Eights Points: 9888 More actions · Answer 4

I have configured my database mail already with a profile.

what about the job step?

I need the logic.