Use sqlcmd in Powershell-less memory utilization

Question

Below is the scripts I m using to loop the servers and databases in multiple servers. One server will have more than 10 databases and I m using this PS scripts to generate the information and output to CSV but I m having high memory utilization when using this PS scripts. Did anyone know how to use sqlcmd include in this PS scripts because I believe generate data using sqlcmd and output to csv will have less memory utilization

 $InstanceList = "DESKTOP-6U9IKQD", "DESKTOP-6U9IKQD\BM"
 $filepath = "C:\b1\script1.sql" 
 $Path = "C:\b1\Server"
 $InstanceList | ForEach-Object{

     $databases = invoke-sqlcmd -ServerInstance $_ -Database "master" -Query "select name from sys.databases where name like 'adventureworks%'"       
     foreach ($database in $databases) {
         $outfile = $Path,$_.replace("\","_")+"txt" -join "."
         "Database name: $($database.name)" | Out-File -FilePath $outfile -Append
         Invoke-Sqlcmd -ServerInstance  $_ -Database $database.name -InputFIle $filepath | Out-File -FilePath $outfile -Append       
     }
 }

Answer 1

Hi Mario2286-5314,

Invoke-Sqlcmd -ServerInstance $_ -Database $database.name -InputFIle $filepath | Out-File -FilePath $outfile -Append

It seems you have use sqlcmd in the Powershell SQLPS module. Please refer to Invoke-Sqlcmd which might help. And it seems it is a known issue that memory usage is high when using invoke-sqlcmd in the Powershell. Please refer to PowerShell Invoke-SqlCmd and memory usage and Creating large CSVs -- running into issue with RAM usage which might help.

You also can try to use command Invoke-DbaQuery in the Powershell dbatools.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

@Anonymous are you able to help on this