I have set up a SQL Server Agent job to call sp_send_dbmail with a very simple select on a certain table in a certain database. Within the properties > permissions of the target database, the Database Role 'public' has select permission. The job runs fine, the email arrives, all good. The problem is, if I turn off the worryingly generous 'public' select permission and try to add the SQLServerAgent user (which is the user the agent is running under and the user that owns the agent job) and give them select permissions, the job fails with this error message: > Failed to initialize sqlcmd library with error number -2147467259. > [SQLSTATE 42000] (error 22050) Can anyone shed any light on this for me? I suspect that maybe a different user is involved in some way. I cannot get SQL Server Profiler to run (it's not installed) and I don't have access to the server to install it.

I have a SQL Server 2012 where all databases are set up in instances. I created a DTS package that accesses a table in a database, let's say MyDB\MyInstance and CSV files in a network folder. Integration Services is NOT running in an instance. My domain account doesn't have rights to login to Integration Services, so the DBA used his account and deployed the DTS package to Integration Services MSDB relying on the package for access and it runs from there without any issues. The DTS package uses my domain account to login to the database and access the table. Then I created a SQL Job to run the package. It didn't run, of course. I create a proxy account to run the package. The proxy account uses the credentials of my domain account (which doesn't have access to SSIS, but the DBA temporarily made it a local admin on the SQL Server assuming that would give it the rights that it needed but that didn't work either. The error message states that it can't access the Integration Services database. We gave the proxy SQL Agent operator role so we run the job with it. The account running the SQL Agent for the instance is NT Service\SQLAgent$MyInstance I don't know what else to try. If there's any information missing please let me know. I apologize, there are a lot of moving parts.

It may be really simple question, but at the moment I have a kind of mental loop and need your help to break it. SQL server 2016 is installed using a domain account. I didn't create an explicit login, there is only a standard generic NT SERVICE\MSSQL$XXX login and all SQL Agent Jobs like backup, rebuild indices and dbcc checkdb are running with no problems, but if I create an SQL job running a simple select on the user database like select * from [AdventureWorks2016].[HumanResources].[EmployeePayHistory] where 1= 2 I get an error Executed as user: [active directory account of the SQL Service]. Failed to initialize sqlcmd library with error number -2147467259. [SQLSTATE 42000] (Error 22050). The step failed. I have to create a login for SQL service domain account to fix an error. Any ideas what happens were? Is this a security feature of the SQL server? UPD: after a drill-down I've found an answer about sqlcmd error: it caused by msdb.dbo.sp_send_dbmail with @query parameter if the query in @query fails. But I do not have an answer to my main question: why I can't run a query over the user database using an SQL server service account. UPD2: UPD2: as I realized my description is not that understandable, sorry for that. One more try. An SQL server is installed to run using active directory accout A. That means, it I open services.msc I see that account as a login account of the service. This configuration was implemented via configuration file, not as an after-setup change. SQL Server logins doesn't contain account A, only generic NT SERVICE\MSSQL$XXX and NT SERVICE\SQLAgent$XXX which are a members of sysadmin role as usual. All SQL maintenance jobs (index maintenance, DBCC and backups) are configured using Ola's scripts and running without any problems also on user databases. If I try to create an SQL Server Job to query user database even if it's a simple query like select * from [AdventureWorks2016].[HumanResources].[EmployeePayHistory] where 1= 2 the query can't run. SQL server trace shows an error: "The server principal A is not able to access the database "AdventureWorks2016" under the current security context" If I create a login A and add it to the sysadm role the job runs, no problems, but it's absolutely clear, as a sysadm it should not have problems to query a database. The point I do not understand: all other SQL jobs are running using generic account in the background, so there is a kind of impersonification in SQL: accout A is using a generic login. As an additional information: if I create a Login for A I can't delete it because SQL server means, User is logged in, so SQl user uses it in the backupground to connect to the SQL and to impersonificate a generic login. Why it's not possible if I query a user database via SQL Agent Job? DrillDown 2nd: USE [msdb] GO BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 19.01.2022 11:21:44 ******/ IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]' IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'test_query_userdb_2Steps', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @description=N'No description available.', @category_name=N'[Uncategorized (Local)]', @owner_login_name=N'sa', @job_id = @jobId OUTPUT IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback /****** Object: Step [query separate] Script Date: 19.01.2022 11:21:44 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'query separate', @step_id=1, @cmdexec_success_code=0, @on_success_action=3, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'select * from dbo.Users where 1=2', @database_name=N'StackOverflow', @flags=0 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback /****** Object: Step [query] Script Date: 19.01.2022 11:21:44 ******/ EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'query param', @step_id=2, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @os_run_priority=0, @subsystem=N'TSQL', @command=N'EXEC msdb.dbo.sp_send_dbmail @profile_name=''my profile'', @recipients=''my email'', @subject=''ALERT: XX'', @query =''select * from dbo.users where 1 = 2''', @database_name=N'StackOverflow', @flags=0 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1 IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO The same query runs as a 1st step of the job and crashes the job if it runs as a @query parameter of send_mail SP. There is no permission context change between steps.

I have two instances on a single server. Instances are named TEST and LIVE for simplicity. Both are running SQL Server 2019. Only the LIVE instance has SQL Agent jobs running (Basic backup jobs via maintenance plans). SSIS is not installed on this environment. I have just upgraded the TEST instance to SQL Server 2022. All was quite straight forward except for the ODBC and OLEDB components having to be uninstalled for it to be able to upgrade correctly. All looks okay now on the TEST instance. However there is an issue with the LIVE instance which is still running SQL Server 2019. The backup jobs are failing with: Could not load package "Maintenance Plans\Backup" because of error 0x80070002. Description: The system cannot find the file specified. I have determined that the issue is with the DTExec component by running the backup code, from the SQLAgent job, via the command prompt. If I attempt to run it with the 150\DTS\Binn version (i.e. The SQL Server 2019 version) it fails. However when I run it with the 160\DTS\Binn (i.e. the newly installed SQL Server 2022 version) it works. I would assumed that if anything it would be the other way around. I cannot see any changes to files within the SQL Server 2019 folders. I am guessing some shared component has altered and is causing this but I cannot track it down. I am quite sure that when I upgrade the LIVE instance then this problem will be resolved but I would like to know why this upgrade has caused an issue. If there were multiple instance on a server then this could be much more of a problem. I have tried all the obvious sources of information (Including ChatGPT) for help but nothing is telling me why this is happening. Does anyone have an idea? Additional information: Reinstalling the old drivers makes no difference. I get the same results.

I have a simple SSIS package that copies files from one directory to another and then deletes the source file. It works fine when run from Visual Studio. Although when I add the package to the SSIS catalog and schedule it via a SQL Server Agent job the files do not get copied over. The job history is showing no authentication errors, so I have no idea what the issue is. Any advice would be appreciated

I have installed an instance of SQL Server 2016 SP2 called DNF containing 2 DBs and also the system DBs. After providing the instance to the dev team, they realised that they need to change the collation. They changed the collation for the 2 DBs by themselves and asked me to make the change for the system DB. I took a backup of all databases and rebuilt the system DB. It works fine and all the dbs changed to new collation. I made a mistake by restoring the msdb, model and finally I tried to restore the master. I succeeded to restore the model and msdb and faced a lot of issues when coming to themaster. I ran the query select name, collation_name from sys.databases and found that the model and msdb have the old collation, and this is logic and was a mistake from me. I tried to rebuild again, but collation refused to change on model and msdb and found the sql server and sql server for the instance stopped and won't start anymore. The situation now is : master and the 2 non system DBs have the new collation and model, msdb have the old one, and the services won't start. What is the way to solve this issue? Does an instance repair will solve this issue? Or there is something else that I can do? .

I have a SQL Agent job I need to run every two weeks on specific Saturdays (6/14, 6/28, 7/12, 7/26...). The job failed on 6/28 and when I query sysjobschedules it indicates the next scheduled run date is now 7/5. How do I get this to stay on the original schedule? I assumed it calculated from the Start Date in the schedule but this does not appear to be the case. I tried changing the Start Date in the schedule to 7/1/2025 to see if it would reset, but the next scheduled run date is still showing 7/5/2025.

I have a large CI pipeline, it runs a series of deployments against a large set of databases(75 to be specific) on a (mostly) single SQL Server(2019). As part of this CI pipeline, and post deployment I am running a large number of database tests. These tests often create(and delete) SQL Agent jobs as part of there specific prerogatives. They also often invoke standard jobs belonging to a specific database. You can think of the process flow as the following: 1. Set some stuff up 2. Run a job 3. Wait for it to complete 4. Validate it some condition These are being run in CI as the contents of the "setup" and "jobs" can change as the software itself is changes(we store database code in source control and it is deployed with each version of the software). Now most(as far as I'm aware) of the jobs have notifications disabled, and the few that do have notifications enabled are ONLY sending "Failure" notifications: So there shouldn't really be that many even with the 75 instance running not that many jobs are failing at the same time, further more we at a later stage prevent the server from sending emails(so suppress these alerts). However, occasionally I do still receive the following error in my database tests: > Unable to post notification to SQLServerAgent (reason: The maximum > number of pending SQLServerAgent notifications has been exceeded. The > notification will be ignored.) Typically I don't care, but this will cause my DB tests to occasionally fail polluting the results of my CI run. How do I increase the number of allowed "Pending SQLServerAgent notifications", I don't care what the affect that this has on the server as long as "some day" the pending notification is made? Is the only way to reduce or eliminate the existing notification configuration in all jobs? Or is there a configuration setting that can be changed here?

I am implementing a new HA/DR architecture using Always On AGs for use in both a HA a DR scenario. I have mastered a mechanism to ensure that jobs are disabled on the secondary replica and enabled when a failover takes place but now realise that, once this happens, no job history will be available. Job history is essential to determine what hadn't run or failed on the now defunct primary and needs to be run on the new primary. I am struggling to find any documentation about how to overcome this and make that data available on the new primary. Has anyone been confronted with this and how did you get around it?

I am trying to use SQL Server Agent to execute a Python file. I am executing Python file using a Powershell script. When I ran the SQL Server Agent job, error message says: Message Executed as user: NT Service\SQLAgent$SQL2022. Start-Process : This command cannot be run due to the error: "Access is denied At F:\xx.ps1:5 char:1 + Start-Process C:\Users\jdoe\AppData\Local\Programs\Python\Python312\p ... + I am thinking this happens because I originally installed Python using my own local access (username: jdoe). Today, I asked our IT to install as Admin. Currently, python is installed in two locations: (When I installed myself originally): C:\Users\jdoe\AppData\Local\Programs\Python\Python312\python.exe (When IT just installed today): C:\Program Files\Python313\python.exe What do I need to do run Python code SQL Server Agent? Where do I have to configure?

I want to find the email of the failsafe SQL Server Agent operator. Usually I use this query : DECLARE @TARGET nvarchar(255); IF @OPERATOR_CONTACT IS NULL exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'AlertFailSafeEmailAddress', @param = @TARGET OUT, @no_output = N'no_output'; But this query does not work on all SQL Server instances... Did anyone has a more generic query to obtain this information ?

I am looking to report on jobs that were running when the instance is rebooted so that I can investigate and requeue. I have tested this scenario and sometimes I can identify them in msdb.dbo.sysjobhistory via: > The job was stopped prior to completion by Shutdown Sequence 0 Sometimes msdb.dbo.sysjobhistory has no data however it will be the last_outcome_message in msdb.dbo.sysjobservers. Other times, I've rebooted an instance with a running job however neither location has this information. I understand that services should be stopped prior to shutdown however that is outside of my control. Is there a more reliable method to identify such jobs? Thanks

I need to create a script that gives the report for the following information: - Backup Job Name - Databases that have been backed up in the last 24 hours - Size of each DB backup - Start time of backup - End time of backup - Duration of backup - Location of the backup files I need to still include Log backup information, backup size and the location of the backup files stored. Could someone point me in the right direction of how to better my query? Thanks in advance. What I have tried:

@dbname sysname
SET @dbname = NULL --set this to be whatever dbname you want
SELECT 
  bup.database_name AS [Database],
  bup.server_name AS [Server],
  bup.backup_start_date AS [Backup Started],
  bup.backup_finish_date AS [Backup Finished]
  ,CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/3600 AS varchar) + ' hours, ' 
  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
  + CAST((CAST(DATEDIFF(s, bup.backup_start_date, bup.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
  AS [Total Time]
FROM msdb.dbo.backupset bup
WHERE bup.backup_set_id IN
  (SELECT MAX(backup_set_id) 
   FROM msdb.dbo.backupset
   WHERE database_name = ISNULL(@dbname, database_name) --if no dbname, then return all
   AND type = 'D' --only interested in the time of last full backup
   GROUP BY database_name) 
/* COMMENT THE NEXT LINE IF YOU WANT ALL BACKUP HISTORY */
AND bup.database_name IN (SELECT name FROM master.dbo.sysdatabases)
ORDER BY bup.database_name

(New to SSIS. None of the similar questions mention this particular impersonation error.) I'm trying to run an SSIS package as an Agent job via a proxy, and I'm getting a non-helpful error message. The credential object is for an AD account. I'm able to log in with the password to other services, but attempting to run the Agent job errors out in the Execute job step. Running the job as SQL Agent service account worked, but it doesn't have access to the filesystem that the SSIS job is supposed to pull data from. I checked the agent log file and it's this: SQLServer Error: 22046, Impersonation error. [SQLSTATE 42000] I checked the Windows security log in Event Viewer: Failure Information: Failure Reason: Unknown user name or bad password. Status: 0xC000006D Sub Status: 0xC000006A I tried it with my (sysadmin) user ID and password in a different credential / proxy and got the same error. I know that account works. We granted both SQL Agent and this account user rights to logon as batch job, and the agent account permission to impersonate. We also tried making the various accounts Windows admins and sysadmins, but nothing has helped. This worked right out of the box in our QA domain. Any suggestions how to get this to work? What domain/local/SQL Server security settings would enable or prevent the agent impersonating a user? Windows 2016 + SQL Server 2016

I want to be able to have a stopped SQL agent, but be able to browse the jobs under in. These are Async DR nodes, which i why I want the agent stopped I've achieved this on some nodes. but on others the agent is Disabled and I can't see the jobs Both instances have the SQL agent set to stopped and manual in configuration manager. Why is one disabled and the other stopped? Cheers Alex

I have done an in-place upgrade of SQL Server from SQL Server 2012 Standard Edition (64-bit) SP4 to >Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0 (Build 17763: ) (Hypervisor). After upgrading, the SQL Server Agent is in a stopped state. I have tried using the *Local System* account but still no luck. Error in Windows event logs is: > SQLServerAgent could not be started (reason: SQLServerAgent cannot start because the msdb database is read-only). I have checked and confirm that the *msdb* database is not in read only state. Can someone help me to resolve this issue. I am connected to the correct instance. I have applied the command:

ALTER DATABASE [msdb] SET READ_WRITE WITH NO_WAIT;

There was no error. The drive that stores *msdb* has not run out of space. No space issue as such. The SQL Server service account has access to the drive for those files. I do have backups of *msdb* from before the upgrade. ### Logs

-none
2023-04-05 15:22:13.640	Server	Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)    Sep 24 2019 13:48:23    Copyright (C) 2019 Microsoft Corporation   Standard Edition (64-bit) on Windows Server 2019 Datacenter 10.0  (Build 17763: ) (Hypervisor)  
2023-04-05 15:22:13.650	Server	UTC adjustment: 1:00
2023-04-05 15:22:13.710	Server	(c) Microsoft Corporation.
2023-04-05 15:22:13.710	Server	All rights reserved.
2023-04-05 15:22:13.760	Server	Server process ID is 5180.
2023-04-05 15:22:13.760	Server	System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.
2023-04-05 15:22:13.760	Server	Authentication mode is MIXED.
2023-04-05 15:22:13.830	Server	Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2023-04-05 15:22:13.840	Server	The service account is 'BICESTER\service_vrsunsql-01'. This is an informational message; no user action is required.
2023-04-05 15:22:13.870	Server	Registry startup parameters:     -d I:\DATA\master.mdf    -e C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\ERRORLOG    -l L:\LOGS\mastlog.ldf    -T 1118    -T 902
2023-04-05 15:22:13.870	Server	Command Line Startup Parameters:    -s "MSSQLSERVER"
2023-04-05 15:22:13.870	Server	Trace flag 1118 is discontinued. Use the options provided with ALTER DATABASE.
2023-04-05 15:22:14.430	Server	SQL Server detected 2 sockets with 2 cores per socket and 2 logical processors per socket, 4 total logical processors; using 4 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2023-04-05 15:22:14.430	Server	SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2023-04-05 15:22:14.440	Server	Detected 24575 MB of RAM. This is an informational message; no user action is required.
2023-04-05 15:22:14.500	Server	Using conventional memory in the memory manager.
2023-04-05 15:22:14.550	Server	Page exclusion bitmap is enabled.
2023-04-05 15:22:14.980	Server	Buffer Pool: Allocating 4194304 bytes for 2785280 hashPages.
2023-04-05 15:22:15.800	Server	Default collation: Latin1_General_CI_AS (us_english 1033)
2023-04-05 15:22:18.770	Server	Buffer pool extension is already disabled. No action is necessary.
2023-04-05 15:22:20.090	Server	Query Store settings initialized with enabled = 1, 
2023-04-05 15:22:20.170	Server	The maximum number of dedicated administrator connections for this instance is '1'
2023-04-05 15:22:20.170	Server	This instance of SQL Server last reported using a process ID of 12452 at 05/04/2023 15:21:40 (local) 05/04/2023 14:21:40 (UTC). This is an informational message only; no user action is required.
2023-04-05 15:22:20.190	Server	Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2023-04-05 15:22:20.250	Server	Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2023-04-05 15:22:20.320	Server	In-Memory OLTP initialized on lowend machine.
2023-04-05 15:22:20.480	Server	[INFO] Created Extended Events session 'hkenginexesession'  
2023-04-05 15:22:20.490	Server	Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
2023-04-05 15:22:20.510	Server	Total Log Writer threads: 2. This is an informational message; no user action is required.
2023-04-05 15:22:20.640	Server	CLR version v4.0.30319 loaded.
2023-04-05 15:22:20.650	Server	clwb is selected for pmem flush operation.
2023-04-05 15:22:21.170	spid10s	Starting up database 'master'.
2023-04-05 15:22:21.200	Server	Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
2023-04-05 15:22:21.470	Server	Software Usage Metrics is enabled.
2023-04-05 15:22:22.030	spid10s	SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2023-04-05 15:22:22.100	spid10s	SQL Server Audit has started the audits. This is an informational message. No user action is required.
2023-04-05 15:22:22.500	spid10s	SQL Trace ID 1 was started by login "sa".
2023-04-05 15:22:22.540	spid10s	Server name is 'VRSUNSQL-01'. This is an informational message only. No user action is required.
2023-04-05 15:22:22.590	spid34s	The certificate [Cert Hash(sha1) "793CBB1F5F4CF0626C1920BEEA6E8D24E96E2C36"] was successfully loaded for encryption.
2023-04-05 15:22:22.600	spid34s	Server is listening on [ 'any'  1433].
2023-04-05 15:22:22.610	spid34s	Server is listening on [ 'any'  1433].
2023-04-05 15:22:22.630	spid34s	Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2023-04-05 15:22:22.650	spid34s	Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ].
2023-04-05 15:22:22.680	spid11s	Starting up database 'mssqlsystemresource'.
2023-04-05 15:22:22.690	Logon	Error: 17187, Severity: 16, State: 1.
2023-04-05 15:22:22.690	Logon	SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again.  [CLIENT: fe80::b425:769:1663:c836%10]
2023-04-05 15:22:22.700	spid36s	Starting up database 'msdb'.
2023-04-05 15:22:22.700	spid42s	Always On: The availability replica manager is starting. This is an informational message only. No user action is required.
2023-04-05 15:22:22.720	spid42s	Always On: The availability replica manager is waiting for the instance of SQL Server to allow client connections. This is an informational message only. No user action is required.
2023-04-05 15:22:22.720	Server	Server is listening on [ ::1  1434].
2023-04-05 15:22:22.720	spid38s	Starting up database 'SUNDB'.
2023-04-05 15:22:22.730	Server	Server is listening on [ 127.0.0.1  1434].
2023-04-05 15:22:22.740	spid39s	Starting up database 'SunSystemsSecurity'.
2023-04-05 15:22:22.750	Server	Dedicated admin connection support was established for listening locally on port 1434.
2023-04-05 15:22:22.760	spid40s	Starting up database 'QA10'.
2023-04-05 15:22:22.780	spid41s	Starting up database 'SapphirePowerTools'.
2023-04-05 15:22:22.800	spid34s	SQL Server is now ready for client connections. This is an informational message; no user action is required.
2023-04-05 15:22:22.820	Server	SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.
2023-04-05 15:22:22.850	spid11s	The resource database build version is 15.00.2000. This is an informational message only. No user action is required.
2023-04-05 15:22:23.150	Server	The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/VRSUNSQL-01.valueretail.com ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
2023-04-05 15:22:23.150	Server	The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/VRSUNSQL-01.valueretail.com:1433 ] for the SQL Server service. Windows return code: 0x21c7, state: 15. Failure to register a SPN might cause integrated authentication to use NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies and if the SPN has not been manually registered.
2023-04-05 15:22:24.190	spid11s	Starting up database 'model'.
2023-04-05 15:22:25.290	spid38s	Parallel redo is started for database 'SUNDB' with worker pool size .
2023-04-05 15:22:25.300	spid40s	Parallel redo is started for database 'QA10' with worker pool size .
2023-04-05 15:22:25.350	spid39s	Parallel redo is started for database 'SunSystemsSecurity' with worker pool size .
2023-04-05 15:22:25.360	spid41s	Parallel redo is started for database 'SapphirePowerTools' with worker pool size .
2023-04-05 15:22:25.390	spid40s	Parallel redo is shutdown for database 'QA10' with worker pool size .
2023-04-05 15:22:25.440	spid38s	Parallel redo is shutdown for database 'SUNDB' with worker pool size .
2023-04-05 15:22:25.470	spid39s	Parallel redo is shutdown for database 'SunSystemsSecurity' with worker pool size .
2023-04-05 15:22:25.480	spid41s	Parallel redo is shutdown for database 'SapphirePowerTools' with worker pool size .
2023-04-05 15:22:25.560	spid11s	Clearing tempdb database.
2023-04-05 15:22:25.630	spid41s	CHECKDB for database 'SapphirePowerTools' finished without errors on 2023-04-02 00:09:22.080 (local time). This is an informational message only; no user action is required.
2023-04-05 15:22:25.650	spid39s	CHECKDB for database 'SunSystemsSecurity' finished without errors on 2023-04-02 00:09:18.850 (local time). This is an informational message only; no user action is required.
2023-04-05 15:22:25.740	spid38s	CHECKDB for database 'SUNDB' finished without errors on 2023-04-02 00:00:01.610 (local time). This is an informational message only; no user action is required.
2023-04-05 15:22:25.760	spid40s	CHECKDB for database 'QA10' finished without errors on 2023-04-02 00:09:21.510 (local time). This is an informational message only; no user action is required.
2023-04-05 15:22:25.770	spid36s	CHECKDB for database 'msdb' finished without errors on 2023-04-05 15:07:59.290 (local time). This is an informational message only; no user action is required.
2023-04-05 15:22:26.000	spid52	Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2023-04-05 15:22:26.270	spid11s	Starting up database 'tempdb'.
2023-04-05 15:22:26.480	spid11s	The tempdb database has 1 data file(s).
2023-04-05 15:22:26.510	spid36s	The Service Broker endpoint is in disabled or stopped state.
2023-04-05 15:22:26.510	spid36s	Server is listening on [ 'any'  5022].
2023-04-05 15:22:26.510	spid36s	Server is listening on [ 'any'  5022].
2023-04-05 15:22:26.510	spid36s	The Database Mirroring endpoint is now listening for connections.
2023-04-05 15:22:26.710	spid36s	Service Broker manager has started.
2023-04-05 15:22:26.710	spid10s	Recovery is complete. This is an informational message only. No user action is required.
2023-04-05 15:22:26.910	spid52	Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
2023-04-05 15:22:26.970	spid52	Using 'xpsqlbot.dll' version '2019.150.2000' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2023-04-05 15:22:27.150	spid52	Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2023-04-05 15:23:35.040	spid56	Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2023-04-05 15:23:35.070	spid56	Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2023-04-05 15:27:13.920	Logon	Error: 18456, Severity: 14, State: 5.
2023-04-05 15:27:13.920	Logon	Login failed for user 'BICESTER\VRSUNSQL-01$'. Reason: Could not find a login matching the name provided. [CLIENT: ]
2023-04-05 15:28:03.600	spid60	Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2023-04-05 15:28:03.640	spid60	Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2023-04-05 15:29:26.920	spid51	Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2023-04-05 15:29:26.960	spid51	Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2023-04-05 15:29:30.880	spid51	Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2023-04-05 15:29:30.910	spid51	Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
2023-04-05 15:32:14.500	Logon	Error: 18456, Severity: 14, State: 5.
2023-04-05 15:32:14.500	Logon	Login failed for user 'BICESTER\VRSUNSQL-01$'. Reason: Could not find a login matching the name provided. [CLIENT: ]

For maintenance tasks on my LogShipped environment I run T-SQL script to disable the copy/restore agent job for the duration of maintenance and then enable when done. When I enable the jobs via TSQL it seems the schedule hangs (at last run date) and does not execute future schedules and LS DBs fall out of sync. Restarting the SQL Agent & manual start of the job resolves the issues ok. SQL Server 2016 SP1. Using msdb..sysjobs to disable/enable the job and msdb..sysjobschedule to disable/enable the schedule Anyone any ideas?

I have a bunch of servers hosted in Canada, one of them has some erratic behaviour when under too much pressure for some time. it just looses connectivity as probably the CPU is on high. this is beyond the point of this question here, just explaining a little bit of the background. What becomes relevant is that when they reboot the box it starts to work fine again. Now the concern: After the box is rebooted the sql server agent does not get started. The reason why the sql server agent does not get started is because of “Agent XPs disabled” error On the above link, there are pictures and those match the situation I see on my rogue server. After I manually enable the Agent XPs and start manually the sql server agent then all works. But I don't want to manually start it. Why is it not starting on its own? Running this query I can see the status of Agent XPs and Show Advanced Options :

use master
go
select * from sys.configurations where name in ('Agent XPs','Show advanced options')

SQL Server Agent should not be in the windows admin group. but I wonder what permission might be missing so that the Agent XPs can be re-enabled and our sql server agent can be started? I have NOT Set the SQL Agent service to Automatic(Delayed) SQL Browser service is running. I see this in the error log: The current event was not reported to the Windows Events log. Operating system error = (null). You may need to clear the Windows Events log if it is full. That took me to have a look at this: > The “Configure log access” policy under “Computer Configuration” -> > “Administrative Templates” -> “Windows Components” -> “Event Log > Service” -> “Application” is enabled. You can check this policy by > running gpedit.msc. and this is the current settings for that: For the startup options set to for the service nothing outstanding.

I want to report on failed Sql Agent jobs using Get-SqlAgentJobHistory but excluded jobs that were not invoked by the service account. I'm running this cmd: Get-SqlAgentJobHistory -ServerInstance $SqlServer -OutcomesType Failed -StartRunDate ((Get-Date).AddMinutes(-15)) I want to exclude these: InstanceID : 146788 SqlMessageID : 0 Message : The job failed. The Job was ***invoked by User domain\user***. The last step to run was step 3 (packagename Pkg). StepID : 0 StepName : (Job outcome) but include these InstanceID : 146789 SqlMessageID : 0 Message : The job failed. The Job was ***invoked by User domain\serviceaccount***. The last step to run was step 3 (packagename Pkg). StepID : 0 StepName : (Job outcome)

So I have a SQL server 2016 on a Windows Server 2012 instance. We have 140 databases(small to med). So the SQL instance was setup for self replication and in the process we have 140 SQL agent replication jobs. When the server reboots it can handle ~120 replication jobs, then when one is stopped it can not be re-started - it fails - but with no message at all - then says "between retries". In replication monitor - the agent has the red x and or is showing not running. In the event viewer I see logread.exe application crash. I was trying to research to see if there is a cap on the number of agent jobs running. Our memory is good, the disks and CPU usage is low. I have not been part of something of this size before and I am currently out of ideas. My current work around is to have a PowerShell script read the current failures and manually kick off the -Publisher [xxx] -PublisherDB [DbName] -Distributor [xxx] -DistributorSecurityMode 1 -Continuous . That is what makes me think there is a cap on the number of agents because I can run it with out the SQL-Agent. I was thinking maybe I need to change that to more of a scheduler but we have reports run that need timely info. What am I missing? Or am I right in looking to breaking up the job runs?