Extended Events Event File Target filename long integer

23rd July 2017

This is a small post on what we’ve found out when trying to use the long integer value within the filename on the Event File Target. The Link is here.

The text that we were mostly interested in was this:

The integer value is calculated as the number of milliseconds between January 1, 1600 and the date and time the file is created.

We did some digging around and found that the long integer value was actually a year later, for clarity its 1601-01-01 not 1600-01-01. We used a throw away script to get to the bottom of the actual date and used Powershell to hook into the C# Library as we knew the DateTime Class supported ticks. Here’s the setup for SQL Server:

if exists ( select 1
 from sys.server_event_sessions
 where [name] = 'blocked_process_report'
 )
begin
 drop event session [blocked_process_report] on server
end
go

SELECT SYSDATETIME() as sys_datetime
GO

create event session [blocked_process_report] 
 on server
 add event sqlserver.blocked_process_report
 add target package0.event_file (set filename = N'C:\Temp\Extended Events\blocked_process_report.xel', max_file_size = 5) with (startup_state=off)
go

if not exists ( select 1
 from sys.dm_xe_sessions
 where [name] = 'blocked_process_report'
 )
begin
 alter event session [blocked_process_report] on server state = start
end
go

Now what we did is grab the SYSDATETIME and also the long integer value that the extended event session created. This was done in Powershell

$XeFileTick = 131452998789580000

$XeTickDate = [DateTime]::Parse('2017-07-23 17:11:18.9075180')

$XeTickDate.AddTicks(-$XeFileTick)

01 January 1601 00:59:59

As you can see the date was not what we were expecting!

The next part we had a little bit of trouble was with the T-SQL DATEADD Function. Based on the new information that we know know the date is 1601-01-01 we tried adding the Ticks from that base date. What we ended up getting was an error message:

DECLARE @SysDateTime AS DATETIME2 = '1601-01-01'

SELECT DATEADD(microsecond, 131452998789580000, @SysDateTime)

Msg 8115, Level 16, State 2, Line 6
Arithmetic overflow error converting expression to data type int.

We looked at the Documentation and it does state the number needs to be of a int type! The next bit we looked as was whether we could CAST a number to DATETIME2, and when we looked at the conversion table, an explicit conversion was not allowed. But what was allowed was an explicit conversion to DATETIME. All we had to do now was reduce the Tick number into a small enough number that we could cast to as datetime type. We did this in two steps, firstly using powershell to make sure the number reduced enough and then we did it in T-SQL:

Powershell
$TickFile = [DateTime]::Parse('2017-07-23 17:11:18.9075180').Ticks ~ 636364266789075180

$Tick16010101 = [DateTime]::Parse('1601-01-01').Ticks ~ 504911232000000000

$Tick19000101 = [DateTime]::Parse('1900-01-01').Ticks ~ 599266080000000000

$Ticks24Hours = ([DateTime]::Parse('1900-01-02')-[DateTime]::Parse('1900-01-01')).Ticks ~ 864000000000

$FileInteger = $TickFile - $Tick16010101

$DateDecimal = (($FileInteger + $Tick16010101) - $Tick19000101) / $Ticks24Hours

[DateTime]::Parse('1900-01-01').AddDays($DateDecimal)

# 23 July 2017 17:11:18

 

T-SQL
--Formula = (($FileInteger+ $Tick16010101) - $Tick19000101) / $Ticks24Hours (This is how many days there are after 1900-01-01)

SELECT CAST( (((131452998789580000 + 504911232000000000)) - 599266080000000000 ) / 864000000000.0 AS DATETIME) AS [file_created]

I hope you’ve found this information useful.