<div>
You said, <b>&quot;Especially if one of the runs takes longer than 2 minutes.&quot;</b><br />
<br />
This explains the problem.<br />
<br />
If you have many stored procedures running so slow many start running at the same time, then no telling what this might mean.<br />
<br />
And, if you have an SQL code running solid for 2x minutes... repeatedly... then some don't finish in 2x minutes...<br />
<br />
Likely you'll end up with serious disk trash, to the point where your machine starts to lag very badly.<br />
<br />
Correct way to do this is for your stored procedure to select + lock records to be used, use them, unlock them.<br />
<br />
Keep in mind, this may cause all manner of problems if a 3rd, then 4th, etc... instance of your stored procedure gets backed up.<br />
<br />
Tip: Fix your stored procedure to run shorter or decrease frequency of run, till there's no chance multiple instances will get backed up.
</div>


raburk

<div>
I would change the procedure to UPDATE the rows it has available from COMPLETE to something like WORKING into a temp table. &nbsp;Then the procedure works from the temp table to process the values. &nbsp;Once done, then use the temp table to update the WORKING status to the final status.<br />
<br />
Then it can take as long as it needs to and several can run at the same time.<br />
<br />
<a href="https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017" rel="ugc">https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-2017</a>
</div>


Developer & Coffee Roaster

Scott Fell

<div>
Thanks for the input! &nbsp;Not only has it answered my original question, but has given me some new practices to consider.
</div>


<div>
<div class="content wysiwyg-content">
I have a SQL Server Agent job that runs every two minutes. &nbsp;That job invokes a stored procedure which looks to see if there are any batches ready to be uploaded (STATUS = 'COMPLETE'). &nbsp;If there is, it decides which environment to use, and runs a function to build dynamic SQL, which it then executes at the end. <br />
<br />
I suspect that running the procedures are conflicting with one another and causing unexpected results. &nbsp;Especially if one of the runs takes longer than 2 minutes. &nbsp;Could this be the case?
</div>
</div>


I have a SQL Server Agent job that runs every two minutes.  That job invokes a stored procedure which looks to see if there are any batches ready to be uploaded (STATUS = 'COMPLETE').  If there is, it decides which environment to use, and runs a function to build dynamic SQL, which it then executes at the end. 

I suspect that running the procedures are conflicting with one another and causing unexpected results.  Especially if one of the runs takes longer than 2 minutes.  Could this be the case?

Can running a stored procedure too frequently cause conflict and unexpected results?

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.