May 2, 2007 at 10:15 am
Hi,
I have job history problems. I created a test job which is running every minute and simply inserts current time-stamp into a dummy table. It's actually running because I can check results from that dummy table.
However when I check history in SSMS GUI tool, it shows nothing. If I run select * from sysjobhistory in msdn database, it shows nothig either. Job activity monitor status is Executing or Performing completion action, however in the Last Run column it shows Unknown.
In Agent properties the check-box for limiting the size of job history is clear. When it was set to 1000, result was the same.
Where else chould I check and what to troubleshoot in order to be able to see jobs historical information?
My SQL Server version is 9.00.2047.00.
Thanks
May 2, 2007 at 10:18 am
Sure you dont have a filter applied to the history view? Are you getting history logged from other jobs?
May 2, 2007 at 11:54 am
What filter should I apply ? An how about select * from sysjobhistory ?
It was just one job running on this server, but now I created another one running every minute, but the problem is still the same - it does not show history.
May 2, 2007 at 1:30 pm
Sorry, I should have been clearer, my first thought was that someone had applied a filter to the log viewer when you look at the job history in SSMS. If you're not getting any rows back from select * from history we can rule that out. Have you tried profiling to see what happens during/after the job run?
May 2, 2007 at 2:49 pm
OK, I setup Profiler with output to a table. I ran this query against that table:
select top 50 EventClass, TextData=substring(TextData,1,50), ApplicationName
from monitor
where ApplicationName like 'sqlagent%'
and starttime >'2007-05-02 16:32:00.000'
order by starttime
And got this result:
EventClass TextData ApplicationName
----------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
15 NULL SQLAgent - Job Manager
12 set quoted_identifier off SQLAgent - Job Manager
13 set quoted_identifier off SQLAgent - Job Manager
14 -- network protocol: LPC
set quoted_identifier on SQLAgent - Job Manager
12 UPDATE msdb.dbo.sysjobactivity SET run_requested_d SQLAgent - Job invocation engine
13 UPDATE msdb.dbo.sysjobactivity SET run_requested_d SQLAgent - Job invocation engine
12 SELECT N'Testing Connection...' SQLAgent - Job invocation engine
13 SELECT N'Testing Connection...' SQLAgent - Job invocation engine
12 DECLARE @startExecutionDate DATETIME SET @startExe SQLAgent - Job Manager
13 DECLARE @startExecutionDate DATETIME SET @startExe SQLAgent - Job Manager
12 SELECT ISNULL(SUSER_SNAME(), SUSER_NAME()) SQLAgent - Job Manager
13 SELECT ISNULL(SUSER_SNAME(), SUSER_NAME()) SQLAgent - Job Manager
12 select convert(sysname, serverproperty(N'servernam SQLAgent - Job Manager
13 select convert(sysname, serverproperty(N'servernam SQLAgent - Job Manager
12 select @@microsoftversion SQLAgent - Job Manager
13 select @@microsoftversion SQLAgent - Job Manager
12 EXECUTE msdb.dbo.sp_sqlagent_has_server_access @lo SQLAgent - Job Manager
13 EXECUTE msdb.dbo.sp_sqlagent_has_server_access @lo SQLAgent - Job Manager
12 SELECT N'Testing Connection...' SQLAgent - Job Manager
13 SELECT N'Testing Connection...' SQLAgent - Job Manager
12 EXECUTE msdb.dbo.sp_help_jobstep @job_id = 0x69058 SQLAgent - Job Manager
13 EXECUTE msdb.dbo.sp_help_jobstep @job_id = 0x69058 SQLAgent - Job Manager
12 SELECT N'Testing Connection...' SQLAgent - Job Manager
13 SELECT N'Testing Connection...' SQLAgent - Job Manager
15 NULL SQLAgent - TSQL JobStep (Job 0x69058A7D01F50F4499D98AFD8A6BFFFC : Step 1)
12 insert into test_jobs
select getdate()
SQLAgent - TSQL JobStep (Job 0x69058A7D01F50F4499D98AFD8A6BFFFC : Step 1)
13 insert into test_jobs
select getdate()
SQLAgent - TSQL JobStep (Job 0x69058A7D01F50F4499D98AFD8A6BFFFC : Step 1)
12 SET TEXTSIZE 1024 SQLAgent - TSQL JobStep (Job 0x69058A7D01F50F4499D98AFD8A6BFFFC : Step 1)
13 SET TEXTSIZE 1024 SQLAgent - TSQL JobStep (Job 0x69058A7D01F50F4499D98AFD8A6BFFFC : Step 1)
12 SELECT ISNULL(SUSER_SNAME(), SUSER_NAME()) SQLAgent - TSQL JobStep (Job 0x69058A7D01F50F4499D98AFD8A6BFFFC : Step 1)
13 SELECT ISNULL(SUSER_SNAME(), SUSER_NAME()) SQLAgent - TSQL JobStep (Job 0x69058A7D01F50F4499D98AFD8A6BFFFC : Step 1)
12 select convert(sysname, serverproperty(N'servernam SQLAgent - TSQL JobStep (Job 0x69058A7D01F50F4499D98AFD8A6BFFFC : Step 1)
13 select convert(sysname, serverproperty(N'servernam SQLAgent - TSQL JobStep (Job 0x69058A7D01F50F4499D98AFD8A6BFFFC : Step 1)
12 select @@microsoftversion SQLAgent - TSQL JobStep (Job 0x69058A7D01F50F4499D98AFD8A6BFFFC : Step 1)
13 select @@microsoftversion SQLAgent - TSQL JobStep (Job 0x69058A7D01F50F4499D98AFD8A6BFFFC : Step 1)
12 set quoted_identifier off SQLAgent - TSQL JobStep (Job 0x69058A7D01F50F4499D98AFD8A6BFFFC : Step 1)
13 set quoted_identifier off SQLAgent - TSQL JobStep (Job 0x69058A7D01F50F4499D98AFD8A6BFFFC : Step 1)
14 -- network protocol: LPC
set quoted_identifier on SQLAgent - TSQL JobStep (Job 0x69058A7D01F50F4499D98AFD8A6BFFFC : Step 1)
12 SELECT ISNULL(SUSER_SNAME(), SUSER_NAME()) SQLAgent - Update job activity
13 SELECT ISNULL(SUSER_SNAME(), SUSER_NAME()) SQLAgent - Update job activity
12 select convert(sysname, serverproperty(N'servernam SQLAgent - Update job activity
13 select convert(sysname, serverproperty(N'servernam SQLAgent - Update job activity
12 select @@microsoftversion SQLAgent - Update job activity
13 select @@microsoftversion SQLAgent - Update job activity
12 set quoted_identifier off SQLAgent - Update job activity
13 set quoted_identifier off SQLAgent - Update job activity
14 -- network protocol: LPC
set quoted_identifier on SQLAgent - Update job activity
15 NULL SQLAgent - Update job activity
12 DECLARE @nextScheduledRunDate DATETIME SET @nextSc SQLAgent - Update job activity
13 DECLARE @nextScheduledRunDate DATETIME SET @nextSc SQLAgent - Update job activity
(50 row(s) affected)
But it does not give any clue about what wrong is going on there.
Thanks
May 2, 2007 at 3:32 pm
When I run a job I get this as part of the trace:
EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = 0x2918E06B7641EC4385F600D3C40459C4, @step_id = 0, @sql_message_id = 0, @sql_severity = 0, @run_status = 1, @run_date = 20070502, @run_time =
152341, @run_duration = 1, @operator_id_emailed = 0, @operator_id_netsent = 0, @operator_id_paged = 0, @retries_attempted = 0, @message = N'The job succeeded. The Job was invoked by User awarren. The
last step to run was step 1 (Split).'
It runs just after this:
SQL:BatchCompleted UPDATE msdb.dbo.sysjobservers SET last_run_date = 20070502, last_run_time = 152757, last_run_outcome = 1, last_outcome_message = N'The job succeeded. The Job was invoked by User awarren. The last step to run was step 1 (Split).', last_run_duration = 0 WHERE (job_id = 0x2918E06B7641EC4385F600D3C40459C4) AND (server_id = 0) SQLAgent - Job Manager SYSTEM NT AUTHORITY\SYSTEM 0 3 0 0 2648 67 2007-05-02 15:27:57.613
I dont know if I'll have a chance to look more tonight. Theories:
May 3, 2007 at 8:42 am
Hmmm.... I don't have anything like "EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory..." in my profiler trace.
I tried to resart SQL Agent, but the problem is still the same. I also checked SQL Server log, it has huge number of entries, all of them for SQL Agent Log Type:
[298] SQLServer Error: 10061, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]
followed by
[165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
But as far as I know, this is not remote connection, SQL Server jobs are not remote - they are always local to server. By contrast, my queries which I am runnniing in my SSMS are remote to server !
I have a feeling that I have to change something in SQL Server Configuration manager or Surface Area configuration. But they both look fine. In the former one, all the triangles are green, including SQL Srever browser, and the latter shows that SQL Agent is running.
Any ideas ?
Thanks
May 3, 2007 at 3:59 pm
Check the SQL Agent connection info - using a domain account? Correct server name? Have you made any changes recently? I dont think its surface area config.
You might also try running sqlagent.eve from the command line withe the -v param, may get some other info to help.
August 24, 2007 at 9:15 am
i'm seeing something similar, but only on servers that are configured as distributors. in my user created jobs i checked off show history or something like that on each step. will see tomorrow
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply