Read-DbaXEFile - Test-Path RemoteTargetFile False Without Wildcard

[mattcargile]

Verified issue does not already exist?

Yes

What error did you receive?

PSMessageDetails      : 
Exception             : System.Exception: 
\\ServerName\C$\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log\XeSessionName 
                        cannot be accessed from LocalServerName. Does domain\user have access?
TargetObject          : 
CategoryInfo          : NotSpecified: (:) [], Exception
FullyQualifiedErrorId : dbatools_Read-DbaXEFile
ErrorDetails          : 
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at Stop-Function, 
                 C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.11\allcommands.ps1: line 91469
                        at Read-DbaXEFile<Process>, 
                 C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.11\allcommands.ps1: line 59945
                        at Select-DefaultView<Process>, 
                 C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.11\allcommands.ps1: line 88935
                        at Get-DbaXESession<Process>, 
                 C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.11\allcommands.ps1: line 39354
                        at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : {}

Steps to Reproduce

Get-DbaXESession $SqlInstance -Session $XeSessionName -EnableException | Read-DbaXEFile -EnableException

Are you running the latest release?

Yes

Other details or mentions

I think the issue was introduced in https://github.com/sqlcollaborative/dbatools/commit/a69925a68f03e26918ff7b3628f832b481cb090c# with the removal of lines 101 to 109.

Now the below line would return $false on all (Get-DbaXESession).RemoteTargetFiles.

https://github.com/sqlcollaborative/dbatools/blob/fb2f08f69524af3f85fb0b6617a3b1d3419a41cd/functions/Read-DbaXEFile.ps1#L90

Considering the aforementioned commit, I think the the other Read* cmdlets may be impacted.

What PowerShell host was used when producing this error

PowerShell Core (pwsh.exe), Windows PowerShell (powershell.exe), VS Code (integrated terminal)

PowerShell Host Version

Name                           Value
----                           -----
PSVersion                      7.1.4
PSEdition                      Core
GitCommitId                    7.1.4
OS                             Microsoft Windows 10.0.18363
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

Name                           Value     
----                           -----                               
PSVersion                      5.1.18362.1171         
PSEdition                      Desktop                
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0...}
BuildVersion                   10.0.18362.1171        
CLRVersion                     4.0.30319.42000        
WSManStackVersion              3.0                    
PSRemotingProtocolVersion      2.3                    
SerializationVersion           1.1.0.1                

SQL Server Edition and Build number

Microsoft SQL Server 2017 (RTM-CU24) (KB5001228) - 14.0.3391.2 (X64) 
Apr 28 2021 10:32:18 
Copyright (C) 2017 Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) 
on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)

[andreasjordan]

I agree. The lines were removed with #7603. @potatoqualitee, do you remember why you removed the lines?

Here the needed code before current line 90 for reference:

            if (-not $Exact) {
                $currentfile = $currentfile.Replace('.xel', '*.xel')
                $currentfile = $currentfile.Replace('.xem', '*.xem')

                if ($currentfile -notmatch "xel" -and $currentfile -notmatch "xem") {
                    $currentfile = "$currentfile*.xel"
                }
            }

[potatoqualitee]

yeah, the new library doesnt do wildcards 😭

[mattcargile]

Oh no! Can this pattern be used instead?

Read-SQLXEvent -ConnectionString "Server=.;Database=master;Trusted_Connection=True;" -SessionName perf_session

This is Example 2 on the Microsoft Docs.

[mattcargile]

Been thinking more about it. So with Example 3 in the Comment Help:

https://github.com/sqlcollaborative/dbatools/blob/fb2f08f69524af3f85fb0b6617a3b1d3419a41cd/functions/Read-DbaXEFile.ps1#L41-L42

One would need to pipe results from Get-DbaXESession into Get-ChildItem or the like to grab the array of files? Then you could pipe that into the Read*

[potatoqualitee]

that seems like a solid solution, @mattcargile. ill check it out

[mattcargile]

Been thinking more about it. So with Example 3 in the Comment Help:

