Hot answers tagged

6

I think I would solve it this way: Create a driving stored procedure that identifies if work needs to be done. That could mean checking a table for the next folder month to process. This driving table would be pre-populated with the number of folders (months) that need to be processed. You might want to include a method to determine the order of the ...


6

You probably don't want to grant direct access to sp_start_job or sp_stop_job, or even grant access to a range of SQL Agent functionality via SQLAgentOperatorRole, to either NT AUTHORITY\NETWORK SERVICE or MyDomain\ServerA$. Doing any of those combinations would, at the very least, allow any process running as NT AUTHORITY\NETWORK SERVICE on ServerA the ...


5

There is a certain amount of overhead each time a job starts--reads and writes on msdb to track the state and schedule calculation, create a session, etc. So from an efficiency standpoint, it would be better to run it in a loop. This will also make it run more closely to every second, because when a job starts, it takes some time for the job to actually ...


5

NT AUTHORITY\NETWORK SERVICE, is a local account, but it can authenticate on the network using the server's computer account (assuming the server is domain-joined). You need to create a login on Server B for the computer account of Server A. Eg if the computer name of Server A is "ServerA", then its computer account is named "ServerA$", so something like: ...


5

The docs on sysjobschedules reveal that the data inside refreshes every 20 minutes. NOTE: The sysjobschedules table refreshes every 20 minutes, which may affect the values returned by the sp_help_jobschedule stored procedure. Which is why your next_run_date & next_run_time columns could show out of date data. For a much longer answer & deep ...


4

When you do that in a job it basically ends the session and rolls back the transaction when the job finishes. You can trace that using SQLTransaction event class in Profiler. That's the reason why you do not see any open transaction/sessions or the work being done.


3

If you found the timezone of your database to be incorrect and you are sure this causes the issue (I have seen this before), you can change it with. set_time_zone_clause::= For example: SQL> select property_value from database_properties where property_name = 'DBTIMEZONE'; PROPERTY_VALUE -------------------- +01:00 You can set a named time zone like: ...


3

You can use sp_help_jobschedule which returns the schedule_description by calling another system proc, sp_get_schedule_description. exec sp_help_jobschedule @job_name = 'Your Job Name', @include_description = 1 It'd probably be faster to roll this in a cursor instead of breaking apart that system proc which


3

You need to analyze data in the jobstephistory table. You can use following query, it is slightly modified script from Analyzing SQL Agent Job and Job Step History in SQL Server by Atif Shehzad: --Script # 1: To generate steps history of all jobs USE msdb GO SELECT j.name JobName,h.step_name StepName, CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS ...


3

In your case you should add the @Resumable = 'Y' option to your job and either restart or just wait for the next schedule to run the job and finish, depending on time and concurrency issues, as running index rebuild while many users are using the database can be an issue. Statistic and index rebuilds were transactional in earlier versions so if an alter ...


3

You could try dynamically generating TSQL to delete the specific step. Here is an example: SELECT 'exec msdb.dbo.sp_delete_jobstep @job_id=''' + convert(varchar(50), a.job_id) + '''' + ', @step_id = ' + convert(varchar(30), b.step_id) as CmdToExec , a.job_id ,name ,enabled ,b.command ,b.step_name ,b.step_id FROM msdb.dbo.sysjobs a INNER JOIN msdb.dbo....


3

As it is using a cron-style schedule definition it is unlikely to support sub-minute granularity as most (all?) cron implementations don't. A hack I've used to run a task more often than every minute with cron is a script that cron calls once per minute, which performs its task, sleeps a bit, then repeats. For example to run for times per minute: dotask; ...


3

uxcolebsrep$ is probably the SQL Server name itself. In this case, as you are using a linked server, that makes sense. Your actual Agent service account is a local account to your sql server. It does not exist on the remote Linked Server so SQL uses the Machine account to try to connect to the remote SQL Server (the one in the linked server). To get ...


2

A quicker / safer method may be to update the command on step 1 to run if it's not the primary, or not in a AG, or whatever. update msdb.dbo.sysjobsteps set command = 'print ''removed primary check code''' where command = 'your old code' and step_id = 1 and step_name like '%check prima%'


2

This is a guess, but I would try enabling lazy schema validation for the linked server under heavy load: EXEC master.dbo.sp_serveroption @server=N'Linked_Server_name', @optname=N'lazy schema validation', @optvalue=N'true' If this option is set to false, the default value, SQL Server checks for schema changes that have occurred since compilation in ...


2

You can use the @subsystem=N'TSQL' parameter of the sp_update_jobstep to alter the Step-Type. Refer to sp_add_jobstep for a list of valid subsystem values. Example: EXEC msdb.dbo.sp_update_jobstep @job_name = N'test' ,@step_id = 1 ,@subsystem = N'TSQL' ,@command = N'EXECUTE [dbo].[IndexOptimize], @Databases = ''USER_DATABASES'', @LogToTable = '...


