DROP AUTO STATS RECURRING

USER GUIDE

INSTALL VIDEO

POWER SHELL SCRIPT

# DropAutoStats_SSPI.ps1
# Production script using System.Data.SqlClient with SSPI, archiving, logging, event logs, STRING_AGG fix, and explicit USE POS

$BaseDir = "C:\POS_Logs\DropAutoStats"
$LogFile = "$BaseDir\DropAutoStats.log"
$ErrorLog = "$BaseDir\DropAutoStats_Error.log"
$ArchiveDir = "$BaseDir\Archive"
$EventSource = "DropAutoStatsScript"
$EventLog = "Application"
$Server = "."
$Database = "POS"

if (-not (Test-Path $BaseDir)) { New-Item -ItemType Directory -Path $BaseDir | Out-Null }
if (-not (Test-Path $ArchiveDir)) { New-Item -ItemType Directory -Path $ArchiveDir | Out-Null }
if (-not [System.Diagnostics.EventLog]::SourceExists($EventSource)) {
    New-EventLog -LogName $EventLog -Source $EventSource
}

$Timestamp = Get-Date -Format "yyyy-MM-dd_HHmm"
$ArchiveBase = "$ArchiveDir\DropAutoStats_$Timestamp"
$ArchiveFile = "$ArchiveBase.log"
$Counter = 1
while (Test-Path $ArchiveFile) {
    $ArchiveFile = "$ArchiveBase" + "_$Counter.log"
    $Counter++
}
try {
    if (Test-Path $LogFile) {
        Move-Item $LogFile $ArchiveFile -ErrorAction Stop
        Compress-Archive -Path $ArchiveFile -DestinationPath "$ArchiveFile.zip" -ErrorAction Stop
        Remove-Item $ArchiveFile -ErrorAction Stop
    }
} catch {
    Write-EventLog -LogName $EventLog -Source $EventSource -EntryType Error -EventId 1004 -Message "Failed to archive log file: $_"
}
"==================================================" | Out-File -FilePath $LogFile -Append
"[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] Running DropAutoStats script" | Out-File -FilePath $LogFile -Append
Write-EventLog -LogName $EventLog -Source $EventSource -EntryType Information -EventId 1000 -Message "DropAutoStats script started."
$sqlCommand = @"
USE POS;
DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql = STRING_AGG(CONVERT(NVARCHAR(MAX), 'DROP STATISTICS '
    + QUOTENAME(SCHEMA_NAME(ob.schema_id)) + '.'
    + QUOTENAME(OBJECT_NAME(s.object_id)) + '.'
    + QUOTENAME(s.name)), '; ')
FROM sys.stats s
INNER JOIN sys.objects ob ON ob.object_id = s.object_id
WHERE SCHEMA_NAME(ob.schema_id) <> 'sys'
AND auto_created = 1;
IF @sql IS NOT NULL AND LEN(@sql) > 0
    EXEC sp_executesql @sql;
