?
Solved

sqL 2005 statistics update Error

Posted on 2011-09-21
11
Medium Priority
?
541 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
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 52

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 52

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 2000 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 52

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 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

719 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