Connexions DAC et TCP Impossibles

Bonjour,
Impossible de me connecter à mon instance locale SQL SERVER 2008.
Mes différentes tentatives de configuration dans "Sql server configuration Manager" sont sans succès !
1 ) SSMS renvoie cette erreur lors de la tentive de connexion :

Citation:

---

Une connexion a été établie avec le serveur,
mais une erreur s'est ensuite produite pendant le processus d'ouverture de session.
(provider: Fournisseur TCP, error: 0 - Une connexion existante a dû être fermée par l'hôte distant.) (.Net SqlClient Data Provider)
------------------------------
Pour obtenir de l'aide, cliquez sur*: http://go.microsoft.com/fwlink?ProdN...4&LinkId=20476

---

l'URL de la page de l'aide est erroné ! Merci MS :oops:

2 ) Dans le fichier ERRORLOG j'ai ceci :

Citation:

---

. The system administrator can use sp_configure to increase the maximum value. The connection has been closed. [CLIENT*: 127.0.0.1]
2010-06-01 14:11:36.40 Erreur*: 17809, Gravité*: 20, État*: 3.
2010-06-01 14:11:36.40 Could not connect because the maximum number of '1' 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]
2010-06-01 14:11:41.44 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 14:11:41.49 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 14:11:46.54 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 14:11:46.60 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 14:11:51.65 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 14:11:51.69 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 14:11:56.74 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 14:11:56.79 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 14:12:01.84 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 14:12:01.88 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 14:12:06.94 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 14:12:06.98 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 14:12:12.04 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 14:12:12.10 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 14:12:17.18 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 14:12:17.27 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 14:12:22.34 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 14:12:22.38 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 14:12:27.43 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 14:12:27.49 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 14:12:32.59 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 14:12:32.63 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 14:12:37.66 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 14:12:37.69 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 14:12:37.69 Erreur*: 17809, Gravité*: 20, État*: 3.
2010-06-01 14:12:37.69 Could not connect because the maximum number of '1' 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]
2010-06-01 14:12:37.69 Erreur*: 17809, Gravité*: 20, État*: 3.
2010-06-01 14:12:37.69 Could not connect because the maximum number of '1' 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]
2010-06-01 14:12:42.74 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.

---

Merci de votre aide

Le scheduler pour DAC n'autorise qu'une seule connexion active, avec très peu de mémoire allouée, parce que DAC est sensé être utilisé que sous forte pression mémoire. Il doit déjà y avoir une connexion dessus, regarde quelle session est connectée au scheduler 255 dans sys.dm_exec_requests.

David B.

Citation:

---

Envoyé par dbaffaleuf

Le scheduler pour DAC n'autorise qu'une seule connexion active, avec très peu de mémoire allouée, parce que DAC est sensé être utilisé que sous forte pression mémoire.
David B.

---

hello David ;)
Justement je n'ai plus d'autre moyen pour me connecter sur l'instance !

Citation:

---

Il doit déjà y avoir une connexion dessus, regarde quelle session est connectée au scheduler 255 dans sys.dm_exec_requests.

---

comment savoir ? impossible de me connecter :
- en DAC (SQLCMD) !
- ou via SSMS !

Bonjour,

Question bête mais essaies tu de te connecter en local sur le serveur où depuis un poste client en DAC ?

++

Citation:

---

Envoyé par mikedavem

Bonjour,
Question bête mais essaies tu de te connecter en local sur le serveur où depuis un poste client en DAC ?
++

---

En local. l'instance est sur mon PC Portable.
Et je ne peux plus y accéder depuis ce matin. :cry:
Je suis entrain de penser utiliser l'outil de restauration systeme windows pour ramener mon PC dans son état d'il y a 2 jours (où tout marche bien).
Pensez-vous que ceci me donnera la main sur mon SQL SERVER ?

Merci

oula

Un simple stop/start de l'instance va dégager la connexion existante sur le DAC.

David B.

Citation:

---

Envoyé par dbaffaleuf

oula
Un simple stop/start de l'instance va dégager la connexion existante sur le DAC.
David B.

---

Je fais celà plusieurs fois. Et il n'y a aucun changement !
Même erreur renvoyée.

Et en redémarrant ton instance en mode mono utilisateur avec l'option -m ?

Code:

---

sqlservr.exe -m

---

++

