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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy