Update Stats job failing

[Catadmin]

All,

Sorry it's been a while. One would think, with the new year, things would slow down for a while. Not so in my workplace. I'm beginning to feel like a fireman. @=)

Anyway, I have an SSIS maint. package which does an Update Statistics job (only) on one production database. It's been working up until 4 days ago and now it's failing every day. Job history doesn't give me much info, SQL Server Agent & SQL Server Error logs aren't worth much (I even enabled execution trace and it didn't help), and the Windows Application Log is a joke ("Job finished at...").

Here's the little information I was able to get out of the job history.

Date 2/20/2007 6:43:51 AM
Log Job History (Update_Stats)

Step ID 1
Server MyServer
Job Name Update_Stats
Step Name Update Statistics on all tables (FULLSCAN)
Duration 00:04:44
Sql Severity 15
Sql Message ID 319
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: MyDomain\User. ...TATE 01000] (Message 0) Object 'AgentsOA' was successfully marked for recompilation. [SQLSTATE 01000] (Message 15070) UPDATE STATISTICS HouseSurcharge WITH FULLSCAN, ALL [SQLSTATE 01000] (Message 0) Object 'HouseSurcharge' was successfully marked for recompilation. [SQLSTATE 01000] (Message 15070) UPDATE STATISTICS HouseSurchargePendingApproval WITH FULLSCAN, ALL [SQLSTATE 01000] (Message 0) Object 'HouseSurchargePendingApproval' was successfully marked for recompilation. [SQLSTATE 01000] (Message 15070) UPDATE STATISTICS LenderGroup WITH FULLSCAN, ALL [SQLSTATE 01000] (Message 0) Object 'LenderGroup' was successfully marked for recompilation. [SQLSTATE 01000] (Message 15070) UPDATE STATISTICS MyTable_PendingApproval WITH FULLSCAN, ALL [SQLSTATE 01000] (Message 0) Object 'OverAgentPendingApproval' was successfully marked for recompilation. [SQLSTATE 01000] (Message 15070) UPDATE STAT... The step failed.

The OverAgentPendingApproval table is empty right now, but from what I've been able to find on SQL 2005, this shouldn't make a difference. The job should still run correctly. Unless there's a bug.

Any thoughts? All the DBCC commands (checkalloc, checkdb, checkconstraints, etc.) work fine and don't show any errors that I can see. We're running SP1. Thanks in advance!


Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[ptheriault]

Can you run DBCC CHECKTABLE? What happens if you update stats manually on that table?

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[mrdenny]

Right click on the Package it self in the GUI and click view history. This will give you the T/SQL command and the exact error that was reported.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com