I'm trying to figure out a way to automatically restart the SQL Agent service when an AG fails over to a secondary node. I've created a job on each AG node that runs every 15 seconds and detects when a fail over has happened. It then issues a SQL Agent stop command and then a SQL Agent start command. The problem is that once the stop command happens the start command never fires because the SQL Agent is now stopped.
View 3 RepliesI need to be able to restart an application service after the SQL Cluster fails over. how to accomplish this as a SQL job?
View 1 Replies View RelatedMorning,
I set up SQL Agent to start up on a domain account with Administrator rights, and the
auto-restart option is on. However, the agent does not restart automatically when restarting
SQL Server. No problems restarting manually. The Event Viewer logs the following:
"SQLServerAgent security context does not have server-autorestart privileges."
Any ideas on how to diagnose this?
Thanks,
Sergio
Hello everybody
I setup Sql agent for autostart with sql server and autorestart if stoped
but after rebooting NT or after stoping SQL server it never starts ,I have to start it manualy
Where to look for problem ?
I've installed an SSL certificate via IIS. I stopped sql server and agent but cannot restart them. I'm getting the following message in the error log:
2007-10-31 21:24:53.63 Server Error: 17182, Severity: 16, State: 1.
2007-10-31 21:24:53.63 Server TDSSNIClient initialization failed with error 0x2, status code 0x38.
2007-10-31 21:24:53.63 Server Error: 17182, Severity: 16, State: 1.
2007-10-31 21:24:53.63 Server TDSSNIClient initialization failed with error 0x2, status code 0x1.
2007-10-31 21:24:53.63 spid9s Starting up database 'model'.
2007-10-31 21:24:53.63 Server Error: 17826, Severity: 18, State: 3.
2007-10-31 21:24:53.63 Server Could not start the network library because of an internal error in the network library. To determine the cause, review the errors immediately preceding this one in the error log.
2007-10-31 21:24:53.63 Server Error: 17120, Severity: 16, State: 1.
2007-10-31 21:24:53.63 Server SQL Server could not spawn FRunCM thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.
I've tried removing the SSL certificate but with no success.
Help! I'm starting to think that the only solution is to uninstall sql server 2005 and reinstall.
Hi, I have setup a replication between two servers. And sometimes due to network connection problem, I get error message like :
The subscription to publication 'pub_1' is invalid.
After 10 retries, if connection cannot be made successfully, the Merge Agent will stop running.
How can I start the Merger Agent automatically after it has been stopped?
The SQL Server Agent process is running. When I use EM to start a database integrity
check job, it fails with an error message of 22029 and the following:
[LOG] SQLServerAgent security context does not have server-autorestart privileges
I can not find error 22029 in Books on Line. I will go to Microsoft Knowledge base
and research this problem.
In the mean time if someone can help, it is much appreciated.
Thanks to all.
I have sql 2000 sp4 running on a cluster, we had to fail over to other cluster, and now the sql agent choice to use a mail profile is grayed out. I can go to sql mail and test the profile and it works, but I can't change it for the sql agent? Anyone seen this before
View 3 Replies View RelatedWe have SQL Server 2000 Standard and recently moved from Exchange 5.5to Exchange 2003. This problem occurred both before and after thee-mail change.We would like to send notifications of failed jobs to multiple peoplein case one person happens not to be in on the day there is a problem.Setting up an operator with an e-mail name that combines twoaddresses seems to work ok as long as we use a format like thefollowing:smith_john; jones_maryorJoin Bytes!; Join Bytes!However, when we add a third name, we start getting problems. Thefirst and third accounts listed get the message but the second onedoes not -- apparently because the address gets corrupted. Forexample (based on the message received at one of the good addresses),a message sent to:Join Bytes!; Join Bytes!;Join Bytes!goes to Smith and Jackson successfully but Jones' address is corruptedto:Join Bytes!omand, of course, fails.From some other testing, it appears that the last three characters ofthe first address always get appended to the end of the secondaddress.Has anyone else run across this? If so, is there a known fix? I hadhoped Exchange 2003 would fix the problem. Most likely, we will trysetting up a personal distribution list under the account from whichthe messages are sent. I just wanted to give one more shot to findingthe problem -- both because I would like to know and because beingable to string together addresses is a little easier than having to gothrough the person who can give us access to the account to set thePDL.Thanks.Jonathan LevyChicago Department of Public Health
View 1 Replies View RelatedAfter upgrading a server from SQL 2000 to SQL 2005, I can't get any SQL Agent jobs to run, giving logon failed messages. MSSQLSERVER and SQLSERVERAGENT services are set up to run as the same domain account which has administrative rights to the server and is a SQL login with the sysadmin role. For basic testing purposes, I created a test job that all it does is run a T-SQL command 'SELECT @@VERSION'. The job is also set to run as the same account that the Agent is running under. Below are the logs (with trace messages on) from the point of starting the Agent to my attempt to run the job (user and server names have been changed to protect the innocent):
2007-10-22 15:31:15 - ? [297] SQLServer Message: 15457, Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ? [297] SQLServer Message: 15457, Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ? [297] SQLServer Message: 15457, Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (DisableAgentXPs)
2007-10-22 15:31:15 - ? [100] Microsoft SQLServerAgent version 9.00.3042.00 (x86 unicode retail build) : Process ID 5168
2007-10-22 15:31:15 - ? [101] SQL Server TESTSERVER version 9.00.3042 (0 connection limit)
2007-10-22 15:31:15 - ? [102] SQL Server ODBC driver version 9.00.3042
2007-10-22 15:31:15 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
2007-10-22 15:31:15 - ? [310] 4 processor(s) and 3328 MB RAM detected
2007-10-22 15:31:15 - ? [339] Local computer is TESTSERVER running Windows NT 5.2 (3790) Service Pack 2
2007-10-22 15:31:15 - ? [431] Populating subsystems cache...
2007-10-22 15:31:15 - ? [432] There are 11 subsystems in the subsystems cache
2007-10-22 15:31:15 - ? [124] Subsystem 'TSQL' successfully loaded (maximum concurrency: 80)
2007-10-22 15:31:16 - ? [124] Subsystem 'ActiveScripting' successfully loaded (maximum concurrency: 40)
2007-10-22 15:31:16 - ? [124] Subsystem 'CmdExec' successfully loaded (maximum concurrency: 40)
2007-10-22 15:31:16 - ? [124] Subsystem 'Snapshot' successfully loaded (maximum concurrency: 400)
2007-10-22 15:31:16 - ? [124] Subsystem 'LogReader' successfully loaded (maximum concurrency: 100)
2007-10-22 15:31:16 - ? [124] Subsystem 'Distribution' successfully loaded (maximum concurrency: 400)
2007-10-22 15:31:16 - ? [124] Subsystem 'Merge' successfully loaded (maximum concurrency: 400)
2007-10-22 15:31:16 - ? [124] Subsystem 'QueueReader' successfully loaded (maximum concurrency: 400)
2007-10-22 15:31:16 - ? [124] Subsystem 'ANALYSISQUERY' successfully loaded (maximum concurrency: 400)
2007-10-22 15:31:16 - ? [124] Subsystem 'ANALYSISCOMMAND' successfully loaded (maximum concurrency: 400)
2007-10-22 15:31:16 - ? [124] Subsystem 'SSIS' successfully loaded (maximum concurrency: 400)
2007-10-22 15:31:16 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2007-10-22 15:31:16 - + [260] Unable to start mail session (reason: No mail profile defined)
2007-10-22 15:31:16 - ? [174] Job scheduler engine started (maximum worker threads: 400)
2007-10-22 15:31:16 - ? [193] Alert engine started (using Eventlog Events)
2007-10-22 15:31:16 - ? [146] Request servicer engine started
2007-10-22 15:31:16 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
2007-10-22 15:31:16 - ? [110] Starting SQLServerAgent Monitor using '' as the notification recipient...
2007-10-22 15:31:16 - ? [133] Support engine started
2007-10-22 15:31:16 - ? [167] Populating job cache...
2007-10-22 15:31:16 - ? [297] SQLServer Message: 0, SQLServerAgent Monitor started successfully. [SQLSTATE 01000]
2007-10-22 15:31:16 - ? [168] There are 2 job(s) [0 disabled] in the job cache
2007-10-22 15:31:16 - ? [170] Populating alert cache...
2007-10-22 15:31:16 - ? [171] There are 0 alert(s) in the alert cache
2007-10-22 15:32:59 - ? [177] Job Test1 has been requested to run by User DOMAINmyaccount
2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp)
2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp)
2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp)
2007-10-22 15:32:59 - ? [184] Job completion for Test1 is being logged to sysjobhistory
2007-10-22 15:32:59 - ! [382] Logon to server 'TESTSERVER' failed (ConnAttemptCachableOp)
When I re-boot the machine, if the sql server is set to "automatic", will the service re-start when re-booting the machine?
View 1 Replies View RelatedI inherited a SQL 2012 Ent server sitting on a 2008R2 server using AlwaysOn High Availability, two nodes.
Available Mode: Synchronous commit
Failover Mode: Manual
Connection in Primary role: Allow all connections
Readable secondary: No
seesion timeout: 10
Somebody decided to give SQL server priority boost so I need to change this ASAP. So I plan on doing the following.
1. Manually fail over to the secondary, which does not have the priority boost set to true
2. change the setting
3. restart the service
4. Manually fail over
My question is with the service restart. How does SQL handle if the DB changes on the new primary while the secondary is having the service restarted. Where can I see if the DB are sync again or if not where are they in the sync process.
As part of an ETL process which takes several days I need to build in an auto pause on processing with no set time for restarting it again. So we cannot use the script..WAITFOR DELAY '00:01:00'
Apparently the Loop is not an option as it uses too much CPU.We need to run a few manual checks and there is no way to determine the amount of time needed. After these check are done I need some way of resuming the package from where it stopped.
The obvious way probably is to build this into two separate packages but this would break the logical way our containers are being setup, in other words, it would make us break a container into two different parts in different packages.
Whenever I restart the Sql Server Agent, it causes SQL Server to restart also - is this correct ? I thought the dependency was the other way around i.e. restarting SQL Server would cause the agent to restart ? Any insights gratefully accepted.
View 5 Replies View RelatedI am attempting to install SQL Server Express 2012. Th setup support rule to "restart computer" continues to fail.
I tried to locate and change PendingFileRenameOperations under HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlSession Manager.
I am not able to locate PendingFileRenameOperations both when following the registry path and even searching at the HKEY levels.
Is there such a thing called 'Load balancing' on fail over cluster?
View 2 Replies View RelatedMy environment is a 64 bit Windows Server 2012 Standard edition that is running SQL Server 2012 SP1 Standard Edition (64 bit) which has an Active Passive Cluster set up and I unfortunately had no involvement in this cluster build. The person who did is no longer with the company and is not responsive to questions.
This morning this following errors popped up:
Event ID: 1230 - Cluster resource 'Databases' (resource type 'Physical Disk', DLL 'clusres.dll') did not respond to a request in a timely fashion. Cluster health detection will attempt to automatically recover by terminating the Resource Hosting Subsystem (RHS) process running this resource. This may affect other resources hosted in the same RHS process. The resources will then be restarted.
The suspect resource 'Databases' will be marked to run in an isolated RHS process to avoid impacting multiple resources in the event that this resource failure occurs again. Please ensure services, applications, or underlying infrastructure (such as storage or networking) associated with the suspect resource is functioning properly.
Event ID: 1146 - The cluster Resource Hosting Subsystem (RHS) stopped unexpectedly. An attempt will be made to restart it. This is usually associated with recovery of a crashed or deadlocked resource. Please determine which resource and resource DLL is causing the issue and verify it is functioning properly.
When I saw these errors, I brought the "Databases", "Logs", and "Quorom" disks back online and all was well. I then proceeded to check Application and System Logs noticing there where some VMWare/SAN errors that caused the CPU spike resulting in the Cluster errors.
Regarding the questions, why didn't a failover occur and What can I do to mitigate this process are now what I am focusing on. Researching the above errors, I discovered that you could a policy based what happens in a situation like occurred this morning. You can set policies at the Role level, "Server" level, and at the disk level.
The Role Policy seems to be a little less involved, screenshot RoleProperties.png show these. I assume these are defaults. The server name properties are set to the following screenshot, Servername_Properties_Policy.png. This seems to be correct to me, but there is where I am lacking. I think this means that if it fails at this level all associated resources, disks, etc will failover to node 2.Now where I think the issue is, the third screenshot, Database_Properties_Policy.png. There is no policy set to for situation where a disk resource fails. Which I believe is what happened this morning. Some questions:
1. Do I need to set this similar to what is in the ServerName screenshot?
2. What happens if I check the box for "If restart is unsuccessful, fail over all resources in the role"? Will this only fail over this one disk? If so, it doesn't seem like that would be ideal. Would it fail over at the Role level and move over all associated resources?
I am also not sure what time limits I should set for Period restarts, maximum restarts, delay between restarts, etc...
I have configured three transactional replication one from DC - Data center & DB for database
DC1-DB1 to DC2-DB1 -- 1
DC2-DB1 to DC2-DB2 --2
DC2-DB2 to -DC2-DB3 --3
Before synching the data on DC2 databases I wanted to truncate / drop the objects on 1 and 2 setup as these are already participating in replication. Setup 1 and 2 are publisher as well as subscriber. I am not able to reinitialize these tables on these setup. I cannot use SSIS.
I am running SQL Server 2012 on a VM and am trying to pull data from a network drive (S: or misvnascfs0003).I can import data using the Import Wizard and save the package as an SSIS package. When I try to Run the Package I get The Acquire Connection method call to the connection manager "SourceConnectionExcel" failed with the error...
If I save the file to the VM local drive the SSIS package run fine.When I log into SSIS through SSMS i have to run as "Administrator".
SQL 2012 Standard on Windows Server 2012 Standard
After observing brute force attacks on our VPS myhosting instance against the SA login, I change the SA login name. Now my two new backup jobs are failing. The agent service logs in as NT ServiceSQLSERVERAGENT. Nothing changed there (so far as I know and I'm the only one who should be on the VPS). The job owner was SA and after changing the SA account that was reflected in the SSMS gui; job owner 'newsaname'. I'm sure I can just rebuild the maintenance plans but I'd like to know what happened.
Also, I would like to learn more about the brute force attacks and how to determine what port they are comming in on. I see an IP address associated with them. Does that mean they are coming in on 1433 or 1434?
SQL 2012 Standard VPS Windows 2012 Server Standard
I have configured two node cluster environment on vmware with windows 2008 r2 os.I want to configure sql clustering over it.but prior to that I ran failover clustering manager on node V1 to create two node cluster(node v1,node v2 and third node as a domain controller with shared storage using iscsi target).After I successfully created cluster with name SqlClus1 I shut down all my vmware.Now when I start them all and see clustering status I get error RPC server not found and Cluster server can not be connected.On my cluster manager page I see cluster events marked as critical for both nodes.and cluster staus down.
View 2 Replies View RelatedEvery once in a while a scheduled restore of a production database backup to a development server will fail with the following error.
RESTORE cannot operate on database 'XXX' because it is configured for database mirroring or has joined an availability group
While it is true the production database is involved in database mirroring, the development server does not have database mirroring enabled. This error tells me something within the backup is telling the development server the database is configured for database mirroring.
However the perplexing part for me is that we only receive this error maybe 5% of the time, if that, and only on a couple of our databases. We have numerous other restores of mirrored production databases to development servers that have never produced this error. So my question is what is causing this error to occur, and why is it not happening all of the time? We get around this error by deleting the DEV database and re-running the restore job.
The import from Flat File Source fails: Error 0xc02020a1: Data Flow Task 1: Data conversion failed.
The data conversion for column "ArticleName" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
(SQL Server Import and Export Wizard)
I have changed the size of the column "ArticleName" (varchar) to max but the error comes up again.
The data i want to import came with multiple flat files. They all could import properly but this one is a problem.
I have a database with a table called RAW, this table receives msgs via XML, sms and various other formats. The data is written into this table at a rate of approximately 50-100 rows per minute.
I have a SP which takes the data written into the raw table and performs various actions looks for account information, writes to a log table, writes to incident table’s gets GPS information and so on.
The records written into the raw table need to be processed at almost instantly with a maximum of under a minute from when they arrive into the table.
At present there is a sql agent job which executes the SP, this consist of a step to execute the SP which on completion moves to the next step which is a loop waitfor delay and then back to step 1.
The trouble is it never actually finishes and runs 24/7 there is no break point for error handling and occasionally the records that arrive in the raw table do not get processed and the job has to be restarted for it to pick them up again.
I am looking for best way to handle this process, I thought about a trigger but the performance impact on using a trigger was too heavy to consider.
As my sql agent is not starting due to XP's are disabled . when I ran the sp_configure with override and changed to 0 to 1 when I am starting the agent the configured value will be back to again 0 and in logs.Waiting for SQL Server to recover database 'msdb'...ODBC Error: 0, Invalid character value for cast specification [SQLSTATE 22018]
View 1 Replies View RelatedI want to give developers access to view SQL Job history, but not let them inhert permission to create local jobs like they get with the fixed server roles.
how can this be achieved? Or can it only be achieved by giving them access to the tables in msdb and querying them?
When I start SQL Server Agent it's shutting down minute after starting. I tried to set Alerts to send email. After I configured Database Mail ,I was able to send test email , however after that SQL Server Agent started to misbehave.
View 3 Replies View RelatedWhat sort of things would it interact with to justify having this ticked as the sqlserver.exe seems to be consuming all the CPU constantly and some suggestions have said that having this ticked can cause a performance issue.
View 3 Replies View RelatedUsing merge replication + web synchronization, I have a situation when there are large amount of data changes to upload to the publisher, Merge agent would create a large request and send it over. The publisher gets it and is able to work on it. After few minutes it has finished but (I assume) the connection has been dropped. At the subscriber's side, it appears that the merge agent is hung. The output would look like something like this:
Upload request size is XXX bytes.
Uploaded a total of 100 chunks.
Uploaded a total of 200 chunks.
Uploaded a total of 211 chunks.
The request message was sent to [URL] ....
Normally, when the publisher finishes working, the merge agent then continues processing. But when it takes more than few minutes (it seems to break about at 2 or 3 minutes), merge agent will hang as long as the InternetTimeout setting is (currently 20 minutes) before finally failing and retrying.
But that's not right. The publisher was done and can't communicate back to the merge agent (presumably because the connection was dropped). As a result, merge agent will try to re-enumerate changes on top of giving appearance that it's hung.
I've already fiddled with settings such as MaxUploadChanges, UploadGenerationsPerBatch, UploadReadChangesPerBatch, and UploadWriteChangesPerBatch. However, none of those setting actually ensure that the request message is too large. It has worked in breaking up the changes into separate batches (e.g. processing a single table rather than all tables) which results in more frequent updates and thus avoid the problem.
However, when a single table has several changes, it is still lumped into one large request which then takes more than 2-3 minutes to process on publisher's side and thus I still end up with the same symptom of merge agent hanging.
Is there anything else I could try to get merge agent to keep its connection alive even during processing a large request?
I have an execute process task in SSIS that executes a .Net program.When this is running as a Sql Server Agent job in Sql Server 2005, I have no issues. The operating system of the server is Windows Server 2003 R2.
Now I'm trying to get this working as a Sql Server Agent job in Sql Server 2012. The operating system of the server is Windows Server 2012 R2. And now I have issues.
If I run the SSIS package in Visual Studio debug mode, I have no issues! It's only when I try running the same package from a Sql Server Agent job that there are problems. The .Net program errors out with a System.TypeInitializationException.
I thought this might be an issue running the .Net program as 64 bit so I changed the agent setting to 32 bit. That didn't work. I thought it might be an issue with authorization (since I can run it in debug with me as the user). I put in a class in the program that changes the user through impersonation. That didn't work either.
Since I don't have a full-blown version of Visual Studio on the database server, I haven't been able to capture the error and debug the program to see where it is blowing up.
why something like this would work in Sql Server 2005/Windows 2003 but not in Sql Server 2012/Windows 2012 when run as an agent job? And on top of that, why would it work when I run it manually in debug mode but not when run as an agent job?
To set the scene I am using SQL 2012, in project deployment mode (SSIS Catalog rather than file system).I have setup an SSIS package to run a stored procedure which exports data for the last hour to a .tsv file and then FTP's the file to some other location via a sql agent job - This all works fine.However, I can see there may be a requirement to run the package with dates that need to be set i.e. in the event of a lost file of some other reason the package has not run and missed some of its hourly slots and the customer requires the files to be resent.
The stored procedure I am using has parameters for "DateOverride" - boolean), "start" and "end" dates (datetime) with defaults set "0" for "DateOverride" and null for the "Start" and "End" dates, I have built logic into the procedure which sets the dates if the parameters are null (as in the above to an hour before now). What I would like to be able to do (and this is to make it user friendly for support staff) is to be able to set parameters/variables in SQL agent with "DateOverride" set to "1" and the the dates I would like to be sent to the stored procedure "Start" and "End" parameters.
I did try using the parameters in SSIS which worked well when the values were true or false (0,1) but didn't work at all for the dates. If I left the dates as I had set them is SSIS it worked, but if I changed them (even if it was just changing the hour) the job errored/crashed and corrupted the job step leaving me the ability to only delete it.
In one of my environments, I need to grant the ability to view all the logins and agent jobs to an account, but I don't want to give him "sysadmin" or "securityadmin".
View 2 Replies View RelatedWe have a large number of SSISDB packages running happily, connecting to our SQL Servers using ADO.Net or Sql Native Client, making their connection using NTLM. (We don't have our SQL Server SPNs correctly configured to support Kerberos).
The SSISDB packages are hosted on and run on a dedicated SQL server, different to the SQL Servers they are connecting to.
Very occasionally, the connection attempt is made using Kerberos instead of NTLM, and the connection attempt to sql server fails. (This is going by the Windows Security event log, which reveals a Kerberos login - a successful one at the Windows level - at the precise time that the calling agent job is informed of a connection timeout and fails, approx 23 seconds after the job starts).
The correct configuration of our SPNs is something we may wish to look into for security best practice, and would of course fix this. However, that may not be my decision to make.