Sql 2025 T-SQL Job

Question

SQL 2025 Std running in a Windows server 2022 Std

A job with a T-SQL task (which calls a stored procedure) ran well weekly for almost a month, at a specific time of day

For various reasons, I had to change it so that it would run at a different time of day. I made the change. The next day the job failed, with the system error:The last step to execute was step 0. Can you understand?

I am currently test a CMDEXE task in the lab, which calls the same stored procedure, to see if it behaves the same way with changes in the scheduling.

I would like to know your opinion on:

Auto-close of a database and Autogrowth Maxsize parameterization 

In percent or Megabytes?

Thanks in advanced

DO

Answer 1

It seems that the job you modified encountered an issue after the scheduling change, indicated by the error "The last step to execute was step 0." This typically means that the job did not execute any steps, possibly due to a misconfiguration in the job settings or scheduling. Here are a few things to check:

1. Job Schedule: Ensure that the new schedule is correctly configured and that it is active. Sometimes, jobs may not run if the schedule is set incorrectly or if there are conflicts with other jobs.
2. Job Step Configuration: Verify that the job step that calls the stored procedure is still correctly set up. Check for any changes in the stored procedure itself that might affect its execution.
3. Permissions: Ensure that the SQL Server Agent service account has the necessary permissions to execute the stored procedure.
4. Error Logs: Check the SQL Server Agent job history and SQL Server error logs for more detailed error messages that can provide additional context on why the job failed.

Regarding your question about the auto-close feature and autogrowth settings:

• Auto-Close: It is generally recommended to keep the auto-close option disabled for production databases. Enabling auto-close can lead to performance issues as it causes the database to close and reopen frequently, which can be detrimental to performance.
• Autogrowth Maxsize: Setting the autogrowth max size can be done in either percentage or megabytes. The choice depends on your specific needs:
  • Percentage: This is useful for databases that grow significantly over time, as it allows for proportional growth relative to the current size.
  • Megabytes: This can be more predictable and manageable, especially for databases with known growth patterns. It can help prevent excessive fragmentation and control growth more tightly.

In many cases, a combination of both settings can be beneficial, but monitoring the database's growth and performance is key to determining the best approach for your environment.

Answer 2

We have been through this before, haven't we?

But this time you gave a hint, and I was able to reproduce the problem. If you have the database in AUTO_CLOSE that could indeed be the problem.

I created a database on a USB disk, and to spice it up, I had attached the USB disk to a virtual machine where I had SQL Server running. I set the database in AUTO_CLOSE, and I created a simple job to run a stored procedure. I scheduled it to run every 10 minutes. And indeed it fails with this The job failed. The Job was invoked by Schedule 9 (slisk). The last step to run was step 0 (no steps ran).

Apparently, Agent is a little impatient when making the connection for the job step, and gives up when there is a just a small delay. Or if just stops at the gate when the database is closed.

What I find irritating is that I can't find any diagnostics which points to the problem. This is why your previous thread just went on and on. Had Agent only reported this problem somewhere, we would have understood this a lot earlier.

I guess the reason job started failing when you changed the schedule is that the previous time slot, there were already users in the database, so that it was open. Or the system was less busy, so the database opened before Agent gave up.

I was too lazy to try a CmdExec job myself, but I would expect that to work, since SQLCMD by default waits for 15 seconds before giving up on the connection. If nothing else, you will get better error diagnostics.