SQL job failed (General network error)
From: LBT (LBT_at_discussions.microsoft.com)
Date: 01/05/05
- Next message: JXStern: "Re: Improving SELECT performance with knowledge of DB content"
- Previous message: evan b: "Re: Database Backups & Restores: an in-depth knowledge required"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 4 Jan 2005 19:59:03 -0800
Good day SQL experts and seniors,
I have a SQL job which is scheduled to run everytime when SQL agent starts.
The job will invoke a stored procedure. This stored procedure will run
continuously using "While Loop" by referring to a flag stored inside a SQL
table. If the flag is set to "OFF", the stored procedure will stop.
Problem arises when CPU usage is high whereby the job will be terminated
unexpectedly with an error message "Executed as user: KMS_NT_P1\SysAdmin.
ConnectionRead (recv()). [SQLSTATE 01000] (Message 10054) General network
error. Check your network documentation. [SQLSTATE 08S01] (Error 11). The
step failed". The MSSQL is already installed with service pack 3 and in
normal condition the job can continue executing for days.
What is the root cause of this issue? Inside my code, there is no any SQL
statement which is called across database server (only got statement to grab
record from other database resided in the same server). Anything I can do to
avoid this error from occuring?
Below are the stored procedure called by my SQL job:
---------------------------------------------------------------------------------------------
CREATE Procedure Proc_RE_On_Off_Process
As
declare @execution_status varchar(5)
declare @delay_time varchar(10)
select @execution_status = RE_On_Off_Flag from SPC_RE_On_Off_Process
if @execution_status <> 'ON'
begin
insert into SPC_RE_On_Off_Log
values (getdate(), 'RE process is not started')
end
else
begin
insert into SPC_RE_On_Off_Log
values (getdate(), 'RE process is started')
end
while @execution_status = 'ON'
begin
exec Proc_Run_RE_Jobs
if @@Error <> 0
begin
insert into SPC_RE_On_Off_Log
values (getdate(), 'Process stopped due to error: ' + cast(@@Error as
varchar(20)))
break
end
else
begin
select @execution_status = RE_On_Off_Flag, @delay_time = RE_Delay_Time
from SPC_RE_On_Off_Process
if @execution_status <> 'ON'
begin
insert into SPC_RE_On_Off_Log
values (getdate(), 'RE process is terminated')
break
end
else
begin
waitfor delay @delay_time
end
end
end
-----------------------------------------------------------------------------------------------
Any idea would be greatly appreciated. Thanks
- Next message: JXStern: "Re: Improving SELECT performance with knowledge of DB content"
- Previous message: evan b: "Re: Database Backups & Restores: an in-depth knowledge required"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
