Solved

sqL 2005 statistics update Error

Posted on 2011-09-21
11
532 Views
Last Modified: 2012-08-14
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
Comment
Question by:malraff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
11 Comments
 

Author Comment

by:malraff
ID: 36573821
i meant to add that the job is already setup to
1) update all existing statistics
2) full scan
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 36578719
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
 

Author Comment

by:malraff
ID: 36579452
hi Valentino

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

0
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!

 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 36579821
What for you need to update statistics after rebulid index? Rebuil index also update statistics.
0
 

Author Comment

by:malraff
ID: 36579849
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
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 36580215
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
 
LVL 14

Expert Comment

by:Daniel_PL
ID: 36580258
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
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 36585197
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
 

Author Comment

by:malraff
ID: 36585936
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
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 36586013
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
 
LVL 37

Assisted Solution

by:ValentinoV
ValentinoV earned 500 total points
ID: 36598440
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

631 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question