Solved

sqL 2005 statistics update Error

Posted on 2011-09-21
11
514 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 47

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 47

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 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now