Error 22022 SqlServerAgent is not currently running....
Dwight Brown
Server Agent. Such as a database backup plan I created.
Now each time I attempt to run a job it errors with;
Error 22022: SQLServerAgent is not currently running so it
cannot be notified of this action.
However, SQL Server Agent is shown to be running in the
task bar.
I am running Windows .Net Server 2003 RC2
Gert E.R. Drapers
pooling on) see
http://support.microsoft.com/default.aspx?scid=kb;en-us;303287
Ge...@SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"Dwight Brown" <dmbPr...@comcast.net> wrote in message
news:2b3301c2f14c$d30fed00$a301...@phx.gbl...
Dwight Brown
Ran the SQL statement from KB article anyway to be sure.
Stopped and restarted server. Still same Problem.
The Job Never starts because SQLServer thinks SQLAgent is
not running. - Very Frustrating.
---- I would be interested to know how SQL server
associates Agent with the windows service. Control panel
services shows the SQLSERVERAGENT, everything looks fine.
no errors. Can stop and start as needed manually.
>.
>
Gert E.R. Drapers
on, use:
exec msdb.dbo.sp_set_sqlagent_properties @errorlogging_level = 7
Send the LOG file with this setting turned on
Also the send the results of:
exec msdb.dbo.sp_get_sqlagent_properties
Ge...@SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"Dwight Brown" <dmbpr...@comcast.net> wrote in message
news:2f4001c2f162$6d5ebed0$a001...@phx.gbl...
Dwight Brown
2003-03-23 13:31:03 - ? [100] Microsoft SQLServerAgent
version 8.00.760 (x86 unicode retail build) : Process ID
2003-03-23 13:31:03 - ? [100] Microsoft SQLServerAgent
version 8.00.760 (x86 unicode retail build) : Process ID
3056
2003-03-23 13:31:03 - ? [101] SQL Server VIPER version
8.00.760 (0 connection limit)
2003-03-23 13:31:03 - ? [102] SQL Server ODBC driver
version 3.85.1015
2003-03-23 13:31:03 - ? [103] NetLib being used by driver
is DBMSSHRN.DLL; Local host server is
2003-03-23 13:31:03 - ? [310] 1 processor(s) and 512 MB
RAM detected
2003-03-23 13:31:03 - ? [339] Local computer is VIPER
running Windows NT 5.2 (3718)
2003-03-23 13:31:03 - ? [124] Subsystem 'TSQL'
successfully loaded (maximum concurrency: 20)
2003-03-23 13:31:03 - ? [124] Subsystem 'CmdExec'
successfully loaded (maximum concurrency: 10)
2003-03-23 13:31:03 - ? [124] Subsystem 'Snapshot'
successfully loaded (maximum concurrency: 100)
2003-03-23 13:31:03 - ? [124] Subsystem 'Distribution'
successfully loaded (maximum concurrency: 100)
2003-03-23 13:31:03 - ? [124] Subsystem 'Merge'
successfully loaded (maximum concurrency: 100)
2003-03-23 13:31:04 - ? [124] Subsystem 'ActiveScripting'
successfully loaded (maximum concurrency: 10)
2003-03-23 13:31:04 - ? [124] Subsystem 'QueueReader'
successfully loaded (maximum concurrency: 100)
2003-03-23 13:31:04 - ? [124] Subsystem 'LogReader'
successfully loaded (maximum concurrency: 25)
2003-03-23 13:31:04 - ! [364] The Messenger service has
not been started - NetSend notifications will not be sent
2003-03-23 13:31:04 - ? [129] SQLSERVERAGENT starting
under Windows NT service control
2003-03-23 13:31:04 - + [260] Unable to start mail session
(reason: No mail profile defined)
2003-03-23 13:31:04 - ? [174] Job scheduler engine started
(maximum worker threads: 0)
2003-03-23 13:31:04 - ? [193] Alert engine started (using
Eventlog Events)
2003-03-23 13:31:04 - ? [146] Request servicer engine
started
2003-03-23 13:31:04 - ? [167] Populating job cache...
2003-03-23 13:31:04 - ? [168] There are 1 job(s) [0
disabled] in the job cache
2003-03-23 13:31:04 - ? [170] Populating alert cache...
2003-03-23 13:31:04 - ? [171] There are 9 alert(s) in the
alert cache
2003-03-23 13:31:04 - + [396] An idle CPU condition has
not been defined - OnIdle job schedules will have no effect
2003-03-23 13:31:04 - ? [133] Support engine started
2003-03-23 13:31:18 - ! [LOG] The data portion of event
17052 from MSSQLSERVER is invalid
2003-03-23 13:31:18 - ! [LOG] The data portion of event
17052 from MSSQLSERVER is invalid
///results of sp_getsqlagent_prop../////
sorry for the formatting....
Message Tab Results
Access is denied.
Access is denied.
GRID Results below
auto start
1
msx server name
null
sqlagent_type
1
startup account
.\SQLAgent
sqlserver_restart
1
jobhistory_max_rows
1000
jobHistory_max_rows_per_job
100
errorlog_file
C:\Program Files\Microsoft SQL
Server\MSSQL\LOG\SQLAGENT.OUT
errorlogging_level
7
error_recipient
null
monitor_autstart
o
local_host_server
NULL
job_shutdown_timeout
15
emdexec_account
null
regular_connections
0
host_login_name
NULL
host_login_password
NULL
login_timeout
30
idle_cpu_precent
10
idle_cpu_duratioin
600
oem_errorlog
0
sysadmin_only
1
email_profile
NULL
email_save_in_sent_folder
0
cpu_poller_enabled
0
>.
>
Gert E.R. Drapers
sp_get_sqlagent_properties wories me.
The SQLAGENT.OUT file is OK, there is a problem with reading log entries by
the alter engine, but that is not related to your current problem.
Could you try executing the same sp_get_sqlagent_properties to a Text
windows instead of Grid, this way you will see what output line the Access
Denied is, so I can relate it back to the entry read.
Anyhow SQL Agent does start normally, so sp_sqlagent_notify is the part that
seems to be failing.
CREATE PROCEDURE sp_sqlagent_notify
@op_type NCHAR(1), -- One of: J (Job action [refresh or
start/stop]),
-- S (Schedule action
[refresh only])
-- A (Alert action [refresh
only]),
-- G (Re-cache all registry
settings),
-- D (Dump job [or job
schedule] cache to errorlog)
-- P (Force an immediate
poll of the MSX)
@job_id UNIQUEIDENTIFIER = NULL, -- JobID (for OpTypes 'J', 'S' and
'D')
@schedule_id INT = NULL, -- ScheduleID (for OpType 'S')
@alert_id INT = NULL, -- AlertID (for OpType 'A')
@action_type NCHAR(1) = NULL, -- For 'J' one of: R (Run - no
service check),
-- S (Start - with
service check),
-- I (Insert),
-- U (Update),
-- D (Delete),
-- C (Stop [Cancel])
-- For 'S' or 'A' one of: I
(Insert),
-- U
(Update),
-- D (Delete)
@error_flag INT = 1 -- Set to 0 to suppress the error
from xp_sqlagent_notify if SQLServer agent is not running
You can try starting your job this way:
declare @job_id uniqueidentifier
select @job_id = job_id from msdb.dbo.sysjobs where name = 'YourMyJob'
select @job_id
exec msdb.dbo.sp_sqlagent_notify
@op_type = 'J',
@job_id = @job_id,
@schedule_id = NULL,
@alert_id = NULL,
@action_type = 'S',
@error_flag = 1 -- Set to 0 to suppress the error from
xp_sqlagent_notify if SQLServer agent is not running
Ge...@SQLDev.Net
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright © SQLDev.Net 1991-2003 All rights reserved.
"Dwight Brown" <dmbPr...@comcast.net> wrote in message
news:2f7f01c2f16e$97d0b810$a001...@phx.gbl...
dwight brown
home tonight.
Also the Access Denied Appears as the first two lines in
the text output, I remembered that from yesterday..
Thanks Dwight
>..\SQLAgent
>.
>
Dwight Brown
Server: Msg 170, Level 15, State 1, Procedure
sp_sqlagent_notify, Line 3
Line 3: Incorrect syntax near 'start'.
Server: Msg 170, Level 15, State 1, Procedure
sp_sqlagent_notify, Line 47
Line 47: Incorrect syntax near 'xp_sqlagent_notify'.
dwight
>.
>
Dwight Brown
However am having trouble. Ran the part you suggested as
try to strart your job this way. Results below..
(1 row(s) affected)
Server: Msg 14262, Level 16, State 1, Procedure
sp_sqlagent_notify, Line 54
The specified @job_id ('(null)') does not exist.
I am sure It is something I failed to add to the job, like
a job id. Not sure where..
dwight
>.
>
dwight Brown
any way this is the result:
(1 row(s) affected)
Server: Msg 22022, Level 16, State 1, Line 0
SQLServerAgent is not currently running so it cannot be
notified of this action.
dwight
>.
>