SQL Server Index Rebuild Interrupted Error Fix

You're rebuilding an index on a large table in SQL Server—say, a 500 GB table with millions of rows. The rebuild's been running for two hours. Then, bam—you get:

Msg 50000, Level 16, State 1
Operation interrupted by user

This usually happens when someone (you, a co-worker, or a monitoring tool) kills the session. Or the query times out. Maybe you closed SSMS while it was running. Or a script with a short query timeout setting kicked in. I've seen it most often when a DBA runs a rebuild from a SQL Agent job that has a 30-minute timeout, and the index takes 45 minutes.

Root cause

The message isn't lying. Somebody or something interrupted the rebuild. SQL Server didn't crash—it just stopped the operation because it got a kill signal or a timeout. The index is left in a broken state. You can't use it. Queries that touch that table might fail or run slow until you fix it.

The real fix isn't just rerunning the rebuild. You need to check if the index is corrupt, then rebuild it properly with enough time and no interruptions.

Step-by-step fix

Check the index state. Run this query to see if the index is disabled or corrupt:
```
SELECT 
    OBJECT_NAME(object_id) AS TableName,
    name AS IndexName,
    type_desc,
    is_disabled
FROM sys.indexes
WHERE object_id = OBJECT_ID('YourTableName')
```
After the interruption, you'll likely see is_disabled = 1 for that index. If it's disabled, you can't do anything until you fix it.
If the index is disabled, enable it. Run:
```
ALTER INDEX [YourIndexName] ON [YourTableName] REBUILD
```
This works even if the index is disabled. It'll rebuild it from scratch. Expect it to take as long as the original rebuild—maybe longer if the table's fragmented.
If the index isn't disabled but still broken, check for corruption. Run:
```
DBCC CHECKTABLE('YourTableName')
```
If it reports corruption, you've got a bigger problem. That's not just an interrupted rebuild—that's data corruption. You'll need to restore from backup or use DBCC CHECKDB with repair options. But 90% of the time, the table is fine, just the index is busted.
Rerun the rebuild with no timeout. In SSMS, go to Tools > Options > Query Execution > SQL Server > General. Set Query Timeout to 0 (unlimited). Or in your script, add:
```
SET LOCK_TIMEOUT -1;
```
This makes sure the rebuild doesn't get killed by a timeout mid-way.
If you're using a SQL Agent job, increase the timeout. In the job step properties, go to Advanced > Execution time limit. Set it to 0 (no limit) or a value way higher than expected. I set mine to 8 hours for big index rebuilds. You can always kill it manually if something goes wrong.

What to check if it still fails

If the rebuild fails again with the same error, here's what to look at:

Who's killing the session? Run this during the rebuild to see if someone's running KILL commands:
```
SELECT 
    session_id,
    login_name,
    host_name,
    program_name,
    status,
    command
FROM sys.dm_exec_sessions
WHERE session_id > 50
```
If you see a session with command = KILLED, that's your culprit. Talk to whoever owns that session.
Is there a blocking chain? Sometimes a rebuild gets blocked, and someone kills it thinking it's hung. Check blocking:
```
SELECT 
    blocking_session_id,
    session_id,
    wait_type,
    wait_time
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
```
If you find blocking, kill the blocking session, not the rebuild.
Check the error log. SQL Server logs kill events. Open the SQL Server log and search for 'interrupted' or 'kill'. You'll see which login ran the kill and why.
Last resort—rebuild online. If the table is huge and you can't afford a minute of downtime, use online rebuild:
```
ALTER INDEX [YourIndexName] ON [YourTableName] REBUILD WITH (ONLINE = ON)
```
This allows queries to run during the rebuild. But it uses more tempdb and takes slightly longer. Enterprise edition only, though.

One more thing—don't skip checking the disk space. An interrupted rebuild sometimes leaves extra log files or tempdb space used up. Run EXEC sp_spaceused on the table to see if it's bloated. If the rebuild failed and left a mess, you might need to shrink the log or truncate tempdb.

Bottom line: the 'operation interrupted' error is almost always a timeout or a manual kill. Fix the timeout settings, talk to your team about not killing index rebuilds, and you'll be fine.