none
SQL Server 2005 reports error:18456... RRS feed

  • 问题

  • My database applications have very large insert and update access operations, after the application running several hours, the  database can not be  connected, the database service log reported following error:

    Error: 18456, Severity: 14, State: 8.

    after restart the SqlServer Service, it's OK.

    I don't know what cause the problem,and how to avoid it!

    Environment:

    SQL Server 2005

    Windows Server 2003

    2012年5月14日 3:42

全部回复

  • Is there any SQL Server 2005 SP1 installed?

    http://support.microsoft.com/kb/925744


    以上說明若有錯誤請指教,謝謝。
    http://www.dotblogs.com.tw/terrychuang/


    2012年5月14日 5:15
  • 你需要提供更详细的版本信息和错误信息。
    操作系统是32bit or 64bit ? SQL 是32bit or 64bit ? 有多少cpu,有多少内存?还有xp_readerrorlog结果。

    workthread不够用或是memtoleave内存不足都会导致sql server不能连接。


    Best Regards, nicofer


    • 已编辑 nicofer 2012年5月14日 6:08
    2012年5月14日 6:02
  • 操作系统和SQL都是32位的,4个CPU,4G内存。故障发生时SQL所在内存:物理内存:710M,虚拟内存710M

    2012年5月14日 6:08
  • 操作系统和SQL都是32位的,4个CPU,4G内存。故障发生时SQL所在内存:物理内存:710M,虚拟内存710M

    贴一下xp_readerrorlog结果在这里,要有问题的log, 你可能需要执行:
    xp_readerrorlog 1
    xp_readerrorlog 2
    xp_readerrorlog 3


    Best Regards, nicofer

    2012年5月14日 6:09
  • 2012-05-11 15:33:39.00 Server      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
    Nov 24 2008 13:01:59 
    Copyright (c) 1988-2005 Microsoft Corporation
    Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    2012-05-11 15:33:39.09 Server      (c) 2005 Microsoft Corporation.
    2012-05-11 15:33:39.09 Server      All rights reserved.
    2012-05-11 15:33:39.09 Server      Server process ID is 1800.
    2012-05-11 15:33:39.09 Server      Authentication mode is MIXED.
    2012-05-11 15:33:39.09 Server      Logging SQL Server messages in file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
    2012-05-11 15:33:39.09 Server      This instance of SQL Server last reported using a process ID of 4400 at 2012-5-11 15:31:45 (local) 2012-5-11 7:31:45 (UTC). This is an informational message only; no user action is required.
    2012-05-11 15:33:39.11 Server      Registry startup parameters:
    2012-05-11 15:33:39.14 Server       -d D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
    2012-05-11 15:33:39.14 Server       -e D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
    2012-05-11 15:33:39.14 Server       -l D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
    2012-05-11 15:33:39.20 服务器         SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
    2012-05-11 15:33:39.20 服务器         Detected 4 CPUs. This is an informational message; no user action is required.
    2012-05-11 15:33:39.80 服务器         Set AWE Enabled to 1 in the configuration parameters to allow use of more memory.
    2012-05-11 15:33:40.32 服务器         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.
    2012-05-11 15:33:41.11 服务器         Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
    2012-05-11 15:33:41.15 服务器         The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted.  If you would like distributed transaction functionality, please start this service.
    2012-05-11 15:33:41.35 服务器         Database mirroring has been enabled on this instance of SQL Server.
    2012-05-11 15:33:41.57 spid5s      Starting up database 'master'.
    2012-05-11 15:33:41.95 spid5s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
    2012-05-11 15:33:42.29 spid5s      Starting up database 'mssqlsystemresource'.
    2012-05-11 15:33:42.37 spid5s      The resource database build version is 9.00.4035. This is an informational message only. No user action is required.
    2012-05-11 15:33:42.80 spid8s      Starting up database 'model'.
    2012-05-11 15:33:42.80 spid5s      Server name is 'IAVP249'. This is an informational message only. No user action is required.
    2012-05-11 15:33:43.05 spid8s      Clearing tempdb database.
    2012-05-11 15:33:43.63 spid8s      Starting up database 'tempdb'.
    2012-05-11 15:33:43.76 spid11s     The Service Broker protocol transport is disabled or not configured.
    2012-05-11 15:33:43.76 spid11s     The Database Mirroring protocol transport is disabled or not configured.
    2012-05-11 15:33:43.81 spid11s     Service Broker manager has started.
    2012-05-11 15:33:43.97 服务器         A self-generated certificate was successfully loaded for encryption.
    2012-05-11 15:33:43.99 服务器         Server is listening on [ 'any' <ipv4> 1433].
    2012-05-11 15:33:43.99 服务器         Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
    2012-05-11 15:33:43.99 服务器         Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].
    2012-05-11 15:33:44.00 服务器         Server is listening on [ 127.0.0.1 <ipv4> 1434].
    2012-05-11 15:33:44.00 服务器         Dedicated admin connection support was established for listening locally on port 1434.
    2012-05-11 15:33:44.01 服务器         The SQL 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 informational message. Further action is only required if Kerberos authentication is required by authentication policies.
    2012-05-11 15:33:44.01 服务器         SQL Server is now ready for client connections. This is an informational message; no user action is required.
    2012-05-11 15:33:44.05 spid18s     Starting up database 'SMUP20'.
    2012-05-11 15:33:44.05 spid20s     Starting up database 'smupdb'.
    2012-05-11 15:33:44.05 spid17s     Starting up database 'msdb'.
    2012-05-11 15:33:44.05 spid21s     Starting up database 'smupdb_calc'.
    2012-05-11 15:33:44.05 spid19s     Starting up database 'Smsinfo'.
    2012-05-11 15:33:44.05 spid22s     Starting up database 'smupdb_history'.
    2012-05-11 15:33:48.40 spid18s     Analysis of database 'SMUP20' (5) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
    2012-05-11 15:33:48.70 spid19s     Analysis of database 'Smsinfo' (6) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
    2012-05-11 15:33:48.80 spid5s      Recovery is complete. This is an informational message only. No user action is required.
    2012-05-11 15:33:51.07 spid51      Using 'xpsqlbot.dll' version '2005.90.4035' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
    2012-05-11 15:33:51.42 spid51      Using 'xpstar90.dll' version '2005.90.4035' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.
    2012-05-11 15:33:51.54 spid51      Using 'xplog70.dll' version '2005.90.4035' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.
    2012-05-11 15:38:33.91 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 15:38:33.91 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 15:56:27.06 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 15:56:27.06 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 15:56:32.12 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 15:56:32.12 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 15:56:42.23 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 15:56:42.23 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 15:57:02.64 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 15:57:02.64 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 15:57:42.68 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 15:57:42.68 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 15:58:42.79 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 15:58:42.79 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 15:59:42.88 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 15:59:42.88 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:00:43.24 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:00:43.24 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:01:43.67 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:01:43.67 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:02:44.22 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:02:44.22 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:03:44.28 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:03:44.28 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:04:44.87 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:04:44.87 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:05:44.99 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:05:44.99 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:13:37.01 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:13:37.01 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:33:49.01 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:33:49.01 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:50:31.26 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:50:31.26 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:54:25.59 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:54:25.59 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:54:31.09 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:54:31.09 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:54:41.14 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:54:41.14 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:55:01.24 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:55:01.24 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:55:41.34 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:55:41.34 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:56:41.37 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:56:41.37 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:57:41.45 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:57:41.45 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 16:58:41.56 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 16:58:41.56 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 17:11:17.73 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 17:11:17.73 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 17:11:22.78 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 17:11:22.78 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 17:11:32.86 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 17:11:32.86 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 17:11:52.91 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 17:11:52.91 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 17:12:32.93 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 17:12:32.93 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 17:13:33.00 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 17:13:33.00 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 17:14:33.02 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 17:14:33.02 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 17:15:33.06 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 17:15:33.06 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-11 17:29:04.98 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-11 17:29:04.98 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-12 00:00:02.98 spid18s     This instance of SQL Server has been using a process ID of 1800 since 2012-5-11 15:33:48 (local) 2012-5-11 7:33:48 (UTC). This is an informational message only; no user action is required.
    2012-05-13 00:00:48.02 spid13s     This instance of SQL Server has been using a process ID of 1800 since 2012-5-11 15:33:48 (local) 2012-5-11 7:33:48 (UTC). This is an informational message only; no user action is required.
    2012-05-14 00:00:33.02 spid13s     This instance of SQL Server has been using a process ID of 1800 since 2012-5-11 15:33:48 (local) 2012-5-11 7:33:48 (UTC). This is an informational message only; no user action is required.
    2012-05-14 08:40:00.79 登录          错误: 18456,严重性: 14,状态: 16。
    2012-05-14 08:40:00.79 登录          Login failed for user 'sa'. [客户端: 192.168.85.77]
    2012-05-14 09:08:39.15 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 09:08:39.15 登录          Login failed for user 'sa'. [客户端: 192.168.15.22]
    2012-05-14 09:08:44.85 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 09:08:44.85 登录          Login failed for user 'sa'. [客户端: 192.168.15.22]
    2012-05-14 09:10:11.23 spid62      SQL Trace ID 1 was started by login "sa".
    2012-05-14 09:10:13.78 spid62      SQL Trace stopped. Trace ID = '1'. Login Name = 'sa'.
    2012-05-14 09:10:17.87 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 09:10:17.87 登录          Login failed for user 'sa'. [客户端: 192.168.15.22]
    2012-05-14 09:10:21.17 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 09:10:21.17 登录          Login failed for user 'sa'. [客户端: 192.168.15.22]
    2012-05-14 09:12:52.40 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 09:12:52.40 登录          Login failed for user 'sa'. [客户端: 192.168.85.249]
    2012-05-14 09:12:59.08 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 09:12:59.08 登录          Login failed for user 'sa'. [客户端: 192.168.85.249]
    2012-05-14 09:13:03.53 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 09:13:03.53 登录          Login failed for user 'sa'. [客户端: 192.168.85.249]
    2012-05-14 09:13:11.22 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 09:13:11.22 登录          Login failed for user 'sa'. [客户端: 192.168.15.22]
    2012-05-14 09:14:23.61 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 09:14:23.61 登录          Login failed for user 'sa'. [客户端: 192.168.15.22]
    2012-05-14 09:14:41.55 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 09:14:41.55 登录          Login failed for user 'sa'. [客户端: 192.168.15.22]
    2012-05-14 09:14:44.00 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 09:14:44.00 登录          Login failed for user 'sa'. [客户端: 192.168.15.22]
    2012-05-14 09:15:04.90 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 09:15:04.90 登录          Login failed for user 'sa'. [客户端: 192.168.15.22]
    2012-05-14 09:15:07.32 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 09:15:07.32 登录          Login failed for user 'sa'. [客户端: 192.168.15.22]
    2012-05-14 09:15:53.24 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 09:15:53.24 登录          Login failed for user 'smup'. [客户端: 192.168.15.22]
    2012-05-14 09:31:07.91 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 09:31:07.91 登录          Login failed for user 'sa'. [客户端: 192.168.15.22]
    2012-05-14 10:30:04.63 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 10:30:04.63 登录          Login failed for user 'sa'. [客户端: 192.168.15.22]
    2012-05-14 10:30:07.00 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 10:30:07.00 登录          Login failed for user 'sa'. [客户端: 192.168.15.22]
    2012-05-14 10:30:14.27 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 10:30:14.27 登录          Login failed for user 'sa'. [客户端: 192.168.85.249]
    2012-05-14 10:30:26.87 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 10:30:26.87 登录          Login failed for user 'sa'. [客户端: 192.168.85.249]
    2012-05-14 10:31:58.05 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 10:31:58.05 登录          Login failed for user 'sa'. [客户端: 192.168.85.249]
    2012-05-14 10:32:13.95 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 10:32:13.95 登录          Login failed for user 'sa'. [客户端: 192.168.85.249]
    2012-05-14 10:32:16.17 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 10:32:16.17 登录          Login failed for user 'sa'. [客户端: 192.168.85.249]
    2012-05-14 10:32:33.89 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 10:32:33.89 登录          Login failed for user 'sa'. [客户端: 192.168.85.249]
    2012-05-14 10:53:57.41 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 10:53:57.41 登录          Login failed for user 'sa'. [客户端: 192.168.15.22]
    2012-05-14 10:58:29.96 登录          错误: 18456,严重性: 14,状态: 8。
    2012-05-14 10:58:29.96 登录          Login failed for user 'sa'. [客户端: 192.168.15.22]
    2012-05-14 11:00:21.47 spid51      Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
    2012-05-14 11:00:23.63 spid11s     Service Broker manager has shut down.
    2012-05-14 11:00:23.76 spid5s      SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.
    2012年5月14日 6:28
  • 看上去似乎是密码问题:
    http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

    Best Regards, nicofer

    2012年5月14日 6:57
  • 但是很奇怪,只是运行一段时间后才出现问题,重启之后就可以登录了,密码应该没错的
    2012年5月14日 7:03
  • 看上去似乎是密码问题:
    http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx

    Best Regards, nicofer

    我想起一个信息,问题出现之前,我打开过management studio,问题出现之后可以继续做查询操作,但是不能查询master库里的内容。所以我在想是不是因为某种原因导致master库无法访问,因而登录时密码验证都失败了...
    2012年5月14日 8:10
  • Did you install sp4?

    2012年5月14日 13:19
  • 这是有人不断的攻击你的数据库,一直在测试你的sa用户的密码
    2012年6月6日 8:32