1

Implied by Learning_DBAdmin's answer, what you do is not supported. Modifying the system tables directly, that is. You should use the interface that is provided for us, the agent stored procedures. FWIW, if you read the source code, for instance sp_update_jobschedule, you'll find a call to sp_sqlagent_notify. This isn't documented, however, for above reason....


1

You may use below script to alter the schedule: EXEC msdb.dbo.sp_attach_schedule @job_id=N'533fb186-72e9-40c5-af88-c8e57f097126',@schedule_id=16 GO USE [msdb] GO EXEC msdb.dbo.sp_update_schedule @schedule_id=16, @active_start_time=24500 GO you may find job_id using below script for the job you want to change schedule: select job_id from msdb.....


1

I would go for one job with a loop, and just do BREAK in the loop based on whatever condition suits you. However, if something goes wrong and the jobs terminates, then you need to determine for how long you can live without this job running. The simplest way IMO is to schedule the job to be executed, say, every minute. If the job is already running, then ...


1

Try this: BEGIN DBMS_SCHEDULER.CREATE_JOB( job_name => 'delete_old_rows', job_type => 'PLSQL_BLOCK', job_action => 'delete_trips;', start_date => SYSTIMESTAMP, repeat_interval => 'FREQ=SECONDLY', end_date => add_months(sysdate,12), auto_drop =&...


1

Scheduler jobs are timezone aware, so if you schedule job A at 13:00 +11:00 and job B at 13:05 'Australia/NSW' then job B will run 5 minutes after job A during the Australian summer and 55 minutes before job A during the Australian winter. Use select owner, job_name, to_char(start_date,'TZR') tz from all_scheduler_jobs to determine what timezone you have ...


1

The description is not actually a value stored in the database. It is dynamically derived from the values in dbo.sysschedules. As per scsimon's answer, you can user sp_help_jobschedule, or just use sp_help_schedule within a cursor to get the description. something like this. CREATE TABLE #tmp (schedule_id INT IDENTITY(1, 1) NOT NULL, ...


1

When inserting one record each 15 seconds I would not be worried about using triggers. Consider these two heap tables, with one rowversion column and 50 float columns each. CREATE TABLE dbo.floattable1 (Rowver ROWVERSION ,Float1 float ,Float2 float ...); CREATE TABLE dbo.floattable2 (Rowver ROWVERSION ,Float1 float ,Float2 float ...); Both ...


1

I'd suggest checking out this post from Greg Larsen - Automating SQL Server Agent Notification. You could manually create a SQL Agent job that runs periodically and searches for enabled jobs that don't have any notifications defined. Here's an example of a dynamic SQL query you could put in a SQL Agent job to find SQL agent jobs without notifications and ...


1

The job might not have completed . The output file will start with what you have received followed by the results.


1

You can, using the stored procedure sp_update_schedule declare @shcedule_id int if exists (select 1 from your_table where condition = 'met') begin select @shcedule_id = s.schedule_id from sysjobs j inner join sysjobschedules s on j.job_id = s.job_id where j.name = 'test_job' sp_update_schedule @schedule_id = 9, @freq_type = 4, @...


1

As already mentioned in the comment, error is very clear that job is trying to insert null value into not null column. You need to check definition of table SMES Live$Case Ledger. This seems to be under database name SQLNavSpeedMedical and column name is Solicitor Name. Table is under default schema dbo. Please check the DDL of table and you will see ...


1

You want to incorporate Environment Variables into your SSIS Packages. Setup the agent job to use different environment variables. You can set the variables at each step in your agent job. If you create multiple SSISDB Environments in your project folder they can be use or store different variables for executing your SSIS Packages. Reference: https://...


1

Thnaks, it has helped a lot. There is a small bug as the Duration is not in Seconds it is in HHMMSS I have fix it this way: SELECT x.executable_name , x.start_time , x.end_time AS end_time , datediff(minute, x.start_time, x.end_time) AS Duration FROM ( SELECT DISTINCT --TOP 100 PERCENT cast([sJOB].[job_id] AS VARCHAR(max)) AS execution_id , [sJSTP]...


1

I modified Shawn Melton's code slightly to get all job errors in the last 24 hours. There was a statement missing in the join, I fixed that as well. Thanks much Shawn, great stuff! /* Select all jobs with an error in the past 24 hours */ SELECT MSDB.dbo.agent_datetime(jh.run_date,jh.run_time) as date_time ,j.name as job_name,js.step_id as job_step,jh....


Only top voted, non community-wiki answers of a minimum length are eligible