sqL 2005 statistics update Error

hi all

Our weekly statistics update routine is falling over at the same point each week with the below error,
the update statistics is part 3 of a 3 stage routine
1. check integrity of db
2.rebuild index task
3.update statistics

the first 2 pass of issue free

anyone any ideas on what the issue may be?

cheers

mal




Executing the query "UPDATE STATISTICS [dbo].[DESPATCH_REPORTS] 
WITH FULLSCAN
" failed with the following error: "A severe error occurred on the current command.  The results, if any, should be discarded.
A severe error occurred on the current command.  The results, if any, should be discarded.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Open in new window

malraffAsked:
Who is Participating?
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
I'd like to add something to VMontalvao's comment: it's true that statistics are updated when their index gets updated, but not all statistics are coupled with an index.  Statistics can be created manually (not that you'd do that, you'd probably just create an index), and statistics can be autogenerated as well (called _Wa_Sys_...).  So for those it might be interesting to keep the "update stats" after the "index rebuild".

More info:
http://sqlserverpedia.com/blog/sql-server-bloggers/update-statistics-before-or-after-an-index-rebuild/
http://sqlserverperformance.idera.com/tsql-optimization/understanding-sql-server-statistics/
0
 
malraffAuthor Commented:
i meant to add that the job is already setup to
1) update all existing statistics
2) full scan
0
 
ValentinoVBI ConsultantCommented:
What happens if you try to update the statistics for each index separately?  Would be an interesting test...


UPDATE STATISTICS [dbo].[DESPATCH_REPORTS] (YourIndexNameHere)
WITH FULLSCAN

Open in new window

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
malraffAuthor Commented:
hi Valentino

just tried that and each index stat updated fine when i ran then individually!

0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
What for you need to update statistics after rebulid index? Rebuil index also update statistics.
0
 
malraffAuthor Commented:
it does? :@

im actually just trying to tidy someone elses job up they where doing a rebuild then a reorganise then an update statistics

i removed the reorganise, but couldnt see if i still needed a stats update

mal
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
The code below should give you the date the index was last stats updated
SELECT OBJECT_NAME(object_id) AS TableName, name AS IndexName, STATS_DATE(object_id, stats_id) AS StatsDate
FROM sys.stats

Open in new window

0
 
Daniel_PLDB Expert/ArchitectCommented:
Hi,
Please take a look at the following links to Ola Hallegren's db maintenance sollution:
http://ola.hallengren.com
http://ola.hallengren.com/Documentation.html#IndexOptimize

I believe you will find it helpful ;)
0
 
malraffAuthor Commented:
ok... a little variation in answers :)

so am i still better running a stats update after index rebuilds? eg do sps etc not have statistics that need rebuilt or am i mixing them up with execution plans?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can save time if follow Valentino's suggestion:
So for those it might be interesting to keep the "update stats" after the "index rebuild".

But for that you need to check if you have statistics that don't belong to indexes. If negative you don't need at all the 3rd step (Update Statistics).
0
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
If you use sp_updatestats then you don't really need to worry about that:

sp_updatestats updates only the statistics that require updating based on the rowmodctr information in the sys.sysindexes catalog view, thus avoiding unnecessary updates of statistics on unchanged rows.
(from http://msdn.microsoft.com/en-us/library/ms173804.aspx)
0
All Courses

From novice to tech pro — start learning today.