none
How to delete Timesheets of a ghost project (Project Server 2010) RRS feed

  • Question

  • I have a ghost project in the Reporting DB. I cannot delete it because there are some Timesheets against it. I found this SQL Query but it returns a lot of errors.

    Could a SQL expert help me to correct this query?

    Thanks

    ============================

    DELETE MSP_TIMESHEET_ACTUALswhere ts_line_UID IN(SELECT DISTINCT A.TS_LINE_UID
    FROM MSP_TIMESHEET_ACTIONS A
    <o:p></o:p>

    INNERJOIN MSP_TIMESHEET_LINES L ON A.TS_LINE_UID = L.TS_LINE_UID WHERE A.TS_ACTION_ENUM= 1 AND L.PROJ_UID= 'FB1-FFDDFD54F48B')<o:p></o:p>

    delete MSP_TIMESHEET_LINESwhere ts_line_UID IN(SELECT DISTINCT A.TS_LINE_UID
    FROM MSP_TIMESHEET_ACTIONS A
    <o:p></o:p>

    INNERJOIN MSP_TIMESHEET_LINES L ON A.TS_LINE_UID = L.TS_LINE_UID WHERE A.TS_ACTION_ENUM= 1 AND L.PROJ_UID= 'FB1-FFDDFD54F48B')<o:p></o:p>


    Monday, February 11, 2013 10:35 AM

Answers

  • So is the job Failed or is it "Waiting for Process (Sleeping)", because those are very different. The refresh can take a while and while it is going it will most often say "waiting for process(sleeping)" Be patient. It will come around.

    Also, as Barbara explained, the fact that this timesheet data is still in the RDB is by design. These are not 'orphaned' timesheets and this is not a 'ghost' project in the general use of those terms. This data is there intentionally since there were timesheets submitted against it. If deleting a project meant deleting all the timesheets Project Server would get kicked out of every government agency and most publicly traded corporation because these organizations have strong requirements that once a timesheet user says that work was actually done that this data should never be deleted.

    You may need to figure out a way to filter out these types of projects from your reporting.

    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    • Marked as answer by WLID1966 Monday, February 11, 2013 10:35 PM
    Monday, February 11, 2013 7:29 PM
    Moderator
  • Sorry, I missed your answer. RDB refresh means, that everything except timesheet tables are emptied.
    Add Sucess as status in queue and look, if it is really not working any longer. It takes hours, and a lot happens in the backgrond. So fast, that you don't see in queue in progress. If everything went through, you can just restart once more. In one case, I had to restore 3 times to get my RDB back.

    I cross my fingers! I don't think that you will need MS. I will check this threat in a few hours again

    Barbara

    • Marked as answer by WLID1966 Monday, February 11, 2013 10:34 PM
    Monday, February 11, 2013 5:03 PM
    Moderator
  • Hi,

    the longest I saw was 5 hours. But it depends, as always. I assume, that HW for your test system is less powerful than your PROD environment?

    However, if you need to do so, just think about starting in the evening or the weekend. Outside of normal working hours, it will be faster.

    Regards
    Barbara

    • Marked as answer by WLID1966 Wednesday, February 13, 2013 9:42 AM
    Tuesday, February 12, 2013 9:43 AM
    Moderator
  • this is because it is not just doing a backup type operation. It is actually causing what amounts to most of a full publish operation on every project in your database. When you do a normal publish it copies the data from draft to published DB. This is pretty fast since their schema are largely the same. It then takes the data from published and transforms it to the RDB schema. This is a big operation since the schema are so different. 3 hours is not out of line since it is doing a massive denormalization effort on all the tasks and assignments in the database.

    an RDB refresh is normally something I would never recommend during 'normal' business hours on your production system. It is an evening or weekend type job.


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    • Marked as answer by WLID1966 Wednesday, February 13, 2013 9:42 AM
    Tuesday, February 12, 2013 3:59 PM
    Moderator

