I was discussing an issue with the SCSM Data Warehouse with a fellow consultant a week or so ago and again with MVP Steve Beaumont only last Friday. The issues was to do with execution of the Data Warehouse Extract jobs after installing Update Rollup 5 for SCSM 2012 R2. At this point, I hadn’t seen this particular issue, but over the course of the next week or so, I found similar behaviour in 3 other environments.
What I observed was that the Extract jobs no longer ran. They didn’t fail, as the jobs never got a FAILED status in the console. More-over, they never actually started. However in the event log, the following errors were raised. Notice the date format of the LastModifiedDate. This is in a localised date format (i.e. UK, since each of these environments where I had observed the issue was in the UK)
Following on from this, I decided to run SQL Server traces with the help of Mihai’s great DW troubleshooting article to get a better look at the possible cause. What I did see was that when the infra.GetBatchStartTimeEndTimeDetails stored procedure was called, the same localised date time shown in the event log was being passed. The data for this I believe is coming from the infra.EventLogging table as shown below:
The date is then converted from the string format from the parameter to a valid datetime data type, and this failed with an out of range error due to trying the cast ’20’ as a month (i.e. in a U.S./SQL datetime format, the first data element is month rather than day as it is in the UK, and other parts of the world)
Today (i.e. 02/03/2015 in the UK, or 03/02/2015 in the U.S.) I tested this again by manually executing the Extract jobs. This then updates the infra.EventLogging table with a new data with today’s date in there. Following this, the jobs then run to schedule as normal. I am guessing that these will run until the 13th March, and will then begin to fail again.
The Product Group are aware of the issue and have been investigating fix for the issue. In the meantime, MSFT have advised the following:
For now, the workaround is to change the datetime format to a US format (or any format that has mm/dd/yyyy like below) in Region settings in Control Panel, on the DW Server and restart the monitoring host.. Also verify the schedules (Get-SCDWJobSchedule) if they are still active and not disabled.
Then the DW jobs should start running.
*****DICLAIMER – I CANNOT PERSONALLY VALIDATE THAT THIS WORKAROUND CAN BE IMPLEMENTED WITHOUT FURTHER NEGATIVE IMPACT ON YOUR ENVIRONMENT. *****
*****IF YOU IMPLEMENT THIS WORKAROUND, YOU DO SO AT YOUR OWN RISK*****
I personally will be waiting until a hotfix has been made available before applying UR5 to any other customer environments.
Microsoft have now release a hotfix for this issue, which is available here