sunhux
asked on
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Do you have email already configured?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Did not keep track if the print statements always precede the task it is working on.
ASKER
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
(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
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/readd
Do not recall all the steps, but removing/recrearting/readd
ASKER
Perhaps between print statements 12 to 14 (inclusive), I'll need a breakdown to
know if it's progressing by how much
know if it's progressing by how much
ASKER
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
(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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
just run it in our environment?