All replies

  • Hi WLID,

    I strictly recommend NOT to use above query. Try to delete your project by Server Settings - Delete Enterprise Objects. If you get an error message, due to existing timesheet, please tell your current patch level. I can remember, that there was a fix for in the past, but I would need to look for it. Please tell more about your current issue, perhaps someone has an idea how to solve without modifying Published DB directly.

    However, if you are really shure, that you want to modify your published DB (I would never do so), use the corrected query:

    DELETE MSP_TIMESHEET_ACTUALs where ts_line_UID 
     IN (SELECT DISTINCT  A.TS_LINE_UID
    		FROM MSP_TIMESHEET_ACTIONS  A 
    		INNER JOIN MSP_TIMESHEET_LINES L ON A.TS_LINE_UID  = L.TS_LINE_UID 
    			WHERE A.TS_ACTION_ENUM= 1 
    			AND L.PROJ_UID= '<complete UID>')
    
    delete  MSP_TIMESHEET_LINES where ts_line_UID 
     IN (SELECT DISTINCT A.TS_LINE_UID
    	FROM MSP_TIMESHEET_ACTIONS A
    	INNER JOIN MSP_TIMESHEET_LINES L ON A.TS_LINE_UID  = L.TS_LINE_UID 
    		WHERE A.TS_ACTION_ENUM= 1 
    		AND L.PROJ_UID= '<complete UID>')
    

    Please rethink your approach :-).

    Regards
    Barbara

    Monday, February 11, 2013 11:32 AM
    Moderator
  • Hi Barbara,

    I cannot delete this ghost project by Server Settings - Delete Enterprise Objects because it is not listed there. It is located only in the Reporting DB: I can see it there, and it pollutes OLAP cube reports...

    Monday, February 11, 2013 11:47 AM
  • Hi,

    OK, you see it in OLAP cube reports: are you referring to timesheet based reports? This is by design. Timesheets are facts, happened in the passed and ensure, that no important data - as it may be used for some billing process - is deleted. If this is the case, the only supported method is to deleted timesheet lines of affected timesheets.

    Only timesheet based:
    SELECT     ProjectName
    FROM         dbo.MSP_TimesheetProject_OlapView
    WHERE     (ProjectUID = '<complete UID>')

    If you see these projects in reports not related to timesheets, you can try to refresh RDB.

    Non timesheet based:

    SELECT     ProjectName
    FROM         dbo.MSP_EpmProject_UserView
    where projectUID = '<complete UID>'

    Regards
    Barbara


    Monday, February 11, 2013 11:56 AM
    Moderator
  • If the ghost project resides in the Reporting DB, in which DB reside the "orphan" timesheets?

    Thanks

    Monday, February 11, 2013 12:37 PM
  • Sometimes a call was easier :).

    That's what I would like to find out, what happened in your case. My assumption:

    Your project was deleted as project in published and reporting successfully. But it is still available with project_UID in both databases with name in timesheet tables. With queries of last post on RDB, you can verify, if my assumption is correct. Not available in MSP_EpmProject_UserView, but still existing for reporting purposes in MSP_TimesheetProject_OlapView.

    Can you verify?
    Barbara

    Monday, February 11, 2013 12:54 PM
    Moderator
  • I tried to retreive this ghost project:

    dbo.MSP_EpmProject : it is there :      59c98b63-57e6-4e25-bb4d-7ec292038fa0 (the ghost project)
    dbo.MSP_EpmPorject_UserView :        59c98b63-57e6-4e25-bb4d-7ec292038fa0
    dbo.MSP_EpmAssignment_UserView : 59c98b63-57e6-4e25-bb4d-7ec292038fa0  (25 assignments)
    dbo.MSP_EpmTask_UserView :            59c98b63-57e6-4e25-bb4d-7ec292038fa0  (21 tasks)
    dbo.MSP_TimesheetLine_UserView :    Nothing for this ProjectUID!
    dbo.MSP_TimesheetProject_OlapView: 59c98b63-57e6-4e25-bb4d-7ec292038fa0 (one project)

    I'll try to refresh the RDB

    Monday, February 11, 2013 2:20 PM
  • Good luck!

    Since data is still available in Project, Task and Assignment views, your initial query will not help. I think you have already done RDB refresh - patience!

    Regards
    Barbara

    Monday, February 11, 2013 2:28 PM
    Moderator
  • ;-(

    To refresh the RDB I ran a backup of custom fields objects, and then a restore. But the restore process failed : "Waiting for process (Sleeping)" and %complete:0%...

    Now my RDB is empty : only one "Timesheet administrative operation" project in dbo.MSP_EpmProject table.

     and the Excel Services pivottables are empty too.

    I think  I'll need MS support...


    • Edited by WLID1966 Monday, February 11, 2013 3:48 PM
    Monday, February 11, 2013 3:47 PM
  • Sorry, I missed your answer. RDB refresh means, that everything except timesheet tables are emptied.
    Add Sucess as status in queue and look, if it is really not working any longer. It takes hours, and a lot happens in the backgrond. So fast, that you don't see in queue in progress. If everything went through, you can just restart once more. In one case, I had to restore 3 times to get my RDB back.

    I cross my fingers! I don't think that you will need MS. I will check this threat in a few hours again

    Barbara

    • Marked as answer by WLID1966 Monday, February 11, 2013 10:34 PM
    Monday, February 11, 2013 5:03 PM
    Moderator
  • So is the job Failed or is it "Waiting for Process (Sleeping)", because those are very different. The refresh can take a while and while it is going it will most often say "waiting for process(sleeping)" Be patient. It will come around.

    Also, as Barbara explained, the fact that this timesheet data is still in the RDB is by design. These are not 'orphaned' timesheets and this is not a 'ghost' project in the general use of those terms. This data is there intentionally since there were timesheets submitted against it. If deleting a project meant deleting all the timesheets Project Server would get kicked out of every government agency and most publicly traded corporation because these organizations have strong requirements that once a timesheet user says that work was actually done that this data should never be deleted.

    You may need to figure out a way to filter out these types of projects from your reporting.

    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    • Marked as answer by WLID1966 Monday, February 11, 2013 10:35 PM
    Monday, February 11, 2013 7:29 PM
    Moderator
  • Thanks a lot Barbara and Brian,

    Obviously, I was not patient enough. I launched a new restore and after 3 hours my (small) RDB was rebuilt. My Excel Reports are working again. And the ghost is gone!

    That was a very instructive experiment!

    Thanks again



    • Edited by WLID1966 Monday, February 11, 2013 10:35 PM
    Monday, February 11, 2013 10:34 PM
  • Hi WLID,

    thanks for your update! Good to read that it is solved now.

    Hi for everyone reading,

    please always think carefully about modifying any DB directly. I strongly recommend never doing so. You will leave your DBs in an unsupported state which may cause you troubles later on.

    I am aware that some posts are available with this suggestion, but be very careful. I only see 2 exception when you may or have to do so:

    Regards
    Barbara

    Tuesday, February 12, 2013 6:17 AM
    Moderator
  • Hi,

    Just one more question:

    My Reporting DB is a very small one (for test) and it needed 3h to "refresh" (to restore, a lot more than to backup)

    What would be the duration of a restore for an industiral Reporting DB : 300 projects with 500 tasks each?

    Do you have any experience of that?

    Thanks

    Tuesday, February 12, 2013 9:26 AM
  • Hi,

    the longest I saw was 5 hours. But it depends, as always. I assume, that HW for your test system is less powerful than your PROD environment?

    However, if you need to do so, just think about starting in the evening or the weekend. Outside of normal working hours, it will be faster.

    Regards
    Barbara

    • Marked as answer by WLID1966 Wednesday, February 13, 2013 9:42 AM
    Tuesday, February 12, 2013 9:43 AM
    Moderator
  • this is because it is not just doing a backup type operation. It is actually causing what amounts to most of a full publish operation on every project in your database. When you do a normal publish it copies the data from draft to published DB. This is pretty fast since their schema are largely the same. It then takes the data from published and transforms it to the RDB schema. This is a big operation since the schema are so different. 3 hours is not out of line since it is doing a massive denormalization effort on all the tasks and assignments in the database.

    an RDB refresh is normally something I would never recommend during 'normal' business hours on your production system. It is an evening or weekend type job.


    Brian Kennemer - Project MVP
    DeltaBahn Senior Architect
    endlessly obsessing about Project Server…so that you don’t have to.
    Blog | Twitter | LinkedIn

    • Marked as answer by WLID1966 Wednesday, February 13, 2013 9:42 AM
    Tuesday, February 12, 2013 3:59 PM
    Moderator