December 6, 2011 at 6:17 am
We have a generic script (rebuild_indexes.sql) that we use to rebuild/reorganize our SQL Server 2008 R1 SP1 indexes, and we execute that script from a centralized, non-database, Windows Server 2003 R2 Enterprise Edition SP2 Production server on 20+ instances. The script, which can run for up to 4 hours on our largest instance, always runs successfully when executed from within SQL Server Management Studio 2008, regardless of the instance.
We normally launch the index rebuild script using Windows Task Scheduler, using a task definition similar to the following:
Run: C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe -command "& {c:\path_name\script_name.ps1}"
Start in: c:\path_name
c:\path_name\script_name.ps1 calls the index rebuild script with the following syntax:
sqlcmd.exe -S server_name\instance_name -E -w600 -b -l 32 -d dba -i rebuild_indexes.sql
Under Task Scheduler, the script runs successfully on all instances except one, our largest instance. For that instance, the script fails *every* time about an hour or so into the execution. (Again, just to reiterate, the same script executes fine on this instance when the tool of execution is SQL Server Management Studio instead of sqlcmd.exe.)
Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C.
We use redirection to capture the output from the task to a log file for later review, and, oddly, there is no traditional SQL Server error message ("Msg nnnn, Level nn, State nn, Line nn") like what we would normally see in a failed task in this script's output. The "CTRL+C" part of the error message is especially perplexing. I don't know of any way for a scheduled task to initiate or receive a "CTRL+C."
I have changed the day of week and time of day for the run, but that has not helped. I've looked for blocking, and that does not seem to be an issue.
Has anyone seen this error message from sqlcmd.exe?
December 7, 2011 at 7:27 am
By shear dumb luck today, I happened to kill the window (by clicking on the "X" in the top right hand corner of the window) that was running the index rebuild and immediately noticed the "Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C." message in the log file.
So, it looks as if something is forcing the Windows Task Scheduler window (on Windows Server 2003) to close in the middle of the task.
Any ideas?
December 7, 2011 at 7:35 am
the task isnt set to stop after X amount of time is it, usually the default is 72 hours
December 7, 2011 at 7:37 am
About the only suggestion I could make, would be to either hide the script window, or if you need to be able to pull it up, minimize the window. Both can be done within your command line, by adding one of the following:
To "Hide" the window: -WindowStyle Hidden
To minimize the window: -WindowStyle Minimized
So your run command would be something like this, if you hide the window:
C:\WINDOWS\system32\WindowsPowerShell\v1.0\powershell.exe -WindowStyle Hidden -command "& {c...
Of course, as I said, a hidden window you won't be able to interact with at all, but your redirection to a log file should still work...
Jason
December 7, 2011 at 7:42 am
anthony.green (12/7/2011)
the task isnt set to stop after X amount of time is it, usually the default is 72 hours
That checkbox is not selected, but it turns out that the "Stop the task if battery mode begins." was selected. This is a Virtual Machine. I wouldn't think that it would ever experience battery mode, but I will test with that checkbox unselected.
December 7, 2011 at 7:45 am
jasona.work (12/7/2011)
About the only suggestion I could make, would be to either hide the script window...
Good idea. I didn't think about that. That could solve another issue for me. Thanks for the input. 🙂
However, the issue that I am having at the moment usually occurs in the dead of night when no one is logged onto the server.
December 7, 2011 at 7:51 am
i would uncheck everything on the settings tab to be on the safe side.
other than that, I cant think of anything else which would send a break to the command window just yet
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply