Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 548
  • Last Modified:

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

0
malraff
Asked:
malraff
  • 4
  • 3
  • 3
  • +1
2 Solutions
 
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
 
malraffAuthor Commented:
hi Valentino

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

0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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
 
ValentinoVBI 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:
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
 
ValentinoVBI 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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now