Citation:

---

Envoyé par mikedavem

Et en redémarrant ton instance en mode mono utilisateur avec l'option -m ?

Code:

---

sqlservr.exe -m

---

++

---

Toujours les mêmes erreurs .
Ci-dessous une partie du log. Je vais restaurer le PC à J -3 pour voir...

Citation:

---

C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8INSTANCE2\MSSQL\Binn>sqlservr.exe -m -s SQL2K8INSTANCE2
2010-06-01 22:09:00.37 Server Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

2010-06-01 22:09:00.37 Server Error: 17054, Severity: 16, State: 1.
2010-06-01 22:09:00.37 Server The current event was not reported to the Windows Events log. Operating system error = 1502(Le fichier journal d'événem
ents est plein.). You may need to clear the Windows Events log if it is full.
2010-06-01 22:09:00.37 Server (c) 2005 Microsoft Corporation.
2010-06-01 22:09:00.37 Server All rights reserved.
2010-06-01 22:09:00.37 Server Server process ID is 5964.
2010-06-01 22:09:00.37 Server Authentication mode is MIXED.
2010-06-01 22:09:00.37 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8INSTANCE2\MSSQL\Log\ERRORLOG'.

2010-06-01 22:09:00.39 Server This instance of SQL Server last reported using a process ID of 1660 at 01/06/2010 22:08:34 (local) 01/06/2010 20:08:34
(UTC). This is an informational message only; no user action is required.
2010-06-01 22:09:00.39 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8INSTANCE2\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8INSTANCE2\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8INSTANCE2\MSSQL\DATA\mastlog.ldf
2010-06-01 22:09:00.40 Server Command Line Startup Parameters:
-m
-s SQL2K8INSTANCE2
SQL2K8INSTANCE2
2010-06-01 22:09:00.46 Serveur SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2010-06-01 22:09:00.46 Serveur Detected 2 CPUs. This is an informational message; no user action is required.
2010-06-01 22:09:01.34 Serveur Perfmon counters for resource governor pools and groups failed to initialize and are disabled.
2010-06-01 22:09:01.34 Serveur 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.
2010-06-01 22:09:01.51 Serveur Node configuration: node 0: CPU mask: 0x00000003 Active CPU mask: 0x00000003. This message provides a description of th
e NUMA configuration for this computer. This is an informational message only. No user action is required.
2010-06-01 22:09:01.56 Serveur Database Mirroring Transport is disabled in the endpoint configuration.
2010-06-01 22:09:01.57 spid7s Warning ******************
2010-06-01 22:09:01.57 spid7s SQL Server started in single-user mode. This an informational message only. No user action is required.
2010-06-01 22:09:01.57 spid7s Starting up database 'master'.
2010-06-01 22:09:01.68 spid7s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is req
uired.
2010-06-01 22:09:01.84 spid7s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2010-06-01 22:09:01.85 spid7s SQL Server Audit has started the audits. This is an informational message. No user action is required.
2010-06-01 22:09:01.87 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQL2K8INSTANCE2'.
2010-06-01 22:09:01.92 spid7s SQL Trace ID 1 was started by login "sa".
2010-06-01 22:09:01.92 spid7s Starting up database 'mssqlsystemresource'.
2010-06-01 22:09:01.96 spid7s The resource database build version is 10.00.1600. This is an informational message only. No user action is required.
2010-06-01 22:09:02.24 spid9s Starting up database 'model'.
2010-06-01 22:09:02.28 spid7s Server name is 'DPAW-ZINZINDOHU\SQL2K8INSTANCE2'. This is an informational message only. No user action is required.
2010-06-01 22:09:02.31 spid7s Information*: aucune langue prise en charge en texte intégral n'a été trouvée.
2010-06-01 22:09:02.32 spid10s Starting up database 'msdb'.
2010-06-01 22:09:02.32 spid12s Starting up database 'ReportServer$SQL2K8INSTANCE2'.
2010-06-01 22:09:02.32 spid13s Starting up database 'ReportServer$SQL2K8INSTANCE2TempDB'.
2010-06-01 22:09:02.34 spid14s Starting up database 'distribution'.
2010-06-01 22:09:03.15 Serveur A self-generated certificate was successfully loaded for encryption.
2010-06-01 22:09:03.31 Serveur Server is listening on [ 'any' <ipv4> 1558].
2010-06-01 22:09:03.34 Serveur Server is listening on [ 127.0.0.1 <ipv4> 4848].
2010-06-01 22:09:03.34 Serveur Dedicated admin connection support was established for listening locally on port 4848.
2010-06-01 22:09:03.46 Erreur*: 17187, Gravité*: 16, État*: 1.
2010-06-01 22:09:03.46 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*: 127.0.0.1]
2010-06-01 22:09:03.51 Erreur*: 17187, Gravité*: 16, État*: 1.
2010-06-01 22:09:03.51 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*: 127.0.0.1]
2010-06-01 22:09:04.10 spid9s Clearing tempdb database.
2010-06-01 22:09:04.64 spid12s 1 transactions rolled forward in database 'ReportServer$SQL2K8INSTANCE2' (5). This is an informational message only. No
user action is required.
2010-06-01 22:09:04.78 spid7s 0 transactions rolled back in database 'ReportServer$SQL2K8INSTANCE2' (5). This is an informational message only. No us
er action is required.
2010-06-01 22:09:04.78 spid7s Recovery is writing a checkpoint in database 'ReportServer$SQL2K8INSTANCE2' (5). This is an informational message only.
No user action is required.
2010-06-01 22:09:05.81 spid9s Starting up database 'tempdb'.
2010-06-01 22:09:05.93 spid7s Recovery is complete. This is an informational message only. No user action is required.
2010-06-01 22:09:06.01 spid7s Launched startup procedure 'sp_MSrepl_startup'.
2010-06-01 22:09:07.89 Serveur The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service
. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informationa
l message. Further action is only required if Kerberos authentication is required by authentication policies.
2010-06-01 22:09:07.90 Serveur SQL Server is now ready for client connections. This is an informational message; no user action is required.
2010-06-01 22:09:08.85 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 22:09:08.90 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 22:09:08.92 Erreur*: 17809, Gravité*: 20, État*: 3.
2010-06-01 22:09:08.92 Could not connect because the maximum number of '1' 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]
2010-06-01 22:09:08.92 Erreur*: 17809, Gravité*: 20, État*: 3.
2010-06-01 22:09:08.92 Could not connect because the maximum number of '1' 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]
2010-06-01 22:09:14.01 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 22:09:14.06 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 22:09:14.07 Erreur*: 17809, Gravité*: 20, État*: 3.
2010-06-01 22:09:14.07 Could not connect because the maximum number of '1' 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]
2010-06-01 22:09:14.07 Erreur*: 17809, Gravité*: 20, État*: 3.
2010-06-01 22:09:14.07 Could not connect because the maximum number of '1' 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]
2010-06-01 22:09:19.15 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 22:09:19.20 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 22:09:24.25 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 22:09:24.29 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 22:09:24.29 Erreur*: 17809, Gravité*: 20, État*: 3.
2010-06-01 22:09:24.29 Could not connect because the maximum number of '1' 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]
2010-06-01 22:09:24.31 Erreur*: 17809, Gravité*: 20, État*: 3.
2010-06-01 22:09:24.31 Could not connect because the maximum number of '1' 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]
2010-06-01 22:09:33.90 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 22:09:33.95 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 22:09:39.00 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 22:09:39.04 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 22:09:44.10 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 22:09:44.15 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 22:09:49.20 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 22:09:49.24 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 22:09:49.26 Erreur*: 17809, Gravité*: 20, État*: 3.
2010-06-01 22:09:49.26 Could not connect because the maximum number of '1' 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]
2010-06-01 22:09:49.26 Erreur*: 17809, Gravité*: 20, État*: 3.
2010-06-01 22:09:49.26 Could not connect because the maximum number of '1' 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]

