In this article I’ll describe a case where several customers already faced an issue where the new data update job in SLM 9 (only applicable for SLM 9 or later) didn’t run after manual termination on the SQL server. It also did not show any information regarding the data update job or steps. Besides this I believe it’s important to inform about this since customers and partners are asking on a frequent base whether it’s possible to stop the data update job or not and what kind of impact this could have on the application.
In case the new Data Update Job (SLM 9) has been terminated manually, the following issues can occur:
- The Data Update Job (DUJ) in Snow License Manager 9 is not working.
- It doesn't get triggered through the Snow Management and Configuration Center.
- Pressing the button "Start update job manually" doesn't work.
- Checking the logs in the applicable tables on SQL level don’t point to a root cause.
The root cause of this issue is that the SQL job has been terminated manually.
It’s NOT supported to manually terminate the Snow License Manager Data Update Job in SLM 9. The reason for this is due to major changes in the Data Update Job in SLM 9 compared to the one in SLM 8 and previous versions. Instead of one SQL job (License Manager Data Update) comprised of many serial steps, Data Update Job for Snow License Manager 9 uses non-scheduled jobs that run in parallel through a new job handler procedure.
Best practices & Solution
It’s recommended and has a lot of benefits to create regular back-ups of both Snow SQL databases and eventual application server snapshots to be able to fallback to an earlier state of the SQL server where the data update job was running fine. This could be daily (best practice), weekly or monthly back-ups which is dependent on customer policies or standards.
If the issue occurs for any reason it is recommended to first check logfiles which can point out to any root cause. This can be used for resolving the issue permanently and avoid the data update job to hang or run for a long time. The logs can be retrieved with the following SQL query:
SELECT * FROM msdb.dbo.sysjobhistory WHERE sql_severity > 0 order by run_date desc, run_time desc
Another way to check for any error in one of the steps, is to double click on "Job Activity Monitory" and see which step failed with red X marker beside the step name.
There will be at least 2 errors, one failing with main update date job, which will not give information about the exact error/step. However, look for the 2nd error in the job list (DataFlow) to see which step failed.
Also, check how the SLM 9 DUJ is running historically by running:
select * from SnowLicenseManager.dbo.tblInventoryDatabaseLog order by logdate desc -- see the oldSequenceNumber and NewSequenceNumber
select * from SnowLicenseManager.dbo.tblJobLogMetrics order by JobDate desc
Also, Check tblErrorlog for any reported error:
select * from SnowLicenseManager.dbo.tblErrorLog order by LogDate desc
select * from SnowLicenseManager.dbo.tblJobLogHistory
After all the basic troubleshooting steps and best practices have been followed and no resolution or root cause analyses have been identified, below SQL queries could help to start the Data Update Job for SLM 9 again after the manual termination which caused the initial issue. I strongly recommend to NOT terminate the Data Update Job in SLM 9 manually. If this happened for a reason or if you’re planning to do so with someone who has expertise on SQL level or either getting help from one of the Snow Support engineers, below queries can resolve the issue:
update inv.tblJobParallelStep set [Status] = 0 where Status= -1
SET LastJobStatus = 'Job finished successfully'
If the Data Update Job in SLM 9 hangs again or run for a longer time than usual, please do NOT terminate the Data Update Job 2.0 (SLM 9) again. In this case I strongly recommend creating a support ticket and ask for assistance before performing any further actions either on application server or SQL server level. The above queries are not applicable for all Data Update Job issues and should be checked case by case.