Comments (8)
I did some testing with this long time ago, but I never got to releasing it. I added a parameter to change the severity for error 1205 (deadlocks) and lock timeouts (1222) from error to informational. Would that work for you?
from sql-server-maintenance-solution.
If I understand it right, the problem may be the returncode of the procedure. If the procedure returns something different than 0, the job will show up as failed:
` ----------------------------------------------------------------------------------------------------
--// Log completing information //--
Logging:
SET @EndMessage = 'Date and time: ' + CONVERT(nvarchar,GETDATE(),120)
SET @EndMessage = REPLACE(@EndMessage,'%','%%')
RAISERROR(@EndMessage,10,1) WITH NOWAIT
IF @returncode <> 0
BEGIN
RETURN @returncode ---<------ This might be the problem ---
END
`
Best regards,
Oliver
from sql-server-maintenance-solution.
In the test version I was working on, I was adding some logic to CommandExecute. Here is the catch block as it looks today. As you see I am raising an error with severity 16. I could so something that if the error number is 1205 or 1222, then I raise the error with severity 10 instead.
BEGIN CATCH
SET @Error = ERROR_NUMBER()
SET @ReturnCode = @Error
SET @ErrorMessageOriginal = ERROR_MESSAGE()
SET @ErrorMessage = 'Msg ' + CAST(@Error AS nvarchar) + ', ' + ISNULL(@ErrorMessageOriginal,'')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END CATCH
from sql-server-maintenance-solution.
Hi Ola,
I think this would only be one part of the solution.
In addition, the procedure IndexOptimize returns the latest return code from CommandExecute.
This would also be the returncode of the job.
Unfortunately, the job step would interpret every returncode <> o as an error. :-(
This would mean we would also have to change IndexOptimize to return 0 if the commandexecute returncode was 1205 or 1222. :-(
Best regards,
Oliver
from sql-server-maintenance-solution.
You are right. I would also need to do a RETURN 0 in CommandExecute, in this case.
Another thing is that there can also be lock timeouts in IndexOptimize (e.g. when checking sys.dm_db_index_physical_stats). (You will get the lock timeout in different places in the code, depending on what type of lock it is.)
So there are some work to get this working correctly.
If we think about the parameter. In the test version that I was working on, I had a parameter called @LockMessageSeverity
, that default was set to 16, but that could be changed to 10. Do you have any thoughts on that?
from sql-server-maintenance-solution.
Yes. That would be great. If I use IndexOptimize from T-SQL context, it would be useful if I already know that the error is not critical from the supplied severity level.
Then I would have the choice to stop further activity when an error was raised with high severity, or to continue with the next steps when the severity level is "informational". I would definitely use it. :-)
Best regards,
Oliver
from sql-server-maintenance-solution.
I have added this feature now.
https://ola.hallengren.com/versions.html
https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html#LockMessageSeverity
By default, the LockMessageSeverity is set to 16 (error), but you can set it to 10 (informational).
Here is how to use it:
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@LockTimeout = 60,
@LockMessageSeverity = 10
from sql-server-maintenance-solution.
Hi Ola,
this is great news.
I just tested the new version.
This is a perfect solution.
I still see the error in the CommandLog table, but the job completes successfully.
This is exactly how I need this. 👍
Thank you!!!! :-)
Best regards,
Oliver
from sql-server-maintenance-solution.
Related Issues (20)
- Option to include UPDATE STATISTICS for filtered indexes or filtered statistics
- Can ability to suppress TLOG backup entry in SQL Error Log (no effect to Full backup entries) be added?
- Error when @Indexes contains an object with a period in the name
- Fragmentation Percentage and Level Conflict
- Reading File Info - Linux OS
- New Feature for Index Maintenance HOT 2
- Returning 1 when running remote
- Resumable Index Operation not allowed on Index Containing Computed Column
- DatabaseBackup fails to backup certain database names HOT 1
- Log backups are not working for availability groups with backup preference set to 'Prefer Secondary' HOT 5
- Index maintenance : Skip filegroup / partition HOT 1
- Index Maintenance Miscalculating Page Numbers
- Create Restore SP
- EMC data Domain: AG backups. skip database part of same AG doesn't work.
- Exclude Multiple tables
- Include the name of the day, for daily differential backups.
- Default Backup Location Folder doesn't exists HOT 1
- Fallback backup location
- ChangeBackuplocation
- Add an option to generate the Task Scheduler commands for SQL Express
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sql-server-maintenance-solution.