Question Re: Validate table progress in Technology Q&A

Validate table progress

MCMartin — Fri, 08 Feb 2019 03:55:48 GMT

Is the statement validate table logging anywhere its Progress? Can I find anything in the server messages or the request logging?

Re: Validate table progress

VolkerBarth — Fri, 08 Feb 2019 07:00:29 GMT

What do you ask for:

How to check whether currently a table is validated (by another connection/event, similar to the "BackupInProgress" database property)?
How to show the progress messages for a long-lasting validation (if there are any - AFAIK, the "progress_messages" option does not affect validation)?

Re: Validate table progress

MCMartin — Fri, 08 Feb 2019 13:02:10 GMT

the second use Case, getting Messages during a long running validation

Re: Validate table progress

johnsmirnios — Mon, 11 Feb 2019 14:49:34 GMT

I believe progress tracking does not exist for validation.

Re: Validate table progress

VolkerBarth — Mon, 11 Feb 2019 16:43:57 GMT

You only believe? I'd been sure you got that code... 🙂

Re: Validate table progress

johnsmirnios — Mon, 11 Feb 2019 18:00:22 GMT

Let's say I looked at the code the code and couldn't find any progress updates. I also don't remember adding progress support when I rewrote validation a long time. So, let's say there's no support for progress tracking but I want weasel words just in case I'm proven wrong 🙂

Re: Validate table progress

VolkerBarth — Thu, 14 Feb 2019 07:09:13 GMT

As John has pointed out, there are liekly no progress messages available during the validation of one single database object, say one huge table.

However, if you do validate several objects in one go, say via the sa_validate() procedure, and you want to get progress messages telling which object is currently validated, and how long that takes, you can have a look at the "maintenance plans" created for validation with SQL Central:

They contain SQL code that combines the validation of each according object with according MESSAGE statements and results in console output like:

...
I. 13.02 03:04:59. Validation started on 2019-02-13 at 03:04:59.993
I. 13.02 03:04:59. Validating database pages
I. 13.02 03:05:46. Validating table: "dbo"."EXCLUDEOBJECT"
I. 13.02 03:05:46. Validating table: "dbo"."ix_consultant_affected_columns"
...
I. 13.02 xx:xx:xx. Validating table: "SYS"."ISYSWEBSERVICE"
I. 13.02 xx:xx:xx. Validation finished on 2019-02-13 at xx:xx:xx.yyy
I. 13.02 xx:xx:xx. Re-enabling new connections started on 2019-02-13 at xx:xx:xx.yyy

As stated, if a single message per object is sufficient, e.g. to find out what table make take particularly long, I guess that is a good starting point for your own code...