Write-DbaDbTableData does not handle Measure-DbaDbVirtualLogFile output when there are multiple log files for a database

[ShaunJStu]

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 (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

[andreasjordan]

Please don't pipe input to Write-DbaDbTableData. Save the result of the first command to a variable and use -InputObject.

[ShaunJStu]

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