none
Uma conexão com o servidor foi estabelecida com êxito, mas ocorreu um erro durante o handshake de pré-logon RRS feed

  • Pergunta

  • Boa noite senhores. Estou com um problemão por aqui. 

    Estava eu tentando configurar meu sql para ser acessado remotamente porém fiz uma alteração que agora não consigo mais entrar no meu banco. 

    A única alteração que fiz foi habilitar o protocolo TCP/IP no:

    SQL Server 2014 Configurator Manager > Sql Server Network Configuration > Protocols for SQLEXPRESS e habilitei a opção TCP/IP. 

    Feito isso tentei conectar no banco no servidor mesmo e me exibe esse erro, fiquei desesperado então desabilitei a opção novamente porem me continuou apresentando o erro. Estou um pouco desesperado pois como mesmo desabilitando a opção novamente, do jeito que estava ele continua não abrindo? Já reiniciei o servidor sem exito. 

    Se alguem puder me ajudar ficarei imensamente grato.

    Edit: Utilizo o sql server express 2014 e uso o windows authentication para logar, preciso apenas que volte como estava, depois pensamos em uma forma de acessar remotamente.

    Edit 2: Lembrei que eu setei o numero maximo de conexões de 0 para 2 também.

    Right click on SQL instance --> Properties --> Connections --> "Set the Maximum number of 0. concurrent connections to '2' ".

    Edit 3: Abri o meu Log de erro e realmente parece algo com esse numero de conexoes. Mas como posso alterar se não consigo logar no sql, não consigo ver minha instância (talvez hora aperce um ip hora localmachine pois estou tentando de todas as formas): 

    2018-10-01 06:30:36.23 Server      Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
    Feb 20 2014 20:04:26 
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

    2018-10-01 06:30:36.23 Server      UTC adjustment: -3:00
    2018-10-01 06:30:36.23 Server      (c) Microsoft Corporation.
    2018-10-01 06:30:36.23 Server      All rights reserved.
    2018-10-01 06:30:36.23 Server      Server process ID is 8492.
    2018-10-01 06:30:36.23 Server      System Manufacturer: 'Positivo Informatica SA', System Model: 'POS-EIB75CO'.
    2018-10-01 06:30:36.24 Server      Authentication mode is WINDOWS-ONLY.
    2018-10-01 06:30:36.24 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Log\ERRORLOG'.
    2018-10-01 06:30:36.24 Server      The service account is 'WORKGROUP\WIN-72M5EJ82M9V$'. This is an informational message; no user action is required.
    2018-10-01 06:30:36.24 Server      Registry startup parameters: 
    -d C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\master.mdf
    -e C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Log\ERRORLOG
    -l C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\mastlog.ldf
    2018-10-01 06:30:36.24 Server      Command Line Startup Parameters:
    -s "SQLEXPRESS"
    2018-10-01 06:30:36.46 Server      SQL Server detected 1 sockets with 2 cores per socket and 2 logical processors per socket, 2 total logical processors; using 2 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
    2018-10-01 06:30:36.46 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2018-10-01 06:30:36.46 Server      Detected 7620 MB of RAM. This is an informational message; no user action is required.
    2018-10-01 06:30:36.46 Server      Using conventional memory in the memory manager.
    2018-10-01 06:30:36.47 Server      Default collation: Latin1_General_CI_AS (us_english 1033)
    2018-10-01 06:30:36.49 Server      Query Store settings initialized with enabled = 1, 
    2018-10-01 06:30:36.49 Server      This instance of SQL Server last reported using a process ID of 8952 at 01/10/2018 06:30:26 (local) 01/10/2018 09:30:26 (UTC). This is an informational message only; no user action is required.
    2018-10-01 06:30:36.49 Server      Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
    2018-10-01 06:30:36.50 Server      The maximum number of dedicated administrator connections for this instance is '1'
    2018-10-01 06:30:36.50 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.
    2018-10-01 06:30:36.50 Server      Software Usage Metrics is disabled.
    2018-10-01 06:30:36.51 spid7s      Starting up database 'master'.
    2018-10-01 06:30:36.60 Server      CLR version v4.0.30319 loaded.
    2018-10-01 06:30:36.63 spid7s      SQL Server Audit is starting the audits. This is an informational message. No user action is required.
    2018-10-01 06:30:36.63 spid7s      SQL Server Audit has started the audits. This is an informational message. No user action is required.
    2018-10-01 06:30:36.64 spid7s      SQL Trace ID 1 was started by login "sa".
    2018-10-01 06:30:36.64 spid7s      Server name is 'WIN-72M5EJ82M9V\SQLEXPRESS'. This is an informational message only. No user action is required.
    2018-10-01 06:30:36.65 spid15s     The certificate [Cert Hash(sha1) "B5306DB337B867B1A699C3309FAD327E57245008"] was successfully loaded for encryption.
    2018-10-01 06:30:36.65 spid15s     Server is listening on [ 'any' <ipv6> 49178].
    2018-10-01 06:30:36.65 spid15s     Server is listening on [ 'any' <ipv6> 50659].
    2018-10-01 06:30:36.65 spid15s     Server is listening on [ 'any' <ipv4> 50659].
    2018-10-01 06:30:36.65 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQLEXPRESS ].
    2018-10-01 06:30:36.65 spid15s     Server local connection provider is ready to accept connection on [ \\.\pipe\MSSQL$SQLEXPRESS\sql\query ].
    2018-10-01 06:30:36.66 Server      Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
    2018-10-01 06:30:36.66 spid15s     Dedicated administrator connection support was not started because it is disabled on this edition of SQL Server. If you want to use a dedicated administrator connection, restart SQL Server using the trace flag 7806. This is an informational message only. No user action is required.
    2018-10-01 06:30:36.67 spid15s     SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2018-10-01 06:30:36.67 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.
    2018-10-01 06:30:36.67 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/WIN-72M5EJ82M9V:SQLEXPRESS ] for the SQL Server service. Windows return code: 0xffffffff, state: 53. 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.
    2018-10-01 06:30:36.67 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) [ MSSQLSvc/WIN-72M5EJ82M9V:50659 ] for the SQL Server service. Windows return code: 0xffffffff, state: 53. 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.
    2018-10-01 06:30:36.84 spid16s     A new instance of the full-text filter daemon host process has been successfully started.
    2018-10-01 06:30:37.09 spid12s     Starting up database 'mssqlsystemresource'.
    2018-10-01 06:30:37.09 spid19s     Starting up database 'msdb'.
    2018-10-01 06:30:37.10 spid21s     Starting up database 'GridWorX'.
    2018-10-01 06:30:37.10 spid20s     Starting up database 'FwxServer'.
    2018-10-01 06:30:37.11 spid12s     The resource database build version is 12.00.2000. This is an informational message only. No user action is required.
    2018-10-01 06:30:37.39 spid52      Starting up database 'AwxServer'.
    2018-10-01 06:30:37.48 spid12s     Starting up database 'model'.
    2018-10-01 06:30:37.56 spid12s     Clearing tempdb database.
    2018-10-01 06:30:37.59 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:37.59 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:37.64 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:37.64 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:37.65 spid12s     Starting up database 'tempdb'.
    2018-10-01 06:30:37.76 spid22s     The Service Broker endpoint is in disabled or stopped state.
    2018-10-01 06:30:37.76 spid22s     The Database Mirroring endpoint is in disabled or stopped state.
    2018-10-01 06:30:37.87 spid22s     Service Broker manager has started.
    2018-10-01 06:30:37.90 spid7s      Recovery is complete. This is an informational message only. No user action is required.
    2018-10-01 06:30:38.59 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:38.59 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:38.64 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:38.64 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:39.60 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:39.60 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:39.65 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:39.65 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:39.65 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:39.65 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]
    2018-10-01 06:30:39.73 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:39.73 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]
    2018-10-01 06:30:39.79 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:39.79 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]
    2018-10-01 06:30:39.93 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:39.93 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]
    2018-10-01 06:30:40.60 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:40.60 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:40.62 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:40.62 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]
    2018-10-01 06:30:40.62 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:40.62 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]
    2018-10-01 06:30:40.65 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:40.65 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:41.04 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:41.04 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]
    2018-10-01 06:30:41.60 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:41.60 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:41.65 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:41.65 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:42.51 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:42.51 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]
    2018-10-01 06:30:42.60 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:42.60 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:42.65 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:42.65 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:43.61 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:43.61 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:43.65 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:43.65 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:44.37 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:44.37 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 127.0.0.1]
    2018-10-01 06:30:44.51 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:44.51 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:44.61 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:44.61 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:44.62 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:44.62 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:44.76 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:44.76 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:44.82 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:44.82 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:44.86 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:44.86 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:45.06 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:45.06 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:45.22 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:45.22 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:45.46 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:45.46 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:45.61 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:45.61 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:45.82 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:45.82 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]
    2018-10-01 06:30:46.02 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:46.02 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:46.26 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:46.26 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:46.61 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:46.61 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]
    2018-10-01 06:30:46.63 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:46.63 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:47.03 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:47.03 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:47.27 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:47.27 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:47.45 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:47.45 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]
    2018-10-01 06:30:48.03 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:48.03 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:48.27 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:48.27 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]
    2018-10-01 06:30:49.03 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 06:30:49.03 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: 192.168.56.1]

    segunda-feira, 1 de outubro de 2018 00:45