https://github.com/sqlcollaborative/dbatools/blob/fb2f08f69524af3f85fb0b6617a3b1d3419a41cd/functions/Read-DbaXEFile.ps1#L41-L42

One would need to pipe results from Get-DbaXESession into Get-ChildItem or the like to grab the array of files? Then you could pipe that into the Read*

I did try this solution with pwsh and it only works on the XE file that isn't currently being written to. Here is the ErrorRecord.

MessageDetails      : 
Exception             : System.IO.IOException: The process cannot access the file 
                        '\\SERVERNAME\e$\MSSQL11.SERVERNAME\MSSQL\Log\SessionName_0_132769660623030000.xel' 
                          because it is being used by another process.
                           at System.IO.FileStream.ValidateFileHandle(SafeFileHandle fileHandle)
                           at System.IO.FileStream.CreateFileOpenHandle(FileMode mode, FileShare share, 
                           FileOptions options)
                           at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, 
                           FileShare share, Int32 bufferSize, FileOptions options)
                           at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, 
                           FileShare share, Int32 bufferSize, Boolean useAsync)
                           at Microsoft.SqlServer.XEvent.XELite.XEFileEventStreamer..ctor(String fileName)
                           at XEModuleCore.SqlXEventCmdlet.OutputXELFile(String fileName)
                           at XEModuleCore.SqlXEventCmdlet.ProcessRecord()
                           at System.Management.Automation.CommandProcessor.ProcessRecord()
TargetObject          : 
CategoryInfo          : NotSpecified: (:) [Read-SqlXEvent], IOException
FullyQualifiedErrorId : System.IO.IOException,XEModuleCore.SqlXEventCmdlet
ErrorDetails          : 
InvocationInfo        : System.Management.Automation.InvocationInfo
ScriptStackTrace      : at Read-DbaXEFile<Process>, 
                             C:\Program Files\WindowsPowerShell\Modules\dbatools\1.1.20\allcommands.ps1: line 59937
                        at <ScriptBlock>, <No file>: line 1
PipelineIterationInfo : {}

Oh no! Can this pattern be used instead?

Read-SQLXEvent -ConnectionString "Server=.;Database=master;Trusted_Connection=True;" -SessionName perf_session

This is Example 2 on the Microsoft Docs.

When I attempt this solution, it appears I can only view the live feed as the data is written to the file and to my pwsh session. Is the only solution to read the actively written file to wait for it to rollover or stop the session?

ADDENDUM: I guess it may be because of the removal of the Microsoft.SqlServer.XEvent.Linq.QueryableXEventData class discussed in the issue below? This is what was giving us the live data? @potatoqualitee , did you ever speak with Microsoft on the timeline for them to bring it back in future versions of SMO. I'm assuming a native code to managed code conversion of the aforementioned class would be years down to the road to never and the solution is to have a two XE sessions that you can flip on and off as you read and process XE events? https://github.com/microsoft/sqltoolsservice/issues/807

[andreasjordan]

Are there any new ideas about this?

[potatoqualitee]

I'm having a hard time concentrating enough to solve the issue, as it's pretty complex :/

[mattcargile]

I haven't thought about the issue since my last posts. It appears one can't read the active,live xel file on the newer builds of SMO or at least the build we have in dbatools. Not sure if we can include multiple builds so we can use the Queryable Events library. Otherwise I just pipe the files directly to the Read cmdlet. Maybe there could be a feature to stop the session and then read the files and then turn it back on.

[andreasjordan]

Let me bring this one up again. Can we do anything about this? Or should we close?

[potatoqualitee]

I'll revisit in 2.0

[andreasjordan]

This got a bit stale. So let me bring this issue to you attention again...

[andreasjordan]

As we currently depend on SqlServer.XEvents, we have a problem:

https://www.powershellgallery.com/packages/SqlServer.XEvent

[andreasjordan]

Moved a lot of code around and now it is working again (on my maschine). But it needs SqlServer module installed. Feedback welcome.

[potatoqualitee]

Uhhh no idea why I didn't implement this? Read-XEvent now exists in dbatools.dll and can be used in lieu of the sql server command.

[andreasjordan]

Thanks for the feedback, updates the pull request.