You may receive the following error when running a scheduled data refresh on a PowerPivot workbook in SharePoint 2013 running PowerPivot for SharePoint:
Could not find the schedule for this work item
This error occurs when the workbook is checked out when the scheduled data refresh runs. Scheduled data refresh for workbooks that are checked out is not supported. The following article has further details: http://msdn.microsoft.com/en-us/library/ee210651.aspx It states that the workbook must be checked in at the time the refresh operation is finished. If the file is checked out to another user, the refreshed data will be thrown out.
I have seen several cases where the background\parts of Excel workbooks are turning black:
Here is an example in the browser:
Here is an example from the PowerPivot Gallery:
From my experience the W3WP.exe is growing in size and this causes issues with performance. Many times this is the result of a corrupt workbook. I like to think of it this way; imagine opening a corrupt workbook on your machine in the Excel rich client. If that workbook hangs, you will notice the Excel.exe process slowly growing in size when looking at Task Manager. You will also experience performance issues on your machine. The only way to close Excel is to kill (End Process) the Excel.exe in Task Manager. If this happen on the server, the ExcelService.asmx will be trying to open this under the W3WP.exe. You will see that the W3WP.exe is slowly growing in size until it affects the server performance. Sometimes I am lucky and can find a corrupt workbook in the ULS logs, but this can be very difficult because performance issues can occur many hours after the workbook was opened.
05/20/2014 18:51:00.10 w3wp.exe (0x1A1C) 0x279C Excel Services Application Excel Calculation Services 2021 Information Failed to load 'http://SharePoint.Contoso.com/sites/Documents/Services/Operations/Workbook.xlsx' with error: 'The workbook is corrupt.'. [Session: 1.V22.891OcC38Mp/PRDHw7Ze1tF90.5.en-US5.en-US73.+0360#0000-11-00-01T02:00:00:0000#+0000#0000-03-00-02T02:00:00:0000#-006036.b06179e8-2e64-440c-99a6-ca7f9f8d93161.N User: User@contoso.com] 3a9bffee-c087-48fb-b34e-8d968bdc6f24
If you find this corrupt workbook (note: there could be more than one) and remove/rebuild it, the issue will go away. If you cannot locate this workbook (again, you may need to dig through massive ULS logs), you will need to Recycle the Excel Services Application Pool.
If you recycle the application pool daily (after hours), a growing W3WP.exe will be reset and you should be able to avoid see performance issues.
Here are instructions on how to recycle the application pool running Excel Services.
In IIS, you will need to expand “SharePoint Web Services” (make sure you are using “Content View”). You will need to click on each GUID under “SharePoint Web Services” until you see the “ExcelService.asmx”, once you find this (special note, if you have multiple “Excel Services Service Applications”, there will be multiple GUIDs with the ExcelServices.asmx under it, you will need to either delete one of the “Excel Services Service Applications” or set both to Recycle), click on “Advanced Settings…”. Here you will see the “Application Pool” once you find the GUID (in this case 9bf574c7cfc04e7e9a8595ec2e887fb8), you will know which Application Pool to Recycle.
Go to Application Pools > Right Click on 9bf574c7cfc04e7e9a8595ec2e887fb8 > Advanced Settings…
You can set the time you want to Recycle under Recycling > Specific Times
Scenario:
You get the following error while running a scheduled data refresh on a PowerPivot workbook:
Problem:
The "Require Check Out" option inside of the Document Library Settings > Versioning Settings of any given document library is likely set to "Yes":
Resolution:
PowerPivot does not attempt to check out documents when it makes its modification, therefore you will need to turn this "Require Check Out" option to "No" for scheduled data refresh to work on the document library in question.