"@
$connectionString = "Server=$Server;Database=$Database;Integrated Security=SSPI;"
try {
    $conn = New-Object System.Data.SqlClient.SqlConnection $connectionString
    $cmd = $conn.CreateCommand()
    $cmd.CommandText = $sqlCommand
    $cmd.CommandTimeout = 60
    $conn.Open()
    $cmd.ExecuteNonQuery()
    $conn.Close()
    "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] SQL command executed successfully." | Out-File -FilePath $LogFile -Append
}
catch {
    $errorMsg = "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] ERROR: $($_.Exception.Message)"
    $errorMsg | Out-File -FilePath $LogFile -Append
}
$hasError = Select-String -Path $LogFile -Pattern "\berror\b|\bfail\b" -Quiet
$hasWarning = Select-String -Path $LogFile -Pattern "\bwarning\b" -Quiet
if ($hasError) {
    $errorMsg = "DropAutoStats script detected critical errors. See $ErrorLog for details."
    "[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] ERROR detected! See error log." | Out-File -FilePath $LogFile -Append
    Copy-Item $LogFile $ErrorLog -Force
    Write-EventLog -LogName $EventLog -Source $EventSource -EntryType Error -EventId 1001 -Message $errorMsg
} elseif ($hasWarning) {
    $warnMsg = "DropAutoStats script completed with warnings. Check $LogFile for details."
    Write-EventLog -LogName $EventLog -Source $EventSource -EntryType Warning -EventId 1002 -Message $warnMsg
} else {
    $infoMsg = "DropAutoStats script completed successfully with no errors."
    Write-EventLog -LogName $EventLog -Source $EventSource -EntryType Information -EventId 1003 -Message $infoMsg
}
"[$(Get-Date -Format 'yyyy-MM-dd HH:mm:ss')] Done dropping auto-created statistics." | Out-File -FilePath $LogFile -Append
"==================================================" | Out-File -FilePath $LogFile -Append
$exitCode = $LASTEXITCODE
Write-EventLog -LogName $EventLog -Source $EventSource -EntryType Information -EventId 1006 -Message "Script completed with exit code $exitCode."
try {
    $archives = Get-ChildItem -Path $ArchiveDir -Filter *.zip | Sort-Object LastWriteTime -Descending
    $archivesToDelete = $archives | Select-Object -Skip 30
    foreach ($file in $archivesToDelete) {
        Remove-Item $file.FullName -ErrorAction Stop
    }
} catch {
    Write-EventLog -LogName $EventLog -Source $EventSource -EntryType Error -EventId 1007 -Message "Failed to clean up old archives: $_"
}

TASK SCHEDULER XML

<?xml version="1.0" encoding="UTF-16"?>
<Task version="1.4" xmlns="http://schemas.microsoft.com/windows/2004/02/mit/task">
  <RegistrationInfo>
    <Description>Runs the DropAutoStats PowerShell script weekly on Sundays at midnight using SqlClient and SSPI, with robust STRING_AGG fix and explicit USE POS database context.</Description>
    <Author>Admin</Author>
  </RegistrationInfo>
  <Triggers>
    <CalendarTrigger>
      <StartBoundary>2025-05-04T00:00:00</StartBoundary>
      <Enabled>true</Enabled>
      <ScheduleByWeek>
        <DaysOfWeek>
          <Sunday />
        </DaysOfWeek>
        <WeeksInterval>1</WeeksInterval>
      </ScheduleByWeek>
    </CalendarTrigger>
  </Triggers>
  <Principals>
    <Principal id="Author">
      <UserId>SYSTEM</UserId>
      <LogonType>Password</LogonType>
      <RunLevel>HighestAvailable</RunLevel>
    </Principal>
  </Principals>
  <Settings>
    <MultipleInstancesPolicy>IgnoreNew</MultipleInstancesPolicy>
    <DisallowStartIfOnBatteries>false</DisallowStartIfOnBatteries>
    <StopIfGoingOnBatteries>false</StopIfGoingOnBatteries>
    <AllowHardTerminate>true</AllowHardTerminate>
    <StartWhenAvailable>true</StartWhenAvailable>
    <RunOnlyIfNetworkAvailable>false</RunOnlyIfNetworkAvailable>
    <IdleSettings>
      <StopOnIdleEnd>false</StopOnIdleEnd>
      <RestartOnIdle>false</RestartOnIdle>
    </IdleSettings>
    <AllowStartOnDemand>true</AllowStartOnDemand>
    <Enabled>true</Enabled>
    <Hidden>false</Hidden>
    <RunOnlyIfIdle>false</RunOnlyIfIdle>
    <WakeToRun>false</WakeToRun>
    <ExecutionTimeLimit>PT1H</ExecutionTimeLimit>
    <Priority>7</Priority>
  </Settings>
  <Actions Context="Author">
    <Exec>
      <Command>powershell.exe</Command>
      <Arguments>-ExecutionPolicy Bypass -File C:\POS_Logs\DropAutoStats\DropAutoStats_SSPI.ps1</Arguments>
    </Exec>
  </Actions>
</Task>

CREDITS