---

Une dernière chose donc ...

Je soupçonne fortement un programme qui se connecte ou qui empêche toute connexion à ton instance ....
N'as tu pas un antivirus qui tourne sur ton environnement ? Si c'est le cas arrête le et réessaye de te connecter ..

++

Citation:

---

Envoyé par mikedavem

Une dernière chose donc ...
Je soupçonne fortement un programme qui se connecte ou qui empêche toute connexion à ton instance ....
N'as tu pas un antivirus qui tourne sur ton environnement ? Si c'est le cas arrête le et réessaye de te connecter ..
++

---

C'est toujours le statu quo après l'arrêt de l'antivirus

Citation:

---

C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8INSTANCE2\MSSQL\Binn>sqlservr.exe -m -s SQL2K8INSTANCE2
2010-06-01 22:54:47.51 Server Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

2010-06-01 22:54:47.53 Server Error: 17054, Severity: 16, State: 1.
2010-06-01 22:54:47.53 Server The current event was not reported to the Windows Events log. Operating system error = 1502(Le fichier journal d'événem
ents est plein.). You may need to clear the Windows Events log if it is full.
2010-06-01 22:54:47.53 Server (c) 2005 Microsoft Corporation.
2010-06-01 22:54:47.53 Server All rights reserved.
2010-06-01 22:54:47.53 Server Server process ID is 3692.
2010-06-01 22:54:47.53 Server Authentication mode is MIXED.
2010-06-01 22:54:47.53 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8INSTANCE2\MSSQL\Log\ERRORLOG'.

