March 11, 2008 at 8:00 am
SQL 2000 SP4 on Windows 2003 SP2
I have a job that updates statistics on 13 databases. This job has been running for a couple of months with no problems. This morning the job failed with the following error message.
Connection may have been terminated by the server [SQLSTATE HY000] (error 0)
Here's the code for updating the statistics, this runs for each database.
-- Declare variables
USE
GO
SET NOCOUNT ON
DECLARE @tblName sysname, @sql nvarchar(100)
DECLARE @tbl TABLE
(name sysname)
INSERT @tbl
SELECT name
FROM sysobjects
WHERE xtype = 'U' and uid = 1
WHILE EXISTS(select top 1 * from @tbl)
BEGIN
SELECT TOP 1 @tblName = name from @tbl
SET @sql = 'UPDATE STATISTICS ' + @tblName + ' WITH SAMPLE 50 PERCENT'
EXEC sp_executesql @sql
--PRINT @sql
DELETE @tbl WHERE name = @tblName
END
Any help is appreciated!
Mike
March 11, 2008 at 9:10 pm
What's the question?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 12, 2008 at 1:13 am
Did the service restart? Are there any errors in the SQL error log from around the same time?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2008 at 1:29 am
Michael Kaluza (3/11/2008)
SQL 2000 SP4 on Windows 2003 SP2I have a job that updates statistics on 13 databases. This job has been running for a couple of months with no problems. This morning the job failed with the following error message.
Connection may have been terminated by the server [SQLSTATE HY000] (error 0)
Here's the code for updating the statistics, this runs for each database.
-- Declare variables
USE
GO
SET NOCOUNT ON
DECLARE @tblName sysname, @sql nvarchar(100)
DECLARE @tbl TABLE
(name sysname)
INSERT @tbl
SELECT name
FROM sysobjects
WHERE xtype = 'U' and uid = 1
WHILE EXISTS(select top 1 * from @tbl)
BEGIN
SELECT TOP 1 @tblName = name from @tbl
SET @sql = 'UPDATE STATISTICS ' + @tblName + ' WITH SAMPLE 50 PERCENT'
EXEC sp_executesql @sql
--PRINT @sql
DELETE @tbl WHERE name = @tblName
END
Any help is appreciated!
Mike
Hi Mike,
Please check the error log, as its sounds like a service restart on local or remote server. Do you have remote server in setup during job run? If so, this server may have been rebooted.
Thanks,
Phillip Cox
MCITP - DBAdmin
March 13, 2008 at 8:04 am
I checked and the server/service had not been rebooted/restarted. No other error messages either around that time.
Could high CPU usage cause this?
Thanks,
Mike
July 18, 2008 at 5:32 am
Hi Experts,
Sorry to interpret......but am also stuck with a similar issue!!!!
we have Scheduled a Job in SQL Server 2005 ManagementStudio enterprise edition SP2.
Sometimes it fails with error :
Unspecified error occured on SQL Server.Connection may have been terminated by the server [SQLSTATE Hy000][Error 0]. The step failed and job is failing
T-sql script of the Job
update ItemMaster_IN set SKUDescription=
case
when
len
(
case right(SKUDescription,1)
when '_' then ltrim(rtrim(SKUDescription)) + ltrim(rtrim(SysSKUCode))
else ltrim(rtrim(SKUDescription)) + '_' + ltrim(rtrim(SysSKUCode))
end
) > 50 then
left
(
case right(SKUDescription,1)
when '_' then ltrim(rtrim(SKUDescription)) + ltrim(rtrim(SysSKUCode))
else ltrim(rtrim(SKUDescription)) + '_' + ltrim(rtrim(SysSKUCode))
end, 50
)
else
case right(SKUDescription,1)
when '_' then ltrim(rtrim(SKUDescription)) + ltrim(rtrim(SysSKUCode))
else ltrim(rtrim(SKUDescription)) + '_' + ltrim(rtrim(SysSKUCode))
end
end
where
right(SKUDescription,len(ltrim(rtrim(SysSKUCode)))) <> ltrim(rtrim(SysSKUCode))
Thanking in advance,
Sandeepii
December 5, 2008 at 6:51 am
I'm getting the error too on a snapshot job. The job stops mysteriously with that error and doesn't run for a few hours then starts working again. Snapshots are scheduled hourly; server is x64. No restarts, no NT errors, no errors in SQL log--still researching. Could this be a scheduler problem?
December 5, 2008 at 10:22 am
can you try one more time. could be server was down at that time or service was re-started as other members mentioned..
December 5, 2008 at 11:01 am
Server was not down, and errors occur maybe once or twice a week at different times. The job has stopped running twice at 2 p.m. though so I'm going to run a trace to see what might be going on at that time.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply