question

Mario2286-5314 avatar image
0 Votes"
Mario2286-5314 asked Willsonyuan-msft edited

Use sqlcmd in Powershell-less memory utilization

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


windows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AmeliaGu-msft avatar image
0 Votes"
AmeliaGu-msft answered Mario2286-5314 commented

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What are other ways to do if want to do same like in the PS scripts without memory constraints. I have try use DBAtools but still the same with the memory issue.

0 Votes 0 ·
Mario2286-5314 avatar image
0 Votes"
Mario2286-5314 answered

@IanXue-MSFT are you able to help on this

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.