January 3, 2019 at 11:34 am
I am running SSMS on my machine in the US and connecting to a server located elsewhere in the world.
I execute a stored proc that takes about 4-5 hours to finish.
It does some calculations, creates a table and then deletes some rows from that table and updates all rows.
Normally, I run it at the end of the day in the hope that it will finish overnight.
But teh Gods of IT have something else on mind. So after I lock my machine with SSMS running, they remotely reboot - probably some patches and updates.
Some times, I have noticed 0 rows in teh table and machine rebooted, so obviously the SP did not finish prior to the client machine being rebooted.
But at other times, the table is created and the machine rebooted and SP update to the table did not occur prior to the machine beging rebooted.
I have two questions:
1. Is there anything I can do in the SP to prevent this reboot? Maybe the reboot process looks for activity and since the SP is running on the remote server and hence there is no activity causing it to reboot.
2. Can I assume that the table would have been fully created if there were more than 0 rows? Could the create process have been interrupted also?
Thanks.
January 3, 2019 at 12:37 pm
1. No - there is nothing you can do to prevent the client machine from being restarted
2. No - you cannot assume that the process completed successfully based on how many rows
What you can do is one of the following:
1) Create a SQL Server Agent job on the server that executes your stored procedure. This will then run the procedure on the server and have no interaction with your client.
2) RDP to the server and execute the stored procedure in SSMS on the server
The better option is to figure out why this code takes 4 to 5 hours to run and optimize the code. There is a very high probability that optimizing the code could get the processing down to less than an hour (or even minutes) - but no way to tell for sure without seeing the code, tables and indexes with the execution plan.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 3, 2019 at 7:33 pm
Heh... write code that runs faster. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
January 6, 2019 at 8:46 am
Thanks Jeff, but the code is being transitioned to something bigger nad better so no effort on throw-away code.
January 6, 2019 at 9:52 am
tinausa - Sunday, January 6, 2019 8:46 AMThanks Jeff, but the code is being transitioned to something bigger nad better so no effort on throw-away code.
Here's a part of your original question...
Normally, I run it at the end of the day in the hope that it will finish overnight.
But teh [sic] Gods of IT have something else on mind. So after I lock my machine with SSMS running, they remotely reboot - probably some patches and updates.
While the end game may eventually cause your code to become "throw away code", it currently isn't and it's currently a problem. It's such a problem that you're looking for a workaround to keep people from rebooting in the middle of your run and, as Jeffrey Williams pointed out, there's no way for you to programmatically prevent such a thing from happening. If "no effort on throw-away code" was the appropriate rule here, you wouldn't be looking for such a workaround to begin with. 😉
If your "throw away code" has to run correctly until the other "Gods of IT" (the ones YOU work for??? ) deploy their "transitioned code" and you don't think it's valuable to fix that code so that it runs faster so that it'll avoid the problem, then your only other option is "effective communication" and, from your original post, it seems like you haven't done such a thing.
You need to talk with the both sets of "Gods of IT" and ask them some questions like...
1. When will that "transitioned code" actually be deployed? If it's not "today", then you need to ask more questions.
2. Are the reboots scheduled to run at a certain time of day? If not, could they be? If they are, what time is that? You need to know this so that you can work around their schedule so that your run(s) will either complete by the time they reboot or your stuff starts after the reboot is complete.
3. Either that or, especially if your code has required time restrictions, have a clear temporal map of when your stuff runs and when it's guaranteed to finish by and ask them if they could schedule the reboot to accommodate.
And, remember... until that "transitioned code" is successfully deployed, (and you don't even know if that will occur in days, weeks, or even months) your code is NOT throw-away code. It's code that needs to run. This won't be the last time you run into something like this in your career. Take some time to seriously improve the performance of the code... the knowledge you gain will be anything but "throw away". Who knows? Someone might even give you a little praise for quickly alleviating an apparent impasse`.
At least bring the problem up to your management and the mangers of the "Gods of IT". Ask the questions I outlined above. If your code absolutely has to run at a certain time and their contentious reboot does, as well, the only thing that's going to be left is to fix the supposed "throw away" code until their "something else" is deployed. Look at the code, come up with a plan and some proof-of-principle timings, and propose the time to fix the existing code is essential until the "transitioned code" can be deployed.
Heh... and I've seen things go seriously wrong with supposed "transitioned code" that's supposed to be an improvement. My question is always "Ok.. sounds good... what's Plan B if that fails or makes things worse?" The answer might be "We can always fall back on the existing method". Yeah... that's your "throw away" code quickly being dug out of the waste basket. 😀 Management on both teams need to be aware of that and it sound like you're the only one that can communicate that.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 6, 2019 at 10:06 am
tinausa - Thursday, January 3, 2019 11:34 AMI am running SSMS on my machine in the US and connecting to a server located elsewhere in the world.
I execute a stored proc that takes about 4-5 hours to finish.
It does some calculations, creates a table and then deletes some rows from that table and updates all rows.
Normally, I run it at the end of the day in the hope that it will finish overnight.
But teh Gods of IT have something else on mind. So after I lock my machine with SSMS running, they remotely reboot - probably some patches and updates.
Some times, I have noticed 0 rows in teh table and machine rebooted, so obviously the SP did not finish prior to the client machine being rebooted.
But at other times, the table is created and the machine rebooted and SP update to the table did not occur prior to the machine beging rebooted.I have two questions:
1. Is there anything I can do in the SP to prevent this reboot? Maybe the reboot process looks for activity and since the SP is running on the remote server and hence there is no activity causing it to reboot.
2. Can I assume that the table would have been fully created if there were more than 0 rows? Could the create process have been interrupted also?
Thanks.
Find out why the remote machines are rebooting, that is your problem, which cannot be fixed in any kind of code!
😎
January 6, 2019 at 10:51 am
Eirikur Eiriksson - Sunday, January 6, 2019 10:06 AMtinausa - Thursday, January 3, 2019 11:34 AMI am running SSMS on my machine in the US and connecting to a server located elsewhere in the world.
I execute a stored proc that takes about 4-5 hours to finish.
It does some calculations, creates a table and then deletes some rows from that table and updates all rows.
Normally, I run it at the end of the day in the hope that it will finish overnight.
But teh Gods of IT have something else on mind. So after I lock my machine with SSMS running, they remotely reboot - probably some patches and updates.
Some times, I have noticed 0 rows in teh table and machine rebooted, so obviously the SP did not finish prior to the client machine being rebooted.
But at other times, the table is created and the machine rebooted and SP update to the table did not occur prior to the machine beging rebooted.I have two questions:
1. Is there anything I can do in the SP to prevent this reboot? Maybe the reboot process looks for activity and since the SP is running on the remote server and hence there is no activity causing it to reboot.
2. Can I assume that the table would have been fully created if there were more than 0 rows? Could the create process have been interrupted also?
Thanks.Find out why the remote machines are rebooting, that is your problem, which cannot be fixed in any kind of code!
😎
I missed that - thought the OP was stating is local machine was getting remotely restarted. If the server is being restarted then the only option is to optimize the code.
I have yet to find a 'process' that takes 4-5 hours that I have not been able to optimize to something less than an hour. In most cases I have been able to get that 'process' to something less than 20 minutes - which isn't necessarily as fast as I want it - but acceptable performance for the task.
I have seen these types of processes quite often - and it just confuses me. Why create a table - populate it with *all* data - then delete some of the data - then update some of the data? Generally much easier to only insert the rows needed with the appropriate values one time. I often see these processes run multiple updates which can normally be combined in the original insert - or abstracted to a iTVF to return the calculated value - which can reduce processing time tremendously.
And further to that - as @JeffModen stated so eloquently - there is no such thing as throw away code.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 7, 2019 at 5:22 am
It is possible that the client machine is being updated. Our workplace does that. In fact, corporate IT forcibly reboots EVERYONE's PCs on a nightly basis (or they did for several years). We had to open a ticket with them to add our IT team to a list of "never reboot" because it was causing issues with us remoting in to solve production issues over the weekends and during the evenings.
And yes, we did sometimes have stuff running on SSMS that got killed because corporate would push patches to our client machines.
Regardless of whether this is a server or a client machine, I recommend the OP get in touch with "the GODS of IT" to ask for notifications to be sent when this maintenance is happening. This will allow for better coordination of when such things can be run without interference. If it's happening every night, you need to have a difference conversation. Regardless, the throwaway code needs to be fixed because you're just driving yourself insane trying to run a long process that will never finish because of reboots.
January 7, 2019 at 3:39 pm
Thanks Eirikur, the machine that is rebooting is indeed the client and not the server.
Thanks Jeff, all your points are well-taken. And you are right. THe bigger and better was supposed to be ready in June 2017 and now iit is Jan 2019 - over 18 months ago.
The code is bad at least in part because an SP calls a view that calls a function. So I should consider rewrite. But it still goes at the bottom of the priorities list and other unrelated stuff that I am working on takes higher priority.
Thanks all,
January 7, 2019 at 7:06 pm
tinausa - Monday, January 7, 2019 3:39 PMThanks Eirikur, the machine that is rebooting is indeed the client and not the server.Thanks Jeff, all your points are well-taken. And you are right. THe bigger and better was supposed to be ready in June 2017 and now iit is Jan 2019 - over 18 months ago.
The code is bad at least in part because an SP calls a view that calls a function. So I should consider rewrite. But it still goes at the bottom of the priorities list and other unrelated stuff that I am working on takes higher priority.
Thanks all,
Thanks for the feedback. I hate being "right" about such things for a couple of reasons... the first is that I know what you're going though and feel bad about that. The second is that I know what you're going through because I've been through the same thing more than once and feel bad for me. 😉 In fact, going through one of those now with a promised (now for more than 2 years) CLI (Command Line Interface) for a particular bit of SSD hardware we have that would allow me to invoke certain features without the aid of humans. They actually have the code but I've been told I can't use it until they do some sort of an upgrade. So, like you, I wait... and wait... and work around... and wait.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply