The Job just runes successfully one time a day

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

The Job just runes successfully one time a day

Author

Topic

lw1990
Yak Posting Veteran

85 Posts

Posted - 2008-07-22 : 11:06:02

Hi,
I have a job running in SQL Server 2000 doing the table synch scheduled to hourly, (which compares a local table with the table in remote database, if there are new records in the remote table, it will add to the local table…).
The problem is that the result of the Job only has one successful a day at 11:00pm, other 23 times are failed. The error message from the job is:

Executed as user: CompanyDomain\Administrator. Could not locate statistics '_WA_Sys_BillingNumber_7EE295C3' in the system catalogs.
[SQLSTATE 42000] (Error 2767) DBCC execution completed. If DBCC printed error messages, contact your system administrator.
[SQLSTATE 01000] (Error 2528) DBCC execution completed. If DBCC printed error messages, contact your system administrator.
[SQLSTATE 01000] (Error 2528). The step failed.

This SQL Server is running in a virtual server. I even increased the memory from 1GB to 2 GB to the server, it's still failed.

It’s OK for the business to have the new records from remote database once a day, but I still need to figure out why it fails 23 times and just successes once a day. Please give me any idea how to fix it.

Thanks.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-22 : 11:38:21

What is the job supposed to be doing? Post some code please?

--
Gail Shaw
SQL Server MVP

lw1990
Yak Posting Veteran

85 Posts

Posted - 2008-07-22 : 14:13:45

It is an Uncategorized job, exec [Stored Procedure].
The Stored Procedure is as simple as:
begin tran
insert into localTable ( An_id, A_name )
select new_id, new_name
from @TempTable
commit tran
set nocount off
return

I used this Stored Procedure in other servers, they're working fine. Only this server has some problems.

Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-07-22 : 14:17:14

It looks like you are running DBCC Updatestatistics at same time.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-22 : 22:39:41

No other steps in the job?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-23 : 02:19:06

quote:
[i]begin tran
insert into localTable ( An_id, A_name )
select new_id, new_name
from @TempTable
commit tran
set nocount off
return

There has to be more than that. Where does the variable @TempTable get declared and populated?

--
Gail Shaw
SQL Server MVP

lw1990
Yak Posting Veteran

85 Posts

Posted - 2008-07-23 : 12:07:57

Yes the @TempTable has some records inserted by an application in another server, the records insered by:
insert into @TempTable ( New_id, New_name )
select Add_id, Add_name
from server1.database1.Remote_Table
After the procedure the Remote_Table is cleared by using:
Delete from Remote_Table

I don’t think the code has some errors, as I use the same procedure to do many Synch job in different servers (Of course the table and fields name are different), and there are no problems at all.

sodeep is correct, there is a DBCC SHOW_STATISTICS (table, index) running, and the error message tells the Index “_WA_Sys_BillingNumber_7EE295C3” is not found. I don’t know why this “Exec [Stored Procedure]” Job starts the DBCC and how to turn this DBCC off.

Thanks.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-07-23 : 22:38:40

That's why I asked if the job has other steps. And check if dbcc is in the sp.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2008-07-24 : 03:03:33

Please post all the details. All the steps in the job and the entire stored procedure.

--
Gail Shaw
SQL Server MVP

shafi.spl
Starting Member

10 Posts

Posted - 2008-07-26 : 02:39:03

i think somebody defragments the data
and also statistics are updated
so, check it first