2010-06-01 22:54:47.53 Server This instance of SQL Server last reported using a process ID of 456 at 01/06/2010 22:54:27 (local) 01/06/2010 20:54:27
(UTC). This is an informational message only; no user action is required.
2010-06-01 22:54:47.54 Server Registry startup parameters:
-d C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8INSTANCE2\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8INSTANCE2\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2K8INSTANCE2\MSSQL\DATA\mastlog.ldf
2010-06-01 22:54:47.56 Server Command Line Startup Parameters:
-m
-s SQL2K8INSTANCE2
SQL2K8INSTANCE2
2010-06-01 22:54:47.62 Serveur SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

2010-06-01 22:54:47.62 Serveur Detected 2 CPUs. This is an informational message; no user action is required.
2010-06-01 22:54:48.48 Serveur Perfmon counters for resource governor pools and groups failed to initialize and are disabled.
2010-06-01 22:54:48.48 Serveur 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.
2010-06-01 22:54:48.65 Serveur Node configuration: node 0: CPU mask: 0x00000003 Active CPU mask: 0x00000003. This message provides a description of th
e NUMA configuration for this computer. This is an informational message only. No user action is required.
2010-06-01 22:54:48.68 Serveur Database Mirroring Transport is disabled in the endpoint configuration.
2010-06-01 22:54:48.70 spid7s Warning ******************
2010-06-01 22:54:48.70 spid7s SQL Server started in single-user mode. This an informational message only. No user action is required.
2010-06-01 22:54:48.70 spid7s Starting up database 'master'.
2010-06-01 22:54:49.71 spid7s 716 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is requ
ired.
2010-06-01 22:54:49.73 spid7s 0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required.

