Exetended Events Session timestamp value

Question

I'm using Extended Events to track events on a SQL Server 2008 R2 Standard installed on Windows 2008 R2. The value of timestamp returned is 23 days in the past and time is neither zulu time nor current system time. Windows system time, SQL Server time all are current and are in agreement. Had created the same event session on another Server (SQL2K8 on Win2K8) as a test and timesatmp returns the current time on that server. Any clue, anyone ?

Answer 1

Could you share what you see (removing any sensitive info)?

---

Arthur My Blog

Answer 2

Thanks for reply ArthurZ, The following XML for eample was generated @ 1:04PM, November 1 2010 US EST Windows and SQL Server time.

<

 

 

event name="sql_statement_starting" package="sqlserver" id="59" version="1" timestamp="2010-09-27T13:48:17.471Z">

Answer 3

The time is in UTC format, what is strange is 471Z.

Check Locale of the target and source (if involved) compare the two.

---

Arthur My Blog

Answer 4

The timestamp on an event is when the event fired inside the engine and is in UTC/Zulu/GMT time.  It looks to me like you are querying older events in the target.  If it is a in memory target like the ring_buffer stop the event session and start it again to clear the target then run what ever test you want to run to capture the events for.  Then requery the target and the timestamps will be offset from current time by the UTC Offset.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 5

Right before I posted the XML above, I stopped the event session, deleted the .xel and .xem files (my target is asynchronous file), then restarted the event. On September 27 2010, I had not even implemented Extended Events session on the server in question, so I am sure I am not querying older events. Please note that I have sucessfully implemented a similar extended event session on another server. The only difference between the two servers is that the one working sucessfully is on a SQL Server 2008 Standard, 32-bit running on Windows 2008 Enterprise; while the server I'm having issues with is a SQL Server 2008 R2 Standard, 32-bit on Windows 2008 R2. Johathan, where in the Windows system does the Extended Events engine pulls its date and time ?

And BTW the locale of the target and source is United States and system time zone (UTC-05:00) Eastern Time (US & Canada).

 

Answer 6

Extended Events uses the same mechanism as the SQL Server Database Engine to get the date and time.  Something has to be set incorrectly on the server having the problem.  Does SELECT GETDATE() and SELECT GETUTCDATE() return the appropriate values inside of SQL Server on that machine?  If it does, change your event session to use a ring_buffer target as well and query that target to see if you still get incorrect timestamps.  If you get September timestamps and GETDATE() and GETUTCDATE() return November 1 datetime values, you've got me beat, and I'll email the dev's at Microsoft that wrote Extended Events in the product to ask them for some ideas where to look.  I have never seen it behave in the manner that you are describing for newly fired events.

---

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.twitter.com/SQLSarg
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

Answer 7

Since your last posting I have had the system administrator in charge of this remote server do the following. 1.) Check BIOS time and ensure it is correct; and 2.) Apply up to date patches on the server (my check had shown patches had not been applied for some time). After this was done, I modified the Extended Event session to use a ring buffer target; and voila, the correct date and zulu time were being reported. Next I switched back to using a asynchronous file target and also have correct date and time. Thank you for your pointers in this case Jonathan and your ramblings in general.

Answer 8

Just a note here, there is a bug that causes timestamp values to get out of sync that is being addressed in SQL Server 2008 R2 SP1 and I'd expect in a CU for SQL Server 2008 SP2.  You can work around this problem by adding the sqlserver.collect_system_time action to the events you are collecting which will have the correct datetime even when the event timestamp is off.

https://connect.microsoft.com/SQLServer/feedback/details/649362/extended-event-session-system-health-diferent-timestamp-datetime-and-datetime-on-server-getdate

 

---

Jonathan Kehayias | Senior Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Feel free to contact me through My Blog or Twitter
Please click the Mark as Answer button if a post solves your problem!