Deleted timesheet resists in Reporting DB

[Barbara - Austria]

On the one hand I hope for all of you that you can’t answer my question since
you never ran into it. On the other hand I hope that anyone has an idea what
I could try:

I have the situation that a timesheet was created at some day. A few days
later this timesheet was deleted. We don’t know exactly when that was done,
but it could be during a timeframe when disk space was full.

The issue is now, that we have the old timesheet data in Reporting DB, but
no information for that timesheetUID in Published DB. When resource create a
new timesheet for that period, there is an error in the queue for Reporting
(Timesheet*). This new timesheet can be found in Published DB with its new
UID, but not in Reporting DB since reporting job fails.

There are no timesheet entries for that old timesheet UID in any
MSP_Timesheet* tables in Published DB. So it seems that deleting went through
successfully in Published DB, but was not updating Published DB.

Error in ‘Manage Queue Jobs‘:
<?xml version="1.0" encoding="utf-16"?>
<errinfo>
<general>
<class name="Queue">
<error id="26000" name="GeneralQueueJobFailed"
uid="6369dc01-c860-40b8-8d60-01eb833e5300"
JobUID="0be13321-74dc-446f-9787-584ca281c98d" ComputerName="xxxxxxx"
GroupType="ReportingTimesheetSave" MessageType="ReportTimesheetSaveMessageEx"
MessageId="1" Stage="" />
</class>
</general>
</errinfo>
ULS tells that there was an System.Data.SqlClient.SqlError because of an
UNIQUE KEY constraint 'UK_MSP_Timesheet'. No duplicate key can be inserted
into 'dbo.MSP_Timesheet' Object (This is a translation)


Attempts to correct:
- Delete, modify, re-create timesheet for this resource and period several
times.
- Create a new timesheet for that resource and period. Force a rebuild of
Reporting DB by backing up and restore of Enterprise fields.
Result: Old Timesheet UID still in Reporting DB. Timesheet UID and actuals
do not match between Reporting DB and Published DB.
- Delete timesheet for that resource and period. Force a rebuild of
Reporting DB by backup and restore of Enterprise fields.
Result: Old Timesheet UID still in Reporting DB.
- Delete all timesheets for that period with Server Settings – Delete
Enterprise Objects – Timesheets. Force a rebuild of Reporting DB by backing
up and restore of Enterprise fields.
Result: Old Timesheet UID still in Reporting DB.

Has anyone an idea how can we get rid of the old timesheet information in
Reporting DB? Where does that information come from when forcing a rebuild of
Reporting DB since I can’t find this old TimesheetUID in Timesheet tables in
Published DB? We don’t want to edit reporting DB since wrong situation will
be back whenever we force another rebuild of Reporting DB in future.

Any ideas are welcome!
Thanks in advance
Barbara

 

[Gary L. Chefetz]

Barbara:

Have you tried forcing a rebuild of the Reporting DB? I don't believe that
there's a mechanism for synching other than a total refresh. Take an
administrative backup of your custom fields, and then restore them. This
will cause the entire RDB to be refreshed.

 

[Barbara - Austria]

Hi Gary,

thanks for that idea. I did that more than 3 times with different situations
(results below in my first post):
- Create a new timesheet for that resource and period. Force a rebuild of
Reporting DB by backing up and restore of Enterprise fields.
- Delete timesheet for that resource and period. Force a rebuild of
Reporting DB by backup and restore of Enterprise fields.
- Delete all timesheets for that period with Server Settings – Delete
Enterprise Objects – Timesheets. Force a rebuild of Reporting DB by backing
up and restore of Enterprise fields.

I am really wondering where that information comes from when forcing a
rebuild of
Reporting DB since I can’t find this old TimesheetUID in Timesheet tables in
Published DB? Really interesting.

Regards
Barbara

 

[Gary L. Chefetz]

Barbara:

Sorry, I should have read your previous message more closely. All of the
data comes from the Published database, but if the RDB refresh is failing it
doesn't matter what's in the Published DB. The last strategy is to clear the
RDB entirely. Take a backup of your database just in case, and then run the
stored procedure in the RDB called: MSP_RDB_DeleteAllData

After you clear out the RDB, you should be able to refresh it.

 

[Barbara - Austria]

Hi Gary,

thanks for taking time. I understand that you didn't read my first message
until the very end. It was really a long one! But I have tried a lot before
asking.

The refresh job is not failing. Moreover I can see that information for the
old timesheet is deleted in the first steps, but re-created later on. This
information must sit in some table in Published DB which name doesn't
include 'timesheet'.
I will give MSP_RDB_DeleteAllData a try tomorrow or the day after. For that
client I need to be on site, no remote access possible.

Regards
Barbara

 

[Gary L. Chefetz]

Barbara:

After you clear the RDB and run the refresh, watch the queue carefully. If
any of the many jobs that this spawns fails, it will likely point to the
source of the corruption. I had to do this recently on one of my 2010 lab
systems and, in my case, the problem resided in a single project plan. The
failure prevented the RDB from refreshing correctly and prevented the cubes
from building.

 

[Barbara - Austria]

Hi Gary,

when I was running the rebuild, there were no errors at all. Since I am
telling everyone that it can take some hours and that there are many jobs, I
watched it carefully ;-). I also checked ULS and Eventlog - nothing special
in there.

I am wondering what I will see when running the rebuild after clearing RDB.
I will let you know! Thanks a lot!

Barbara