dataplat
Write-DbaDbTableData does not handle Measure-DbaDbVirtualLogFile output when there are multiple log files for a database
Verified issue does not already exist?
I have searched and found no existing issue
What error did you receive?
Input string was not in a correct format. At line:97921 char:9
-
throw $records[0] -
~~~~~~~~~~~~~~~~~- CategoryInfo : NotSpecified: (YourServer:String) [], Exception
- FullyQualifiedErrorId : dbatools_Write-DbaDbTableData
Steps to Reproduce
Create a database on server YourServer that has two log files. Run the below commands.
$SQLServers = "YourServer"
Measure-DbaDbVirtualLogFile -SQLInstance $SQLServers | Write-DbaDbTableData -SQLInstance YourReportingServer -Database YourReportingDatabase -Table DatabaseVLFStats -AutocreateTable -EnableException
Drop the database with 2 log files and the command works.
Please confirm that you are running the most recent version of dbatools
Get-Module dbatools | Select -ExpandProperty Version
Major Minor Build Revision
2 1 14 -1
Other details or mentions
Yes, I know having 2 log files for a database is not a best practice. One is used as an "emergency overflow" file because our main log file is on a drive that cannot be expanded.
What PowerShell host was used when producing this error
Windows PowerShell (powershell.exe), Windows PowerShell ISE (powershell_ise.exe)
PowerShell Host Version
$PSVersionTable | Out-String
Name Value
PSVersion 5.1.20348.2400
PSEdition Desktop
PSCompatibleVersions {1.0, 2.0, 3.0, 4.0...}
BuildVersion 10.0.20348.2400
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 2022 (RTM-CU12-GDR) (KB5036343) - 16.0.4120.1 (X64) Mar 18 2024 12:02:14 Copyright (C) 2022 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2022 Datacenter 10.0 <X64> (Build 20348: ) (Hypervisor)
.NET Framework Version
PS C:\Users\sstuart> Get-ChildItem 'HKLM:\SOFTWARE\Microsoft\NET Framework Setup\NDP' -Recurse | Get-ItemProperty -Name version -EA 0 | Where PSChildName -Match '^(?!S)\p{L}' | Select PSChildName, version
PSChildName Version
Client 4.8.04161 Full 4.8.04161 Client 4.0.0.0
Please don't pipe input to Write-DbaDbTableData. Save the result of the first command to a variable and use -InputObject.
Still gives same error:
$SQLServers = "YourServer" $VLFInfoTable = Measure-DbaDbVirtualLogFile -SQLInstance $SQLServers Write-DbaDbTableData -SQLInstance YourReportingServer -InputObject $VLFInfoTable -Database YourReportingDatabase -Table DatabaseVLFStats -EnableException
Input string was not in a correct format. At line:97921 char:9
-
throw $records[0] -
~~~~~~~~~~~~~~~~~- CategoryInfo : NotSpecified: (YourReportingServer:String) [], Exception
- FullyQualifiedErrorId : dbatools_Write-DbaDbTableData
Can't reproduce, so closing this.
Point here is that Measure-DbaVirtualLogFile outputs an object whose properties are strings, and in case of multiple log files you get a concatenated result, but it's still perfectly pipeable to a table.
ugly, but doesn't raise any exception on the latest dbatools.
