Data Collection & Daylight Saving Time

Question

Data Collection & Daylight Saving Time

R.P.Rozema

SSChampion

Points: 12307
More actions
October 26, 2015 at 9:53 am

#399227

Last weekend many of our severs had a failed job "collection_set_3_upload". The error that occured is:
"Violation of PRIMARY KEY constraint 'PK_active_sessions_and_requests'. Cannot insert duplicate key in object 'snapshots.active_sessions_and_requests'. The duplicate key value is (2824333, 2015-10-25 02:54:49.7630000 +02:00, 1)."
Last weekend we happened to go from summer time to winter time. i.e. the clock passed 02:00 - 3:00 two times during this night.
I.e. there is a bug in the Data Collector component that collects data for the Management Data Warehouse: it uses local time instead of UTC. I've created a Connect item to report it to Microsoft. I would appreciate it if you could vote it up, to have them fix this error. https://connect.microsoft.com/SQLServer/feedback/details/1946994
Meanwhile, how do you get your process running again? the job will no longer run because it will every 5 minutes keep on trying to upload the conflicting data for the 2nd 2:00 - 3:00 period. I've only found one solution: get rid of all data collected but not yet uploaded.
You do this by stopping the Collection set (in SSMS go to Object Explorer -> <the server you want to fix> -> Management -> Data Collection -> System Data Collection Sets. Right click "Query Statistics" and select "Stop Data Collection Set").
Then you delete the cached results from the sql server machine's harddisk. These cached results are in files located in a Temp folder on the sql machine itself, inside the AppData folder for the service account SQL Server Agent is running under. Usually it will be something like: c:\Users\<sql agent service account>\AppData\Local\Temp\. Inside this folder delete all files that have 'QueryActivity' in their name. You'll loose all data collected since the start of wintertime, but at least your data collection process will work again.
After this you can start the Collection set again by right clicking it and select "Start Data Collection Set". Every 5 minutes the data will be summarised and uploaded into your management data warehouse.

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

R.P.Rozema SSChampion Points: 12307 More actions · Answer 1

Happy to inform you that the same error still occurs... :hehe:

Posting Data Etiquette - Jeff Moden[/url]
Posting Performance Based Questions - Gail Shaw[/url]
Hidden RBAR - Jeff Moden[/url]
Cross Tabs and Pivots - Jeff Moden[/url]
Catch-all queries - Gail Shaw[/url]

If you don't have time to do it right, when will you have time to do it over?

elliotw84 Newbie Points: 0 More actions · Answer 2

This is because of the default setting inside Microsoft Windows working frameworks and other web servers that consequently set their clock back or forward one hour when Daylight Saving Time starts or closures. Log document information is gathered from 01:00-01:59, after which the clock is set back to 01:00. Log document information gathered in that one hour from now, Coursework Labs [/url]which would have been from 02:00-02:59, is added to the 01:00-01:59 territory. Since the information will show up out of succession, the second hour will be disposed of. At the point when the server sets its clock forward 60 minutes, the hour of 02:00 is skipped and reports will show what gives off an impression of being a hole in the information as a consequence of hopping from 01:59 to 03:00.