Hot answers tagged

5

The service account for Agent is irrelevant. All Agent does it to tell SQL Server to produce a backup file. It is SQL Server that produces the backup file, hence it is the service account for the SQL server service that matters. You can't do anything about that. So, either run SQL Server using a domain account; or use the virtual service account and assign ...


4

Aside from the comments, here's something you can use to accomplish what you're trying to do (since I'm not a fan of reinventing the wheel for locking systems). You can use the system stored procedure msdb.dbo.sp_help_job to determine if your other jobs are running as the first step in each job. Then from there you can choose what logic you want it to take ...


4

A schedule has no dependencies on a job. In fact, a schedule is a shared resource which can be used by several jobs. There's no such thing as a "local" schedule. There's nothing stopping you, of course, from having only one job using a schedule. I.e., there is no place in the Agent architecture for a schedule to kick off a certain job step. So, yes,...


3

I would say that Ola scripts work well for SQL 2014 too. Here is what you should do: EXECUTE dbo.IndexOptimize @Databases = 'USER_DATABASES', @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE', @FragmentationLevel1 = 5, @...


2

You can use the (documented) stored procedure sp_help_job to get the next execution time, however progratically capturing the results can be difficult due to the limitation around nested INSERT...EXEC calls. As an alternative, many folks use the (undocumented) stored procedure xp_sqlagent_enum_jobs which captures much of the same info. Like you, I prefer to ...


2

The @schedule_id parameter on sp_update_schedule does refer to the schedule_id column on msdb.dbo.sysschedules sp_update_schedule contains a call to sp_verify_schedule_identifiers to validate the schedule, and it's from this verification step that you are getting that error (message 14262). EXECUTE @retval = msdb.dbo.sp_verify_schedule_identifiers ...


2

If SQL Server Enterprise Edition is being used, SQL Agent jobs can be classified into a workload group specifically for the agent based on account in suser_name in the classifier function. Or, the various steps of each relevant job could be used as app_name values in a classifier function. The assigned workload group can have set GROUP_MAX_REQUESTS to 1 so ...


2

I recommend you to go through dbatools It contains all the Powershell modules required to manage the SQL Server Always On Availability Group. 1.I require that all jobs on the primary server be in sync with those on the secondary server. For Object syncing, check this package: Sync-DbaAvailabilityGroup run periodically or schedule it. 2.However, I just ...


2

You can create a linked server to the primary and write an Agent job that compares the logins on both servers.


2

The information is not in a file, it is a table named sysjobstepslogs. The column for the logged data is nvarchar(max). I don't think there's any trunc option for this table, so if you output lots and lots of data from a job step, then you will have to live with having the last execution's data in this table being huge. Note, that the regular sysjobhistory ...


2

It's "Most Likely" a Permissions Issue If you're performing a network SQL Server backup to a file share you'll most likely need to do one or more of these three common (A,B,C) tasks below: A.> SQL Server Service If you're running your backup from a SSMS query window, you need to make sure that the SQL Server service is using an Active Directory ...


1

NT SERVICE\MSSQLSERVER is a local service account - therefore it does not have access to network resources (like file shares) Change to either a Network service account or a domain account using SQL Server Configuration Manager The different service account types are described here :- https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/...


1

And that is one of the reasons why everyone should forget DBMS_JOB and use DBMS_SCHEDULER. DBMS_JOB jobs do not have history or any logging enabled. If a DBMS_JOB job fails, that should be reported in the database alert log. If a DBMS_JOB fails 16 times in a row, it will be automatically marked as broken. Your job either failed 16 times in a row and was ...


1

Here is the correct an answer to your reddit post by reddit user 'beunbehagen' I believe you cannot do DDL commands in a PL\SQL block. To do it would need to use "execute immediately " Execute immediately 'drop table <tableName>'; Also, be warned, plsql blocks normally don't run with permissions granted by roles (like dba).


1

Schedule is independent entity. We can not map it with the job steps. But you can achieve your goals in couple of others ways. Create another job and have a step to run your required job as below. And schedule it as per your wish. EXEC msdb.dbo.sp_start_job N'job_name' , 'step_name' As part of your job step, check current date and time, run or skip that ...


1

As @MSSQLServerDBA says only a sysadmin can alter another user's job or change the job's owner. But the reason for this is that any user who can do that is effectively a sysadmin. SQL Agent TSQL Job Steps always connect as the Agent Service which is always a sysadmin, but before the job step Agent impersonates the job owner with EXECUTE AS LOGIN = N'...


1

Only users in the sysadmin role can edit jobs they aren't the owner of via the SSMS object explorer so if you add all of the users to an AD group that is in the sysadmin server role, it will work, but you probably don't want to do that.


1

There is no way to assure you can get data for that historical range because- This depends upon the settings of SQL agent where you decided to keep history based on certain conditions , unless left default which again may or may not go that time back. If you are lucky enough and msdb has the information still stored for jobs 60 days old, you can simply ...


1

create or replace procedure p1 as begin for u in (select username from dba_users where account_status in ('EXPIRED', 'EXPIRED(GRACE)') and username not in ('SYS', 'SYSTEM', '...')) loop execute immediate 'alter user "' || u.username || '" account lock'; end loop; end; / For 12c and higher you could use the ORACLE_MAINTAINED='N' for ...


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