command / script /ways to track progress of an SQL script that is updating MS SQL database schema

Refer to attached SQL script: I may run it in scheduled batch mode (in case it takes too
long & my Rdp session into SQL Management Studio drops/disconnects).

The database of 90GB (& another one to be done next month is 550GB) is rather
large & the script may take very long (or possibly 'hangs').

Is there a way (using sql command or tools or SQL Mgmt Studio) to track its progress
(say how much has been updated, how many more to go or in terms of % completion ? )

I'm not a DBA & not MS SQL trained, just a sysadmin running blindly from Management studio,
so appreciate a layman's step by step instruction

Just checking if there's IO activity is not good enough
UpdateSchemaSqlScript.txt
sunhuxAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sachiekCommented:
You can introduce a temp table in sql server.

Once certain number of migrations or updates been done, insert one entry or you can insert after every row.

Have a seperate job which will monitor this temp table every 15mins or x min to send one email. You can use ssis or email from ssms.

So this way you can get to know after certain time period if script is running by the email showing you about the number of rows processed.
sunhuxAuthor Commented:
Can you provide specific script / commands to do this such that I can
just run it in our environment?
sachiekCommented:
Do you have email already configured?
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

sachiekCommented:
In case you had already email configured..

then you can use this set kind of code to send out email say every "n" rows processed..

USE msdb 
GO 

EXEC sp_send_dbmail 
@profile_name='LongrunProfile', _
@recipients='Your email@domain.com', @subject='<< Your custom subject >>', _
@body='<< Your custom message >>' 

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sunhuxAuthor Commented:
Not sure if we have email configured, need to
check but I'm fine with saving the file to be
emailed to the local disk.

Is there any way anyone knows how we can "set spool ... " to output
some sort of logs that I can check for the progress?  

Feel free to amend this script & point out to me which line you
amended/added
sachiekCommented:
arnoldCommented:
Your script includes print statements at different stages of the process, looking at the messages tab, you will see what it is doing now.
Did not keep track if the print statements always precede the task it is working on.
sunhuxAuthor Commented:
Am aware of the print statements but they are not in proportion to the progress
(in terms of % completion or in terms of how many hours more to go before
 completion) of the job.

In fact when we tested on an 80GB DB, the last 5-6 statements suddenly complete
in about 1 minutes but at print statements 12-14, it practically takes all the time
arnoldCommented:
I am unaware of a way to predict the duration of each task ahead of time to be in a position to show a progress and time remaining.
Do not recall all the steps, but removing/recrearting/readding indexes are fairly quick, the alteration to the schema is the longer proces affecting each row.
sunhuxAuthor Commented:
Perhaps between print statements 12 to 14 (inclusive), I'll need a breakdown to
know if it's progressing by how much
sunhuxAuthor Commented:
Am aware of the print statements but they are not in proportion to the progress
(in terms of % completion or in terms of how many hours more to go before
 completion) of the job.

In fact when we tested on an 80GB DB, the last 5-6 statements suddenly complete
in about 1 minutes but at print statements 12-14, it practically takes all the time
arnoldCommented:
Font sure why you are reposting what you posted previously in the last two comments?
During dev. Running the same script along with date/time outputs between the different steps would give you a baseline of duration, that you could "extrapolate" onto the larger DB.

Redgate is a commercial product that helps schema transition/DB comparisons that might include what you are looking for.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.