Powershell script to automate cleaning and vacuuming the Netlimiter Stats database. Created with the Help of Gemini LLM. Can be automated using Task Scheduler or any other automation/scheduler tools you'd like.

NetLimiter-Database-Cleaner-DryRun.ps1

# --- Configuration ---
$DaysToKeep = 365
$SqlitePath = "sqlite3.exe" 
$DbPath = "$env:ProgramData\Locktime\NetLimiter\5\Stats\nlstats.db"

# 1. Calculate Thresholds
$TargetDate = (Get-Date).AddDays(-$DaysToKeep)
$ThresholdWin = $TargetDate.ToFileTime()
$ThresholdUnix = [int64]($TargetDate - (Get-Date "1970-01-01")).TotalSeconds

Write-Host "--- NetLimiter Cleanup Dry Run ---" -ForegroundColor Cyan
Write-Host "Target Date: $($TargetDate.ToString('yyyy-MM-dd'))"
Write-Host "Windows Threshold (Cnns): $ThresholdWin"
Write-Host "Unix Threshold (Transfers): $ThresholdUnix"
Write-Host "----------------------------------"

# 2. Execute Count Queries
if (Test-Path $DbPath) {
    # We use a single query string to get both counts
    $SqlCmd = "SELECT 'Cnns to delete: ', COUNT(*) FROM Cnns WHERE TimeStart < $ThresholdWin; SELECT 'Transfers to delete: ', COUNT(*) FROM Transfers WHERE Time < $ThresholdUnix;"
    
    & $SqlitePath $DbPath "$SqlCmd"
} else {
    Write-Error "Database not found at $DbPath"
}

NetLimiter-Database-Cleaner.ps1

# --- Configuration ---
$DaysToKeep = 365
$SqlitePath = "sqlite3.exe" 
$DbPath = "$env:ProgramData\Locktime\NetLimiter\5\Stats\nlstats.db"
$LogPath = "$env:ProgramData\Locktime\NetLimiter\5\Stats\cleanup_log.txt"
$ServiceName = "nlsvc"
$MaxLogSizeMB = 5

# --- Log Rotation Logic ---
if (Test-Path $LogPath) {
    if ((Get-Item $LogPath).Length -gt ($MaxLogSizeMB * 1MB)) {
        Move-Item -Path $LogPath -Destination "$LogPath.old" -Force
        Write-Host "Log rotated."
    }
}

function Write-Log {
    param([string]$Message)
    $Timestamp = Get-Date -Format "yyyy-MM-dd HH:mm:ss"
    "$Timestamp - $Message" | Out-File -FilePath $LogPath -Append
}

Write-Log "Starting NetLimiter Database Cleanup..."

# --- 1. Calculate the TWO different thresholds ---
$ThresholdWin = (Get-Date).AddDays(-$DaysToKeep).ToFileTime()      # 14-digits for Cnns
$ThresholdUnix = [int64]((Get-Date).AddDays(-$DaysToKeep) - (Get-Date "1970-01-01")).TotalSeconds # 10-digits for Transfers

# 2. Stop Service
if ((Get-Service $ServiceName).Status -eq 'Running') {
    Write-Log "Stopping $ServiceName..."
    Stop-Service -Name $ServiceName -Force
}

# 3. Execution
if (Test-Path $DbPath) {
    $InitialSize = (Get-Item $DbPath).Length / 1MB
    # APPLY FORMATTING HERE:
    Write-Log ("Initial Database Size: {0:N2} MB" -f $InitialSize)
    
    $SqlCmd = "DELETE FROM Cnns WHERE TimeStart < $ThresholdWin; DELETE FROM Transfers WHERE Time < $ThresholdUnix; VACUUM;"
    $ErrorOutput = & $SqlitePath $DbPath "$SqlCmd" 2>&1
    
    if ($LASTEXITCODE -eq 0) {
        $FinalSize = (Get-Item $DbPath).Length / 1MB
        Write-Log "Cleanup successful. Records older than $DaysToKeep days removed."
        # APPLY FORMATTING HERE:
        Write-Log ("Final Database Size: {0:N2} MB (Reclaimed: {1:N2} MB)" -f $FinalSize, ($InitialSize - $FinalSize))
    } else {
        Write-Log "SQL Error: $ErrorOutput"
    }
} else {
    Write-Log "ERROR: Database not found at $DbPath"
}

# 4. Restart Service
Start-Service -Name $ServiceName
Write-Log "Service restarted. Cleanup cycle complete."
Write-Log "--------------------------------------------"