crcsupport
asked on
Updating statistics with error notification email in SQL server
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;
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?
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
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?
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.
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
I added @OnlyModifiedStatistics='Y
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the good script.
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'