While playing with my multiple SQL instances, I realized that I was not able to start one of the SQL instance on my laptop. Here is the error which I received when I tried starting it from Start > Run > Servics.msc. This error is related to TCPprovider.

windows could not start the SQL Server (mssqlSERVER) on Local Computer. For more information, review the System Event Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific error code 10048.

I opened ERRORLOG file and found below message.

2016-12-07 05:39:05.52 spid11s Server is listening on [ ‘any’ 51823].
2016-12-07 05:39:05.52 spid11s Error: 26023, Severity: 16, State: 1.
2016-12-07 05:39:05.52 spid11s Server TCP provider failed to listen on [ ‘any’ 51823]. Tcp port is already in use.

2016-12-07 05:39:05.52 spid11s Error: 17182, Severity: 16, State: 1.

2016-12-07 05:39:05.52 spid11s TDSSNIClient initialization failed with error 0x2740, status code 0xa. Reason: Unable to initialize the TCP/IP listener. Only one usage of each socket address (protocol/network address/port) is normally permitted.

2016-12-07 05:39:05.52 spid11s Error: 17120, Severity: 16, State: 1.

2016-12-07 05:39:05.52 spid11s SQL Server could not spawn FRunCommunicationsManager thread. Check the SQL Server error log and the Windows event logs for information about possible related problems.

Application event logs show below message.

Log Name: Application
Source: MSSQLSERVER
Date: 12/7/2016 5:38:18 AM
Event ID: 26023
Task Category: Server
Level: Error
Keywords: Classic
User: N/A
Computer: sqlserver2016
Description:
Server TCP provider failed to listen on [ ‘any’ 51823]. Tcp port is already in use.
SOLUTION/WORKAROUND

By looking at error messages above, we know that SQL is trying to start on post 51823 and someone else is already using that port. We have two choices at this point.

Find the process which is using 51823 ports and stop that process. Change port of SQL Server to a port which is not used by any other process.

To find out details about port usage, I generally use a free tool called TCPView . This utility is a sysinternals tool which gives us the information we need to fix this issue. Just start it, pause the data view, and look at which process is using the local port with the TCP protocol. As we can see below PID 3724 is using “Local Port” 51823 which is there in error message.


SQL SERVER   Unable to Start SQL Service   Server TCP provider failed to liste ...

Once we identified know the process that is already using that port, we can take the appropriate action.

If you are not allowed to think in above direction, then you can always change the port in which your SQL Server instance is listening on, but I’d personally be extremely curious as to which process is already using that port.

Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)

Have you seen such behavior and how did you fix it? Please comment and let me know and share with other blog reader.

Reference: Pinal Dave ( http://blog.sqlauthority.com )

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLWindowsSQL ServerUTTI
分页:12
转载请注明
本文标题:SQL SERVER Unable to Start SQL Service Server TCP provider failed to liste ...
本站链接:http://www.codesec.net/view/522308.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(45)