Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Kristen
Test
22859 Posts |
 Posted - 2006-10-28 : 05:54:43
|
| One of our SQL boxes was rebooted yesterday morning, and I was called out to an unscheduled meeting and skipped some routine checks that might have caught the issue ... typical 5-things-have-to-go-wrong!Somehow (still to investigate) SQL Agent service [Startup Type] was set to "Manual" rather than "Automatic" ... so it didn't start.(I imagine it was set to MANUAL from some previous reboot where the Admin didn't want SQL Agent to start doing backups etc. until some issue had been sorted out (or multiple reboots were anticipated, or somesuch), and the service was restarted manually and not reconfig'd to Auto Start on Boot).Can I set some sort of alert on that?SQL Server is set to automatically restart if it stops suddenly etc.SQL Server Properties shows "Autostart SQL Server" is tickedSQL Server Properties shows "SQL Server Agent" is ticked too - FWTWActually I wonder if it has worked OK in the past with the Service set to Manual and SQL Server startup actually starting the SQL Agent service??Kristen |
|
|
vaddi
Posting Yak Master
145 Posts |
Posted - 2006-10-28 : 20:07:14
|
| HelloI am giving below , which I am using for the sql server agent , alert incase it does not start , it sends a mail the DBA . And the whole thing should be run as a bat file , and schedule using the windows scheduler.The Script is : declare @cmd nvarchar(1000)declare @cmd2 nvarchar(1000)declare @state1 varchar(100)declare @message varchar(100)DECLARE @date varchar(100)set @date = convert(varchar(100), getdate(),109)set @message = '' -- Build command to determine state of SQLSERVERAGENT service on Master Server SET @CMD = 'create table #state (state varchar(2000))' + char(10) + 'declare @cmdx varchar(1000)' + char(10) + 'insert into #state EXEC master..xp_servicecontrol ''''QueryState'''', ''''SQLSERVERAGENT''''' + + char(10) + 'select @state=state from #state' + char(10) + 'drop table #state' -- Build command to execute command that determines state of service being monitored set @cmd2 = 'declare @state varchar(100)' + char(10) + 'exec ' + rtrim(@@servername) + '.master.dbo.sp_executesql N''' + @CMD + ''',' + 'N''@state varchar(100) out'',' + '@state out' + char(10) + 'set @state1 = @state' -- Execute command and return state of service being monitored exec master.dbo.sp_executesql @cmd2,N'@state1 varchar(100) out',@state1 out -- Is the service that was monitored not IF (UPPER(@state1) <> 'RUNNING.') --if @state1 <1 'Running.' begin -- Display message that primary monitor is down select @message = @message+char(13)+ @@servername + ' -' + 'Sql Server Agent Not Running'+char(13)+ '-' + @date print 'Master server "' + rtrim(@@servername) + '" for monitoring is not available.' -- Perform monitoring print @message EXEC master.dbo.xp_smtp_sendmail@FROM = N'from address@TO = N'to address@server = N'smtp.depaul.edu',@subject = N'Status of sqlserver Agent!',@type = N'text/html',@message = @messageEnd2) The script is to be saved in a notepad with ‘ .sql’ extension. 3) An output file should be created with the name output.txt.4) A Bat file should be created with the script below : osql -E -iC:\serveragent.sql -oC:\outputfile.txt When the above script is created in a bat file , it takes the input file with the name serveragent.sql and writes the output in the file named outputfile.txt6) The bat file is to be scheduled using the windows task scheduler.Thanks |
 |
|
|
|
|
|
|
|
SQL Agent Not Running Test - How?