Run away process

Question

Bad while loop on site cause it to keep writing to SQL, will not stop even after server stopped....NEED IMMEDIATE HELP

Answer 1

Hi @Rich Simpson ,
Thank you for the update.

Glad to hear that the issue has been resolved after restarting the DEV server. Based on your confirmation, it appears the continuous write activity was coming from an active process on that server, and restarting it helped terminate the loop.

This aligns with scenarios where a background process or application loop continues to reconnect and execute write operations, even if other components are stopped.

If you notice similar behavior in the future, we recommend:

• Checking active sessions in the database to identify the source
• Reviewing application logic (loops/retry mechanisms)
• Ensuring all related services/processes are fully stopped

We appreciate your quick action in resolving this.

Please feel free to reach out if you need any further assistance.

Answer 2

Hello Rich !

Thank you for posting on MS Learn Q&A.

First, contain the database, not only the web server. Stopping the server may not stop already open SQL sessions, background jobs, WebJobs, Functions, queue retries, another app slot, or another instance.

Try to find the active writer sessions in Azure SQL :

SELECT
    s.session_id,
    s.login_name,
    s.host_name,
    s.program_name,
    c.client_net_address,
    r.status,
    r.command,
    r.start_time,
    r.total_elapsed_time,
    r.cpu_time,
    r.logical_reads,
    r.writes,
    SUBSTRING(
        t.text,
        (r.statement_start_offset / 2) + 1,
        CASE
            WHEN r.statement_end_offset = -1
                THEN LEN(CONVERT(NVARCHAR(MAX), t.text))
            ELSE (r.statement_end_offset - r.statement_start_offset) / 2 + 1
        END
    ) AS running_statement
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s
    ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections AS c
    ON r.session_id = c.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE s.is_user_process = 1
  AND s.session_id <> @@SPID
ORDER BY r.writes DESC, r.total_elapsed_time DESC;

Then kill the runaway session by using the session_id from the query above:

KILL 123; // replace 123 with the id

If there are multiple sessions from the same app login, kill only the sessions you have identified as the runaway app sessions. Do not blindly kill every session unless you accept disrupting all users.

Temporarily block the app by doing one of these:

• Disable/remove the SQL firewall rule that allows the app/client IP.
• Disable Allow Azure services and resources to access this server if it is being used.
• Rotate or change the app SQL password or connection string secret.
• Stop all possible sources: App Service, deployment slots, WebJobs, Function Apps, Logic Apps, queue processors, containers, scheduled tasks...

Look at these fields from the first query:

login_name
host_name
program_name
client_net_address
running_statement

They should tell you whether the writer is the website, another worker, a retry process, or another environment still using the same connection string.

If bad rows were written, do not delete randomly. First take a copy or an export if possible. Azure SQL Database supports point-in-time restore, so you can restore the database to a separate database from before the bad loop, then compare the affected rows.

Fix the code loop, add transaction limits, idempotency checks, retry limits, logging and preferably a circuit breaker so the app cannot continuously write the same records again.