2010-06-01 22:54:49.73 spid7s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is req
uired.
2010-06-01 22:54:49.87 spid7s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2010-06-01 22:54:49.90 spid7s SQL Server Audit has started the audits. This is an informational message. No user action is required.
2010-06-01 22:54:49.90 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'SQL2K8INSTANCE2'.
2010-06-01 22:54:49.96 spid7s SQL Trace ID 1 was started by login "sa".
2010-06-01 22:54:49.98 spid7s Starting up database 'mssqlsystemresource'.
2010-06-01 22:54:50.01 spid7s The resource database build version is 10.00.1600. This is an informational message only. No user action is required.
2010-06-01 22:54:50.28 spid9s Starting up database 'model'.
2010-06-01 22:54:50.28 spid7s Server name is 'DPAW-ZINZINDOHU\SQL2K8INSTANCE2'. This is an informational message only. No user action is required.
2010-06-01 22:54:50.32 spid7s Information*: aucune langue prise en charge en texte intégral n'a été trouvée.
2010-06-01 22:54:50.34 spid10s Starting up database 'msdb'.
2010-06-01 22:54:50.34 spid12s Starting up database 'ReportServer$SQL2K8INSTANCE2'.
2010-06-01 22:54:50.34 spid13s Starting up database 'ReportServer$SQL2K8INSTANCE2TempDB'.
2010-06-01 22:54:50.37 spid14s Starting up database 'distribution'.
2010-06-01 22:54:51.28 Serveur A self-generated certificate was successfully loaded for encryption.
2010-06-01 22:54:51.31 Serveur Server is listening on [ 'any' <ipv4> 1558].
2010-06-01 22:54:51.31 Serveur Server is listening on [ 127.0.0.1 <ipv4> 4848].
2010-06-01 22:54:51.31 Serveur Dedicated admin connection support was established for listening locally on port 4848.
2010-06-01 22:54:51.57 spid16s 1 transactions rolled forward in database 'distribution' (9). This is an informational message only. No user action is
required.
2010-06-01 22:54:51.76 spid9s Clearing tempdb database.
2010-06-01 22:54:51.78 spid7s 0 transactions rolled back in database 'distribution' (9). This is an informational message only. No user action is req
uired.
2010-06-01 22:54:51.78 spid7s Recovery is writing a checkpoint in database 'distribution' (9). This is an informational message only. No user action
is required.
2010-06-01 22:54:51.89 spid15s 1 transactions rolled forward in database 'Logging_demo' (8). This is an informational message only. No user action is
required.
2010-06-01 22:54:51.99 spid14s 1 transactions rolled forward in database 'SNCF_BIS' (7). This is an informational message only. No user action is requ
ired.
2010-06-01 22:54:52.09 spid7s 0 transactions rolled back in database 'Logging_demo' (8). This is an informational message only. No user action is req
uired.
2010-06-01 22:54:52.09 spid7s Recovery is writing a checkpoint in database 'Logging_demo' (8). This is an informational message only. No user action
is required.
2010-06-01 22:54:52.29 spid7s 0 transactions rolled back in database 'SNCF_BIS' (7). This is an informational message only. No user action is require
d.
2010-06-01 22:54:52.29 spid7s Recovery is writing a checkpoint in database 'SNCF_BIS' (7). This is an informational message only. No user action is r
equired.
2010-06-01 22:54:52.48 spid13s 401 transactions rolled forward in database 'ReportServer$SQL2K8INSTANCE2TempDB' (6). This is an informational message
only. No user action is required.
2010-06-01 22:54:52.68 spid7s 0 transactions rolled back in database 'ReportServer$SQL2K8INSTANCE2TempDB' (6). This is an informational message only.
No user action is required.
2010-06-01 22:54:52.68 spid7s Recovery is writing a checkpoint in database 'ReportServer$SQL2K8INSTANCE2TempDB' (6). This is an informational message
only. No user action is required.
2010-06-01 22:54:52.68 spid7s Recovery completed for database ReportServer$SQL2K8INSTANCE2TempDB (database ID 6) in 1 second(s) (analysis 779 ms, red
o 544 ms, undo 165 ms.) This is an informational message only. No user action is required.
2010-06-01 22:54:52.92 spid12s 400 transactions rolled forward in database 'ReportServer$SQL2K8INSTANCE2' (5). This is an informational message only.
No user action is required.
2010-06-01 22:54:52.93 Erreur*: 17187, Gravité*: 16, État*: 1.
2010-06-01 22:54:52.93 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*: 127.0.0.1]
2010-06-01 22:54:52.95 Erreur*: 17187, Gravité*: 16, État*: 1.
2010-06-01 22:54:52.95 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*: 127.0.0.1]
2010-06-01 22:54:52.98 spid7s 0 transactions rolled back in database 'ReportServer$SQL2K8INSTANCE2' (5). This is an informational message only. No us
er action is required.
2010-06-01 22:54:53.01 spid7s Recovery is writing a checkpoint in database 'ReportServer$SQL2K8INSTANCE2' (5). This is an informational message only.
No user action is required.
2010-06-01 22:54:54.23 spid10s 346 transactions rolled forward in database 'msdb' (4). This is an informational message only. No user action is requir
ed.
2010-06-01 22:54:54.42 spid7s 0 transactions rolled back in database 'msdb' (4). This is an informational message only. No user action is required.
2010-06-01 22:54:54.42 spid7s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is requi
red.
2010-06-01 22:54:54.59 spid7s Recovery completed for database msdb (database ID 4) in 3 second(s) (analysis 1760 ms, redo 1032 ms, undo 187 ms.) This
is an informational message only. No user action is required.
2010-06-01 22:54:55.56 spid9s Starting up database 'tempdb'.
2010-06-01 22:54:55.71 spid7s Recovery is complete. This is an informational message only. No user action is required.
2010-06-01 22:54:55.78 spid7s Launched startup procedure 'sp_MSrepl_startup'.
2010-06-01 22:54:58.04 Erreur*: 17187, Gravité*: 16, État*: 1.
2010-06-01 22:54:58.04 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*: 127.0.0.1]
2010-06-01 22:54:58.04 Erreur*: 17187, Gravité*: 16, État*: 1.
2010-06-01 22:54:58.04 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*: 127.0.0.1]
2010-06-01 22:54:58.23 Serveur The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service
. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informationa
l message. Further action is only required if Kerberos authentication is required by authentication policies.
2010-06-01 22:54:58.23 Serveur SQL Server is now ready for client connections. This is an informational message; no user action is required.
2010-06-01 22:55:08.32 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 22:55:08.37 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 22:55:08.39 Erreur*: 17809, Gravité*: 20, État*: 3.
2010-06-01 22:55:08.39 Could not connect because the maximum number of '1' 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]
2010-06-01 22:55:08.39 Erreur*: 17809, Gravité*: 20, État*: 3.
2010-06-01 22:55:08.39 Could not connect because the maximum number of '1' 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]
2010-06-01 22:55:13.43 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 22:55:13.48 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 22:55:13.49 Erreur*: 17809, Gravité*: 20, État*: 3.
2010-06-01 22:55:13.49 Could not connect because the maximum number of '1' 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]
2010-06-01 22:55:13.49 Erreur*: 17809, Gravité*: 20, État*: 3.
2010-06-01 22:55:13.49 Could not connect because the maximum number of '1' 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]
2010-06-01 22:55:18.54 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 22:55:18.59 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 22:55:23.64 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2.
2010-06-01 22:55:23.68 spid51 Setting database option COMPATIBILITY_LEVEL to 100 for database ReportServer$SQL2K8INSTANCE2TempDB.
2010-06-01 22:55:23.70 Erreur*: 17809, Gravité*: 20, État*: 3.

