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

# Configuration

$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."

}

Unable to Drop a User from SSISDB?

I ran into an issue when trying to drop a user from SSISDB (the Integration Services Catalog database). 

The database principal has granted or denied permissions to catalog objects in the database and cannot be dropped.

The transaction ended in the trigger. The batch has been aborted.

How to solve it?

Find where the user has permissions

USE SSISDB

go

SELECT *

FROM catalog.object_permissions

WHERE grantee_sid = SUSER_SID('YourUserName');

--change YourUserName to your actual username

Revoke permissions

REVOKE READ ON OBJECT::[folder_name] TO [YourUserName];

REVOKE MODIFY ON OBJECT::[project_name] TO [YourUserName];

Drop the user

DROP USER [YourUserName];