Solved

Updating statistics with error notification email in SQL server

Posted on 2016-10-20
4
117 Views
Last Modified: 2016-10-22
First of all, I'm sorry I am not SQL expert, but I have to do this tomorrow, so I need a quick starting code to do what I want.
There are 2000 tables I need to update statistics, but last time, it fails in the middle of updating statistics and I had to start from the begining. I like to break down the 2000 tables to small  pieces like 200 tables and want to get a notification email if it failed or no. Then, I can fix and continue from the point where it stopped.

When I generated codes for the Maintenance Plan for updating statistics, it gives like this;
UPDATE STATISTICS [dbo].[tbZones_CRC] 
WITH FULLSCAN,COLUMNS
GO
UPDATE STATISTICS [dbo].[TNIRErrorMessages] 
WITH FULLSCAN,COLUMNS
GO
UPDATE STATISTICS [dbo].[TNIRInBox] 
WITH FULLSCAN,COLUMNS
GO
UPDATE STATISTICS [dbo].[TNIROutBox] 
WITH FULLSCAN,COLUMNS
GO
UPDATE STATISTICS [dbo].[TNIRPricingResults] 
WITH FULLSCAN,COLUMNS
GO
UPDATE STATISTICS [dbo].[TRC04030601] 
WITH FULLSCAN,COLUMNS
GO

Open in new window


Quesitons:
1.As far as I understand this seems it does in sequential orders which is good for my purpose.  
I have 6000 lines of this code, like to use for or while loop to read the table name and do update statistics, then when it fails and finished successfully, I like to receive an email. Can someone give me a basic code to do this?

The Database email is set up on the server.

2.Can you give me codes to show what tables failed for updating statistics so that I can run manually in the next morning?
0
Comment
Question by:crcsupport
[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
  • 3
4 Comments
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41852907
Dont do it this way but instead use Ola Hallegren maintenance solution the IndexOptimisation stored procedure. It is the best there is. It will not fail due to a single step but it will just log it and continue the process until completed. Here it is:

https://ola.hallengren.com/

Just follow the instruction there and you will be just fine. You can break the jobs into multiple ones if you want. I recommend you to also run the index optimization part as it is very important for performance but it will take lot longer if they have not been optimized for a long time. If uyou want only th estatistics this is how you do it:

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL'

This will apply on all the user databases on the server. If you want only a particula database then you just give it the name:

EXECUTE dbo.IndexOptimize
@Databases = 'YourDatabase',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL'
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 41852913
The best way to execute this is to use the Job that is created for you and just update the stored procedure call with the one that applies to you. Be aware that is uses not he SQL query to execute it but the sqlcmd utility called from the job for the exact reason I mentioned above, to not stop on one database. This is how the step command will look like in your case:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d zb_dba_maint -Q "EXECUTE dbo.IndexOptimize @Databases = 'YourDatabase', @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics='Y'" -b

I added @OnlyModifiedStatistics='Y' so it will ignore "not moving" parts.
0
 
LVL 26

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 41852928
Actually if you are only do this for one database then it is simpler to execute the stored procedure:
EXECUTE dbo.IndexOptimize 
	@Databases = 'YourDatabase', 
	@FragmentationLow = NULL, 
	@FragmentationMedium = NULL, 
	@FragmentationHigh = NULL, 
	@UpdateStatistics = 'ALL', 
	@OnlyModifiedStatistics='Y'
	
	-- if you want just to see the statements that will execute without actually executing them(for test/check purpose) uncomment the line below
	--,@Execute='N'

Open in new window

You can use the @Execute='N' parameter to just check the statements without executing them.
0
 
LVL 1

Author Comment

by:crcsupport
ID: 41852936
Thanks for the good script.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql update 2 36
Return Rows as per Quantity of Columns Value In SQL 6 27
SQL works but want to get the XML node data separately 11 25
SQL 2014 missing dll from Bin? 3 31
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

740 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