---

Hello,

Si le problème vient de 'user connections' à 1, alors une connexion DAC doit fonctionner, elle ne tient pas compte du paramètre.

Code:

---

sqlcmd -E -A

---

David B.

Citation:

---

Envoyé par dbaffaleuf

Hello,
Si le problème vient de 'user connections' à 1, alors une connexion DAC doit fonctionner, elle ne tient pas compte du paramètre.

Code:

---

sqlcmd -E -A

---

David B.

---

j'ai fais une restauration du systeme à J-3.
Résultats :
1 ) connexion DAC possible via la console DOS

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106

Microsoft Windows XP [version 5.1.2600] (C) Copyright 1985-2001 Microsoft Corp.   C:\Documents and Settings\e.zinzindohoue>sqlcmd -E -A -S "DPAW-ZINZINDOHU\SQL2K8INSTANCE2" 1> select @@version 2> go     ---------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)     (1 lignes affectées) 1> sp_configure 2> go name minimum maximum config_value run_value ----------------------------------- ----------- ----------- ------------ ----------- access check cache bucket count 0 16384 0 0 access check cache quota 0 2147483647 0 0 Ad Hoc Distributed Queries 0 1 1 1 affinity I/O mask -2147483648 2147483647 0 0 affinity mask -2147483648 2147483647 0 0 Agent XPs 0 1 0 0 allow updates 0 1 0 0 awe enabled 0 1 0 0 backup compression default 0 1 0 0 blocked process threshold (s) 0 86400 0 0 c2 audit mode 0 1 0 0 clr enabled 0 1 0 0 common criteria compliance enabled 0 1 0 0 cost threshold for parallelism 0 32767 5 5 cross db ownership chaining 0 1 0 0 cursor threshold -1 2147483647 -1 -1 Database Mail XPs 0 1 0 0 default full-text language 0 2147483647 1036 1036 default language 0 9999 2 2 default trace enabled 0 1 1 1 disallow results from triggers 0 1 0 0 EKM provider enabled 0 1 0 0 filestream access level 0 2 0 0 fill factor (%) 0 100 0 0 ft crawl bandwidth (max) 0 32767 100 100 ft crawl bandwidth (min) 0 32767 0 0 ft notify bandwidth (max) 0 32767 100 100 ft notify bandwidth (min) 0 32767 0 0 index create memory (KB) 704 2147483647 0 0 in-doubt xact resolution 0 2 0 0 lightweight pooling 0 1 0 0 locks 5000 2147483647 0 0 max degree of parallelism 0 64 0 0 max full-text crawl range 0 256 4 4 max server memory (MB) 16 2147483647 2147483647 2147483647 max text repl size (B) -1 2147483647 65536 65536 max worker threads 128 32767 0 0 media retention 0 365 0 0 min memory per query (KB) 512 2147483647 1024 1024 min server memory (MB) 0 2147483647 0 8 nested triggers 0 1 1 1 network packet size (B) 512 32767 4096 4096 Ole Automation Procedures 0 1 0 0 open objects 0 2147483647 0 0 optimize for ad hoc workloads 0 1 0 0 PH timeout (s) 1 3600 60 60 precompute rank 0 1 0 0 priority boost 0 1 0 0 query governor cost limit 0 2147483647 0 0 query wait (s) -1 2147483647 -1 -1 recovery interval (min) 0 32767 0 0 remote access 0 1 1 1 remote admin connections 0 1 0 0 remote login timeout (s) 0 2147483647 20 20 remote proc trans 0 1 0 0 remote query timeout (s) 0 2147483647 600 600 Replication XPs 0 1 0 0 scan for startup procs 0 1 1 1 server trigger recursion 0 1 1 1 set working set size 0 1 0 0 show advanced options 0 1 1 1 SMO and DMO XPs 0 1 1 1 SQL Mail XPs 0 1 0 0 transform noise words 0 1 0 0 two digit year cutoff 1753 9999 2049 2049 user connections 0 32767 1 1 user options 0 32767 0 0 xp_cmdshell 0 1 1 1 1> EXEC sp_configure 'show advanced options', 1 2> GO L'option de configuration 'show advanced options' est passée de 1 à 1. Pour installer, exécutez l'instruction RECONFIGURE. 1> RECONFIGURE 2> GO 1> EXEC sp_configure 'remote admin connections', 1 2> GO L'option de configuration 'remote admin connections' est passée de 0 à 1. Pour installer, exécutez l'instruction RECONFIGURE. 1> RECONFIGURE 2> GO 1> EXEC sp_configure 'remote admin connections' 2> go name minimum maximum config_value run_value ----------------------------------- ----------- ----------- ------------ ----------- remote admin connections 0 1 1 1 1>

