DBAs often need a quick way to extract SQL Server security information logins, roles, and permissions into a readable format for audits and reviews.
Long Running SQL Agent Jobs - How I Monitor Them Automatically
In day-to-day DBA work, one common issue we face is SQL Agent jobs that keep running for hours. They don’t fail, but they block other jobs, eat resources, and usually get noticed only after someone escalates.
To avoid that, I built a simple monitoring script that keeps an eye on long-running SQL Agent jobs and sends an email alert when something crosses the expected time.
How to Remotely Log Off a Stuck RDP Session
Sometimes when working on a remote server, you may see "Signing out" stuck forever. In such cases, you can forcefully log off the session without restarting the server.
Using Command Prompt:-
qwinsta /server:ServerName
logoff <SessionID> /server:ServerName
Example logoff 2 /server:SP-SQL-BI
Using PowerShell:-
quser /server:ServerName
logoff <SessionID> /server:ServerName
Fast PowerShell Script to Copy Latest SQL Backups Over Network
If you're managing SQL Server backups and need to automatically copy the latest
.bak files from one server to another within the same domain and on a high-bandwidth connection, this PowerShell + robocopy script is optimized for speed and reliability.function Copy-LatestBackupFast {
param (
[string]$SourceDir,
[string]$DestDir
)
# Ensure destination exists
if (!(Test-Path $DestDir)) {
New-Item -ItemType Directory -Path $DestDir -Force | Out-Null
}
# Get the latest .bak file
$Latest = Get-ChildItem -Path $SourceDir -Filter *.bak | Sort-Object LastWriteTime -Descending | Select-Object -First 1
if ($Latest) {
$SourcePath = $Latest.FullName
$FileName = $Latest.Name
# robocopy works on directories, so we pass the directory and file
$SourceFolder = Split-Path $SourcePath
$cmd = @(
"robocopy",
"`"$SourceFolder`"",
"`"$DestDir`"",
"`"$FileName`"",
"/COPY:D", # Copy data only
"/NFL", "/NDL", # No file/dir listing
"/NP", # No progress
"/NJH", "/NJS", # No job header/summary
"/R:0", "/W:0", # No retries/wait
"/MT:32" # Multi-threaded copy
) -join ' '
Write-Host "Copying: $FileName from $SourceFolder to $DestDir ..."
Invoke-Expression $cmd
} else {
Write-Warning "No .bak files found in $SourceDir"
}
}
# === Copy Each Backup Set ===
Copy-LatestBackupFast "\\node1\adads" "\\node\Backup\addad"
Copy-LatestBackupFast "\\node2\aff" "\\node\Backup\addd"
Copy-LatestBackupFast "\\node3\addd" "\\node\Backup\asdref"
Copy-LatestBackupFast "\\node3\addd" "\\node\Backup\adeedd"
Copy-LatestBackupFast "\\node3\eeww" "\\node\Backup\addd"
Automate Emailing of Latest Excel Reports from Shared Folders Using PowerShell
Automatically send the latest Excel file from a shared folder via email, along with the file's creation date, using a simple PowerShell script. No more manual checks
$sharedFolder = "\\YourSharedServer\SharedFolderPath"
$smtpServer = "smtp.yourdomain.com"
$from = "sender@yourdomain.com"
$to = "recipient@yourdomain.com"
$subject = "Latest Excel File from Shared Location"
# Get the latest Excel file
$latestFile = Get-ChildItem -Path $sharedFolder -Filter *.xls* |
Sort-Object LastWriteTime -Descending |
Select-Object -First 1
if ($latestFile) {
$filePath = $latestFile.FullName
$fileName = $latestFile.Name
$fileDate = $latestFile.LastWriteTime.ToString("yyyy-MM-dd HH:mm:ss")
$body = @"
Hello,
Please find the latest Excel file attached.
**File Name:** $fileName
**Generated On:** $fileDate
Regards,
Your DBA Automation
"@
# Send email with attachment
Send-MailMessage -From $from -To $to -Subject $subject -Body $body `
-SmtpServer $smtpServer -BodyAsHtml -Attachments $filePath
} else {
Write-Host "No Excel file found in the shared folder."
}
Subscribe to:
Comments (Atom)