Cannot insert duplicate key in object 'dbo.RunningJobs'

I'm using SSRS 2005 on a Server 2003 machine.

Some of my reports run fine but others take a long time to run and sometimes fail with an http error 503.

After a report errror happens I find entries in the event log that says: cannot open a connection to the report server

When I look at the log files for Reporting services I see an error caused by a primary key violation on the Running Jobs Table:

Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'

Does anyone know why do I get such errors?

Bellow is the error message in the logs:

w3wp!library!6!21/08/2006-09:30:22:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing., ;
Info: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. > System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'.
The statement has been terminated.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at Microsoft.ReportingServices.Library.RunningJobsDb.AddRunningJobs(Hashtable runningJobs)

This error message is not really a critical one. For now as a workaround try to restart the SQL Server Reporting Services Web Service in IIS (e.g. by using iisreset).

When the RS Web service restarts and there is no immediate load on the service during the actual service startup, it will work just fine after the restart.

-- Robert

|||

The error is critical for my system because reports are crashing and running very slow.

Is this a known bug? Why would RServices generate primary key conflicts on its own tables? I don't have any subscriptions at all on the server. It just runs 2 or 3 on-demand reports. Anyone has had this problem before? It would be good to at least have a better solution than the workaround of restarting the service.

|||

Yes - I get this problem intermittently. It seems to happen when we are processing some of the large, resource intense reports (reports with 20+ subreports and complex SQL). This problem occurs in RS2005 - never experienced it in RS2003.

It does not cleanly correlate to subscriptions running either, as far as I can tell.

I have found no workaround and occasionally our reports do fail.

Any insight would be appreciated, please?

Matt

|||

There is a bug right now that produces PK violation error when your app pool is started under load. You can verify this by looking at the log file, and notice that there are multiples of the same entry. The work around is to not start under load. Notice sometimes IIS decides to recycle worker process, so you may get this error in the middle of your tests.

We are currently considering putting the fix in SP2. It is very likely to go through.

|||The problem I have is that I've got thousands of reports that hit our servers weekly and when this started occurring, I have to drop this server out of our externally load balanced cluster so that no clients can access it, then I have to recycle the app pool and then all of my customers reports fail that were in process. Then I put it back in the cluster so that it can be accessed again. Further, it doesn't fix the problem. Just today I did this procedure and it wenr right along dropping reports.

Is there a hotfix available for this outside of SP2? We've got some pretty serious impact with this bug.|||Is there any solution to this issue? We too are experiencing this. A hotfix?|||

Hi Mattyb83,

The issue regarding this:

Violation of PRIMARY KEY constraint 'PK_RunningJobs'. Cannot insert duplicate key in object 'dbo.RunningJobs'.

is fixed in SQL 2005 Reporting Services SP2.

Before SP2 was shipped the workaround used by many people was iisreset.

Mihaela

|||

Hi Mihaela,

I'm facing such issue as well on my SQL 2005 server. As you have mentioned, it is fixed in SQL 2005 SP2. Is it confirmed? I did browse through the fixes but did not find any fix related to this issue. Appreciate if you could help verify.

Thanks

Barry