solving sql server problems for millions of dbas and developers since 2006




Learn more about SQL Server tools



















sysjobhistory table in msdb is empty

Asked 10/25/2011 7:51:35 PM by maya

Hello,

i'm having a problem with SQL server 2008, sysjobhistory is not being populated, it's empty.

what could be the possible reason?

SQL server agent is running and the job finished running successfully.

pls help.

thanks

 



Answered 10/26/2011 9:19:25 AM by Scott C (0) vote answer is useful vote answer is NOT useful

Is anything showing up in the SQL Server Agent Error Logs related to this job?

Are the sysjobhistory records being created, but are being deleted too quickly?  Check SQL Agent properties in Management Studio Object Browser.  Are there any strange settings on the History tab, such as limiting the size of the job history log to 1 record?

I've never seen SQL Agent fail to create sysjobhistory records, but if that is the case the most likely cause I can think of would be permission issues.  Has the service account for SQL Agent and/or msdb user permissions been changed so that SQL Agent does not have write access on sysjobhistory?  Another table that is used is sysjobactivity, are there any records in it?  If so, what is in the job_history_id column?  If there are reasonable values for job_history_id then the sysjobhistory records must have been created at some point.

SELECT  j.name, a.*
FROM dbo.sysjobactivity a
LEFT JOIN dbo.sysjobs j ON a.job_id = j.job_id

 


Answered 10/27/2011 5:57:05 PM by maya (0) vote answer is useful vote answer is NOT useful

Thank you for your reply.

The following message shows in the SQL Server Agent Error Logs:

[298] SQLServer Error: 515, Cannot insert the value NULL into column 'instance_id', table 'msdb.dbo.sysjobhistory'; column does not allow nulls. INSERT fails. [SQLSTATE 23000] 

The History tab looks ok... no strange settings and the service account for SQL Agent and/or msdb user permissions hasn't been changed.

sysjobactivity is populated but job_history_id column is null.

I would be grateful if anyone could offer any help with this.

Thanks




 

 

 


Answered 10/28/2011 8:05:50 AM by Scott C (0) vote answer is useful vote answer is NOT useful

That's a pretty strange error for that table.  The sysjobhistory.instance_id column is supposed to be an identity column, at least on the SQL 2000, 2005, and 2008 R2 servers I checked.  If you try inserting a NULL into an identity column, with or without using SET IDENTITY_INSERT, you'll get a different error.

The error message you cite is from trying to insert a NULL into an INT NOT NULL column that is not an IDENTITY column.

Is instance_id an identity column?  Has someone been altering the definition of system tables on this server?

SELECT  name
FROM msdb.sys.columns
WHERE object_id = OBJECT_ID('dbo.sysjobhistory') AND is_identity = 1

If instance_id is an identity column, but it's not working normally, this might give you a clue:

USE msdb
DBCC CHECKIDENT ('dbo.sysjobhistory', NORESEED)
/*
 If the IDENTITY field has been set normally by an INSERT:
Checking identity information: current identity value '9999', current column value '9999'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  If sysjobhistory has never been written to:
Checking identity information: current identity value 'NULL', current column value 'NULL'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
 If there is no IDENTITY column in the table:
Msg 7997, Level 16, State 1, Line 2
'sysjobhistory' does not contain an identity column.
*/

This is a wild guess because I've never seen a non-functional IDENTITY, but it might fix the problem:

TRUNCATE TABLE dbo.sysjobhistory
DBCC CHECKIDENT ('dbo.sysjobhistory', RESEED, 0)

Answered 10/28/2011 10:25:47 AM by maya (0) vote answer is useful vote answer is NOT useful

Problem is solved.

Thank you for your help Scott.


Answered 8/8/2013 3:58:45 PM by Kishor (0) vote answer is useful vote answer is NOT useful

Thank You Guys.., i also had similar problem..

I ran the queries and it solved my issue.

 

Great job.!!



Post an Answer

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email   Notify for updates
Comments
*Enter Code refresh code


 
Sponsor Information






Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | today's tip | first timer?
Some names and products listed are the registered trademarks of their respective owners.