---

2) Impossible de se connecter via SSMS authentification Windows

3) Impossible de se connecter à SSMS en ADMIN: (voir fichier joint)

Etienne,

Il faut te connecter en DAC et modifier la valeur de 'user connections':

Code:

---

1 2 3 4 5

sqlcmd -E -A exec sp_configure 'user connections',0 go reconfigure go

---

En la passant à 0, elle redevient auto-configurée.

David B.

Citation:

---

Envoyé par dbaffaleuf

Etienne,
Il faut te connecter en DAC et modifier la valeur de 'user connections':

Code:

---

1 2 3 4 5

sqlcmd -E -A exec sp_configure 'user connections',0 go reconfigure go

---

En la passant à 0, elle redevient auto-configurée.
David B.

---

Et ça marche :ccool:

Un grand merci à David (dbaffaleuf) :ccool:
Merci également à David (mikedavem) :ccool:
Bravo les gars ;)

Mais qu'est ce qui peut être à l'origine de ce problème ?
Enfin, bref...
De cette anomalie je retiens 3 étapes pour le troubleshooting :
1 ) redemarrage de l'instance en mode mono-utilisateur

Code:

---

1 2	sqlservr.exe -m

---

2 ) Connexion Admin dédié

Code:

---

1 2	sqlcmd -E -A -S NomINSTANCE

---

3 ) et la reconfiguration de l'option avancée 'user connections' à 0

Code:

---

1 2 3 4 5

exec sp_configure 'user connections',0 go reconfigure go

---

Thanks !

La question c'est pourquoi elle était à 1 cette valeur (le défaut est à 0)

David B.

Citation:

---

Envoyé par dbaffaleuf

La question c'est pourquoi elle était à 1 cette valeur (le défaut est à 0)
David B.

---

Il me semble que c'est le fait de démarrer l'instance en mono-utilisateur

Code:

---

sqlservr.exe -m

---

qui place à 1 la valeur de 'user connections'.
Par contre la source du problème initial sur l'instance pourrait être l'utilisation de

Code:

---

EXEC master..xp_regwrite

---

C'est juste une hypothèse...