dataplat
Get-DbaMaintenanceSolutionLog returns nothing
Verified issue does not already exist?
I have searched and found no existing issue
What error did you receive?
no error, just no objects returned
Steps to Reproduce
Get-DbaMaintenanceSolutionLog -SqlInstance myserver01 -LogType IndexOptimize -Path 'D:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\' -Verbose
Please confirm that you are running the most recent version of dbatools
1.1.113
Other details or mentions
At first glance I think the function works fine except there's no line that actually returns the results. all 31 indexoptimize_0x511DA.........txt files are found and read.
What PowerShell host was used when producing this error
Windows PowerShell (powershell.exe)
PowerShell Host Version
Name Value
PSVersion 5.1.22000.832
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.22000.832
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 2014 (SP3-CU4-GDR) (KB5014164) - 12.0.6439.10 (X64) Apr 20 2022 03:13:42 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
.NET Framework Version
.NET Framework 4.8.9037.0
There is an output: [pscustomobject]$remember in line 249 should output that object. Maybe that line is never reached?
On my system, the agent job is named "IndexOptimize - USER_DATABASES" and so the file names begin with that string. But the code looks for "IndexOptimize_*.txt". What are the filenames like on your system?
Looks like $line = $text.ReadLine() is the problem as it reads the first line, but then stops at the second line as this is an empty line.
Ok, the while loop can be fixed with while ($null -ne ($line = $text.ReadLine())) {, but there is much more to fix as the parsing does not work...
Maybe this code was based on an old version of the maintanace solution and now the log files have a different structure?
But let's have a look at the list of authors: "Author: Klaas Vandenberghe (@powerdbaklaas) | Simone Bizzotto (@niphlod)" - maybe they can help... ;-)
ahum, the only bit I can remember is that previous ola's jobs launched sqlcmd within the step rather than the stored proc itself .... the parser here reads the "sqlcmd" bit: the job you're trying to read logs from has sqlcmd step or directly executes the sproc ?
I used our own Install- command and that sets up a step that directly runs the proc without sqlcmd.
just for test's sake: try running it with a cmdexec step and
sqlcmd -E -S .... -d ..... -Q "EXECUTE [dbo].[IndexOptimize] ...." -b
if then the logs are read and parsed, at least we know why
Thanks, you are right. Changed the step and now the files are parsable. And the "empty" lines contain a tab, so they are not empty and the while is not stopped.
So this is not a bug but only a missing information in the documentation?
well, it's a limitation. documenting that it works if your jobs are cmdexec type would clarify the limits, adjusting the code to use the current if cmdexec and develop a new parsing for the "newer" tsql step would close the bug.
As I recommend using "@LogToTable = 'Y'", I will not invest time to implement the new parsing. If someone provides a text for the documentation I can open a pull request - but that's all I want to invest here.
I concur. this is a request for documenting the limitation and optionally a feature request to make it digest the newer format.
I created this function because at that time the log files held more information than the CommandLog table, e.g. index fragmentation. Now I found those numbers in the [ExtendedInfo] column of the [CommandLog] table, so the function may be obsolete by now. @niphlod took my inefficient parsing to a whole other level. I've no idea what it would take to redo that once more, but probably just an alert on version issues would be a better use of your time. Thanks @andreasjordan @niphlod !!