Long SQL Job: Keep It Running When the VPN Drops

The VPN drops twenty minutes into an index rebuild, your SQL client shows a broken connection, and the obvious question has no answer in that error. Did the statement finish, roll back, or keep running without you? Reconnecting and resubmitting is the worst of the three options, because it turns one uncertain outcome into two possible ones. Move the work to something with a durable owner before the next window.

What the connection error does not tell you

A client-side error describes the client. It reports that the socket closed, not what the server did with the transaction. The statement may still be executing, may have committed just before the link died, or may have rolled back when its session was cleaned up. All three produce the same message on your laptop.

The server's state is the only source that can settle it. Look there before deciding anything.

Hand the work to a server-side owner

For work that does not need a human watching, let the database host own the execution. SQL Server Agent runs jobs on demand or on a schedule and records each run, so the job's lifecycle is independent of your VPN session. The job identity needs exactly the permissions the task requires, not an administrator's full interactive context. The laptop then starts work and reads results instead of being the process that keeps it alive.

EXEC msdb.dbo.sp_start_job @job_name = N'nightly-customer-reindex';

Availability of Agent depends on the edition and service you deployed. Use the mechanism your platform actually provides instead of assuming this one exists everywhere.

A jump host near the database shortens the path and is worth having, but it does not change the ownership question. The session still ends when the network does. Use it to observe, not to be the thing that keeps the work alive.

Leave a record the next session can read

Record a job name or identifier, the target database and the result you expect. That record is what lets a different session, or a different person, answer the question the error could not. Without it you have a vague memory of an update and no way to check.

EXEC msdb.dbo.sp_help_jobhistory @job_name = N'nightly-customer-reindex';

The history shows outcome and timing per step, which is more useful than a single success flag. For tasks that change data, add the application's own verification as well: a row count, a checksum or a status row written by the job. Two independent records are what make a retry safe, and the recorded job history is the first of them.

Reconcile before you resubmit

When the observing connection disappears, find the existing run before starting another. Read its step outcomes and decide whether the work committed, partially completed or never started. Only then apply the retry rule the workflow defines, because a blind resubmit can duplicate a load or fail halfway through a second time.

Keep the durable owner and the inspectable outcome, and a brief access failure becomes an inconvenience rather than a guess. That is the whole design goal: the work should outlive the connection that asked for it.