Cannot Kill Process with Status Suspended.
Applies to: Microsoft SQL Server 2005 SP3.
Problem Description.
We have a SQL Server 2005 cluster and we are using SQL backup and jobs to
backup databases to a shared drive. While the transaction log backup of a
database was running, we ran out of space on the shared drive, and SQL Server
was not able to complete the I/O operation.
We had to kill the job because the job was not responding and we then compressed
the share drive. After the job was killed the job did not run successfully again
and the Process ID of the job when it was killed was present on the Activity
Monitor with status “Suspended” and command “KILLED/ROLLBACK”.
On the SQL Server logs we found the following error was appearing every two
minutes:
Date
11/29/2010 9:31:42 PM
Log
SQL Server (Archive #1 - 11/29/2010 9:32:00 PM)
Source
spid71
Message
The operating system returned error 1450(Insufficient system resources exist to
complete the requested service.) to SQL Server during a write at offset
0x00000ad4120a00 in file with handle 0x00000F68. This is usually a temporary
condition and the SQL Server will keep retrying the operation. If the condition
persists then immediate action must be taken to correct it.
Trying to terminate that process we use the following command but the command
was blocked.
ALTER DATABASE [DBname] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Solution.
We initiated a manual failover during maintenance window and that took care of
the problem. The backup job started to run successfully once the failover
process finished.
![[Company Logo Image]](images/SQLCofee.jpg)
