Skip to content

Archive for

SQL Server agent service failing with errors

SQL Server agent service may fail with following errors and you may find below errors in SQL Server agent log file:-
2011-10-26 10:58:10 - ! [298] SQLServer Error: 10061, TCP Provider: No connection could be made because the target machine actively refused it. [SQLSTATE 08001]</div>
<div>2011-10-26 10:58:10 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]</div>
<div>2011-10-26 10:58:10 - ! [298] SQLServer Error: 10061, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]</div>
<div>2011-10-26 10:58:10 - ! [000] Unable to connect to server 'MSSQLSERVER\TEST'; SQLServerAgent cannot start</div>
<div>2011-10-26 10:58:15 - ! [298] SQLServer Error: 10061, TCP Provider: No connection could be made because the target machine actively refused it. [SQLSTATE 08001]</div>
<div>2011-10-26 10:58:15 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]</div>
<div>2011-10-26 10:58:15 - ! [298] SQLServer Error: 10061, An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. [SQLSTATE 08001]</div>
<div>2011-10-26 10:58:15 - ! [382] Logon to server 'MSSQLSERVER\TEST' failed (DisableAgentXPs)</div>
<div>2011-10-26 10:58:16 - ? [098] SQLServerAgent terminated (normally)
SQL Server agent may not start and will fail with above errors after you have changed the port number for SQL Server. One of the reson for this failure is that you already have a alias created for this instance and it is using the old port number.
In order to fix this issue, go to SQL Server configuration Manager –> SQL Native Client Configuration –> Aliases. Here check the properties of alias created for the instance and make sure the port number is correct, else correct the same and try to start SQL Server agent srevice again. It will start ithout issues.
So, bottom line is to make sure that port number being used by the instance mentioned under TCP/IP and in alias is same. Else SQL Server agent will not start and will just fail.

Who changed SQL Server database state to OFFLINE?

To check, who changed the SQL Server database status to OFFLINE, please follow the below steps.

1) Connect to SQL Server → open SQL Server logs and scan through the logs. You will find entry like:
Date 9/6/2012 10:54:54 AM
Log SQL Server (Current – 9/1/2012 12:30:00 PM)
Source spid61
Message
Setting database option OFFLINE to ON for database test21.

This mean, test21 database was changed to OFFLINE on 9/6/2012 @ 10:54:54 AM . Also, we know this change was done by spid 61 at that time.

2) Now go to Windows event viewer (under Administrative Tools) and open Application logs. Here try to look for entries by Source = MSSQLSERVER around 9/6/2012 10:54:54 AM timeframe (This step will be done by logging in to the server by making remote desktop connection)

If windows authenticated account changed the database state then you will find that account here against User. If the change was made by sql authenticated account then it will show N/A.

Proceed to next step if it says N/A.

3) Now we know the spid, timeframe when this change was made. Also, we know the change was made by sql authenticated account. Now, run the below script by changing spid value and starttime to correct value.

DECLARE @FileName VARCHAR(MAX)

SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'
 FROM sys.traces
 WHERE is_default = 1;

SELECT DatabaseID, HostName, ApplicationName, LoginName, DatabaseName
 FROM sys.fn_trace_gettable( @FileName, DEFAULT )
 where starttime = '2012-09-06 10:54:55.117'
 and spid=61

Output:-

DatabaseID HostName ApplicationName LoginName DatabaseName
23 NHYUJM987123456 Microsoft SQL Server Management Studio DM\USER123 test21
23 NHYUJM987123456 Microsoft SQL Server Management Studio DM\USER123 NULL

From output you can see that this change was made by use DM\USER123.

Please note, this step will work only if default trace is enabled and the trace file is still available for the timeframe when database status was changed.

Additionally, you can follow above steps to check who changed database state to ONLINE, READ-ONLY, etc.