Respostas

  • Rewsolvi o problema logando por DAC no prompt e alterei o numero de conexões no sp_configure. Porém para entrar por DAC no cmdm foi preciso adicionar o código -T7806 no Startup Parameters do serviço do SQL SERVER.
    segunda-feira, 1 de outubro de 2018 16:50
  • William,

    Um detalhe, se você estiver trabalhando com o SQL Server 2014 RTM (versão sem service pack aplicado), trata-se de um bug que foi corrigido após o lançamento do SP1, sendo assim, aplique o mesmo.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 3 de outubro de 2018 12:53

Todas as Respostas

  • Achei um link aqui me parece que é o mesmo problema que o meu: 

    https://blogs.msdn.microsoft.com/dataaccesstechnologies/2015/07/21/error-while-connecting-to-sql-server-could-not-connect-because-the-maximum-number-of-1-user-connections-has-already-been-reached/ 

    porém quando tento executar essa linha de comando: 

    "C:\Program Files\Microsoft SQL Server\MSSQLXX.MSSQLSERVER\MSSQL\Binn\sqlservr.exe" -sMSSQLSERVER -mSQLCMD –c 

    para fechar as conexões o cmd me retorna um erro: 

    seu eu abro o cmd com usuário do windows: 

    2018-10-01 07:23:33.60 Server      Error: 17058, Severity: 16, State: 1.
    2018-10-01 07:23:33.60 Server      initerrlog: Could not open error log file ''.
     Operating system error = 3(O sistema não pode encontrar o caminho especificado.
    ).

    se eu abro o cmd como administrador me retorna esse erro: 

    2018-10-01 07:31:28.97 Server      initerrlog: Could not open error log file
    \Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\Log\ERRORLOG'.
    ating system error = 32(O arquivo já está sendo usado por outro processo.).

    segunda-feira, 1 de outubro de 2018 10:50
  • Se eu fizer um "repair" no banco, eu perco todos os dados e databases nele contido?

    Edit: acredito que o repair não me atenderá, correto?

    segunda-feira, 1 de outubro de 2018 12:01
  • Na verdade esse ip 192.168.56.1 não sei de onde é. O que fiz, deixei somente o shared memory habilitado. O named pipes e o TCP/IP eu deixei desabilitado como estava antes. O log do erro aparece somente <named pipes> ou <local machine> nas conexões do log de erro: 

    2018-10-01 10:41:16.85 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:16.85 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]
    2018-10-01 10:41:16.85 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:16.85 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]
    2018-10-01 10:41:16.85 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:16.85 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <local machine>]
    2018-10-01 10:41:16.90 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:16.90 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:16.94 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:16.94 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:17.08 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:17.08 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:17.16 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:17.16 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:17.29 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:17.29 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:17.50 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:17.50 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:17.63 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:17.63 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:17.66 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:17.66 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:17.80 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:17.80 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:17.90 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:17.90 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:17.94 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:17.94 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:18.08 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:18.08 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:18.17 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:18.17 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:18.29 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:18.29 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:18.50 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:18.50 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:18.63 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:18.63 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:18.66 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:18.66 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:18.83 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:18.83 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:18.90 Logon       Error: 17809, Severity: 20, State: 3.
    2018-10-01 10:41:18.90 Logon       Could not connect because the maximum number of '2' user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT: <named pipe>]
    2018-10-01 10:41:18.95 Logon       Error: 17809, Severity: 20, State: 3.

    segunda-feira, 1 de outubro de 2018 13:45
  • Pelo que vi por aqui senhores, preciso editar o SP_CONFIGURE e alterar o numero de conexão de 2 para 0. Porém como editar esse SP_CONFIGURE, se eu conseguisse conectar na minha instancia eu conseguiria alterar via SSMS , mas não consigo conectar a minha instância.

    Edit: Estou tentando entrar pelo sqlcmd com esse comando: 

    sqlcmd -E -S SQLEXPRESS 

    porém é me retornado esse errro: 

    Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Named Pipes Provider: C
    ould not open a connection to SQL Server [53]. .
    Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
    Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A network-related or in
    stance-specific error has occurred while establishing a connection to SQL Server
    . Server is not found or not accessible. Check if instance name is correct and i
    f SQL Server is configured to allow remote connections. For more information see
     SQL Server Books Online..

    segunda-feira, 1 de outubro de 2018 14:01
  • Rewsolvi o problema logando por DAC no prompt e alterei o numero de conexões no sp_configure. Porém para entrar por DAC no cmdm foi preciso adicionar o código -T7806 no Startup Parameters do serviço do SQL SERVER.
    segunda-feira, 1 de outubro de 2018 16:50
  • William,

    Um detalhe, se você estiver trabalhando com o SQL Server 2014 RTM (versão sem service pack aplicado), trata-se de um bug que foi corrigido após o lançamento do SP1, sendo assim, aplique o mesmo.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 3 de outubro de 2018 12:53