Asked By RickB
03-Jul-08 07:08 AM
I'm doing queries against an SQL Server 2005 database.
After calling ...Fill($records) and processing $records the routine
exits.
An outer loop calls it again. Pretty soon I get an out-of-memory
exception.
I tried putting [gc]::collect() in the routine that creates $records
but it doesn't help.
Then I tried from the command prompt.
PS 20> [GC]::GETTOTALMEMORY($TRUE)
1379511036
PS 21> [GC]::GETTOTALMEMORY($TRUE)
1379510280
PS 22> [GC]::COLLECT()
PS 23> [GC]::GETTOTALMEMORY($TRUE)
1379510188
None of the variables I use in any of this processing exist any more
(that I can tell).
Any suggestions?
In case the devil is in the details, this is all the code involved in
the process.
Get-Table_Names emo___|%{Generate-PLan_Snapshot $_.table $_.schema 93 -
m}
function Get-Table_Names ([string]$template = '%'
,[string]$schema = '%'
,[string]$database = 'Raser_Rpt'
,[switch]$Q,[switch]$M) {
(Execute-SQLcmd "select RS.[NAME] as [schema], RT.[name] as
[table]
from sys.tables as Rt
join sys.schemas as Rs on Rt.schema_id = Rs.schema_id
where Rs.[name] like '$schema' -- schema name pattern
and Rt.[name] like '$template' -- table name pattern
function Generate-Plan_Snapshot ([string]$Table,[string]$Schema =
'dbo',
[string]$Plan,[int]$Timeout=300,[switch]$View,[switch]
$Measure){
$ErrorActionPreference = 'stop'
[GC]::Collect()
$RunDate = get-date
$Abbr = (Execute-SQLcmd "select Abbreviation from R_D.Company_Codes
where Company_Code = '$Plan'" `
-M:
$Measure).tables.item(0).rows.item(0).item(0)
$File = ("Q:\Temp\Full_Snapshot\{0,2}pdclms{1,6}.
{2:yyyyMMdd.HHmmss}.txt" -f $Abbr,$table,$RunDate).ToLower()
$Cutoff = (Execute-SQLcmd "select max(process_date) from $Schema.
$Table" -t $timeout `
-M:
$Measure).tables.item(0).rows.item(0).item(0)
$Records = Execute-SQLcmd "select * from Snapshot_$Table('$Plan')" -t
$timeout -M:$Measure
if ($Records.tables.item(0).rows.count -eq 0){throw "$table has no
records for $plan"}
$RecLen = $Records.tables.item(0).rows.item(0).item(0).length
function i_generate(){
($RecLen -45)}" -f
$RunDate,$Cutoff,$Records.tables.item(0).rows.count,''
$records.tables.item(0)|%{$_.item(0)}
($RecLen -45)}" -f
$RunDate,$Cutoff,$Records.tables.item(0).rows.count,''}
if ($view){i_generate|write-host}else{i_generate|out-file $File -
encoding ASCII}}
command(s)
function Execute-SQLcmd ([string[]]$cmd
,[int]$timeout=120
,[string]$database = 'Raser_Rpt'
,[string]$server=$(if ($server -like
'*dras01')
{"$server"}else{"pmdras01"})
,[switch]$M
,[switch]$Q,
[switch]$debug)
{ Write-Debug "$cmd" -debug:$debug
if ($M){write-host ('{0:T}' -f $(get-date))}
if ($cmd.Count -ne $null -and $cmd.Count -ne 0)
{ $SqlAdapter = New-Object
System.Data.SqlClient.SqlDataAdapter
$SqlConnection = New-Object
System.Data.SqlClient.SqlConnection
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$DataSet = New-Object System.Data.DataSet
$SqlConnection.ConnectionString = "Server=$server;Database=
$database" +
Security=True"
$SqlCmd.Connection = $SqlConnection
$SqlCmd.CommandTimeout = $timeout
$SqlAdapter.SelectCommand = $SqlCmd
$SqlCmd.CommandText = $cmd
if ($Q) {[void]$SqlAdapter.Fill($DataSet)}
Else {Write-Host "Rows Affected = $
($SqlAdapter.Fill($DataSet))"}
if ($M){write-host ('{0:T}' -f $(get-date))}
$DataSet
$SqlConnection.Close()}}