Logging Jobs with Start-Transcript

Powershell (.ps1) scripts supports much better logging than SQL Server Agent or Windows Task Scheduler (WTS).
The Start-Transcript command captures script and program output at the script level. This level of logging catches error messages that other logging strategies miss. Logging features in application programs for instance don’t begin to capture error messages until sometime after the application program begins running.

In contrast the job logging in SQL Server Agent and WTS is very minimal.

Example of Logging with PowerShell Start-Transcript

Writes the log to a file with date and time. For example: RunLog_2023_08_23@12-30.txt.
The file path is based on the location of the script. For example: <path-to-the-script-file>\Run_Logs\ RunLog_2023_08_23@12-30.txt.

#########  Start Logging  ######################################### 
$DateTimeStr = Get-Date -UFormat   %Y-%m-%d@%H-%M     # date-time string for log file name 
[string]$RunLogsPath = "$($PSScriptRoot)\Run_Logs"     # path for log file created below 
[string]$Transcript_FileRef = "$($RunLogsPath)\RunLog_$DateTimeStr.txt" 
Start-Transcript -Path $Transcript_FileRef -Append -Verbose

Notes

$PSScriptRoot returns the full path of the executing script's parent directory.
$PSScriptRoot is used here as the ‘base’ path for a sub-folder named “\Run_Logs".
- $PSScriptRoot is a variables that stores state information for and about PowerShell scripts. These variables are created and maintained by PowerShell. https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_automatic_variables?view=powershell-7.2

Managing Old Log Files

See Deleting Old Files After <n> Days

https://dataself.atlassian.net/wiki/spaces/DS/pages/521732097 How to run PowerShell scripts instead of .bat files.

Example of Logging with PowerShell Start-Transcript

Notes

Managing Old Log Files

Related Pages