文章出處
文章列表
分享今天在客戶那里遇到的SQLSERVER連接超時以及我的解決辦法
客戶的環境：SQLSERVER2005，WINDOWS2003 SP2 32位
這次發生連接超時的時間是2013-8-5 21:34分
在2013-8-5 19:25分左右也發生過一次
下面的SQL ERRORLOG截取的是19:18分~22:08分時間段之內的關鍵日志！！！
這個問題昨天出現了一次，今天出現了兩次，起初沒有認真對待，認為重啟一下機器就沒事了
我認為是CPU占用高或者內存占用高或者磁盤讀寫高，因為客戶的機器安裝了360安全衛士，
當時客戶反映360安全衛士報“磁盤讀寫高”
因為我當時看了任務管理器，windows日志，各項信息都很正常，CPU占用在30%左右，內存在800MB左右
360那里也沒有看出磁盤讀寫特別高（注意：當時我是在客戶重啟了機器之后看的!!!!!!!!!!!!）
-------------------------------------華麗的分割線-----------------------------
入手
誰知道今晚又出現第三次，使得我要從酒店又跑回客戶那里
我去到客戶那里第一時間先看windows日志和sql errorlog
由于在SQL ERRORLOG里看到很多想嘗試登陸數據庫而失敗的日志，
而且看到Windows eventlog里有好幾次SQL服務意外關閉
的記錄，但是SQLSERVER是沒有shutdown 電腦的SQL語句的，所以肯定不是外人所為
而且連接超時也不是經常出現，所以排除了黑客搗蛋的可能性（客戶的電腦設置了防火墻，sa用戶禁用，路由器只開了1433端口）
電腦也做了一些安全設置
不過SQLSERVER有停止Microsoft SQL server的命令
下面兩個SQL語句都可以用來停止SQLSERVER
1 SHUTDOWN 2 SHUTDOWN WITH NOWAIT
不過如果運行上面兩個語句，在SQL ERRORLOG里會有相應的日志記錄的
1 2013-11-30 00:09:42.89 spid12s Service Broker manager has shut down. 2 2013-11-30 00:09:42.94 spid54 Server shut down by request from login JOE\Administrator. 3 2013-11-30 00:09:42.94 spid54 SQL Trace was stopped due to server shutdown. Trace ID = '2'. This is an informational message only; no user action is required. 4 2013-11-30 00:09:42.94 spid54 SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required. 5 2013-11-30 00:09:43.87 登錄 錯誤: 17188，嚴重性: 16，狀態: 1。 6 2013-11-30 00:09:43.87 登錄 SQL Server cannot accept new connections, because it is shutting down. The connection has been closed. [客戶端: <local machine>] 7 2013-11-30 00:09:43.87 登錄 錯誤: 17188，嚴重性: 16，狀態: 1。 8 2013-11-30 00:09:43.87 登錄 SQL Server cannot accept new connections, because it is shutting down. The connection has been closed. [客戶端: <local machine>]
1 2013-11-30 00:14:19.99 spid52 Server shut down by NOWAIT request from login JOE\Administrator. 2 2013-11-30 00:14:19.99 spid52 SQL Trace was stopped due to server shutdown. Trace ID = '2'. This is an informational message only; no user action is required. 3 2013-11-30 00:14:19.99 spid52 SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
2013-11-30 00:09:42.94 spid54 Server shut down by request from login JOE\Administrator.
2013-11-30 00:14:19.99 spid52 Server shut down by NOWAIT request from login JOE\Administrator.
通過SQL語句來shundownSQLSERVER在錯誤日志里會顯示上面兩條記錄
但是這次的shutdown記錄是
SQL Server is terminating because of a system shutdown.
----------------------------------華麗的分割線-------------------------------
再仔細分析
由于我們的應用系統其中一個是用delphi寫的，而且用ODBC來連接SQLSERVER，而且這個應用
是一打開就馬上open connection，直到退出這個應用為止，不像C# 執行完畢就close（）
所以如果這個應用斷開與數據庫的連接就會影響到客戶的業務運行，當時操作系統彈出“超時對話框”的時候
點擊我們的系統很慢幾乎沒有反應，響應很遲鈍，我重啟電腦，看一下windows日志
我在客戶的機器里截了一些圖片
再看一下SQL ERRORLOG
下面這個是重啟電腦之后，SQLSERVER啟動的SQLERRORLOG
1 2013-08-05 19:18:09.24 Server Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) 2 Dec 10 2010 10:56:29 3 Copyright (c) 1988-2005 Microsoft Corporation 4 Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 5 6 2013-08-05 19:18:09.31 Server (c) 2005 Microsoft Corporation. 7 2013-08-05 19:18:09.31 Server All rights reserved. 8 2013-08-05 19:18:09.31 Server Server process ID is 2744. 9 2013-08-05 19:18:09.33 Server Authentication mode is MIXED. 10 2013-08-05 19:18:09.33 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. 11 2013-08-05 19:18:09.33 Server This instance of SQL Server last reported using a process ID of 888 at 2013-8-5 19:17:27 (local) 2013-8-5 11:17:27 (UTC). This is an informational message only; no user action is required. 12 2013-08-05 19:18:09.33 Server Registry startup parameters: 13 2013-08-05 19:18:09.33 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf 14 2013-08-05 19:18:09.33 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG 15 2013-08-05 19:18:09.33 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf 16 2013-08-05 19:18:09.36 服務器 SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required. 17 2013-08-05 19:18:09.36 服務器 Detected 2 CPUs. This is an informational message; no user action is required. 18 2013-08-05 19:18:09.73 服務器 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. 19 2013-08-05 19:18:10.05 服務器 Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required. 20 2013-08-05 19:18:10.39 服務器 The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service. 21 2013-08-05 19:18:10.46 服務器 Database mirroring has been enabled on this instance of SQL Server. 22 2013-08-05 19:18:10.56 spid5s Starting up database 'master'. 23 2013-08-05 19:18:10.69 spid5s 33 transactions rolled forward in database 'master' (1). This is an informational message only. No user action is required. 24 2013-08-05 19:18:10.74 spid5s 0 transactions rolled back in database 'master' (1). This is an informational message only. No user action is required. 25 2013-08-05 19:18:10.74 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required. 26 2013-08-05 19:18:10.88 spid5s Starting up database 'mssqlsystemresource'. 27 2013-08-05 19:18:10.92 spid5s The resource database build version is 9.00.5000. This is an informational message only. No user action is required. 28 2013-08-05 19:18:11.18 spid5s Server name is 'HS'. This is an informational message only. No user action is required. 29 2013-08-05 19:18:11.20 spid8s Starting up database 'model'. 30 2013-08-05 19:18:11.42 spid8s Clearing tempdb database. 31 2013-08-05 19:18:12.12 spid8s Starting up database 'tempdb'. 32 2013-08-05 19:18:12.34 spid11s The Service Broker protocol transport is disabled or not configured. 33 2013-08-05 19:18:12.34 spid11s The Database Mirroring protocol transport is disabled or not configured. 34 2013-08-05 19:18:12.38 spid11s Service Broker manager has started. 35 2013-08-05 19:18:12.60 服務器 A self-generated certificate was successfully loaded for encryption. 36 2013-08-05 19:18:12.62 服務器 Server is listening on [ 'any' <ipv4> 1433]. 37 2013-08-05 19:18:12.62 服務器 Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ]. 38 2013-08-05 19:18:12.62 服務器 Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ]. 39 2013-08-05 19:18:12.64 服務器 Server is listening on [ 'any' <ipv4> 1434]. 40 2013-08-05 19:18:12.64 服務器 Dedicated admin connection support was established for listening remotely on port 1434. 41 2013-08-05 19:18:12.70 服務器 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. 42 2013-08-05 19:18:12.70 服務器 SQL Server is now ready for client connections. This is an informational message; no user action is required. 43 2013-08-05 19:18:13.23 spid14s Starting up database 'msdb'. 44 2013-08-05 19:18:13.23 spid15s Starting up database 'GPOSDB'. 45 2013-08-05 19:18:14.31 spid14s 25 transactions rolled forward in database 'msdb' (4). This is an informational message only. No user action is required. 46 2013-08-05 19:18:14.78 spid5s 0 transactions rolled back in database 'msdb' (4). This is an informational message only. No user action is required. 47 2013-08-05 19:18:14.78 spid5s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required. 48 2013-08-05 19:18:19.86 spid15s Analysis of database 'GPOSDB' (5) is 5% complete (approximately 4 seconds remain). This is an informational message only. No user action is required. 49 2013-08-05 19:18:23.85 spid15s Analysis of database 'GPOSDB' (5) is 82% complete (approximately 0 seconds remain). This is an informational message only. No user action is required. 50 2013-08-05 19:18:24.88 spid15s Analysis of database 'GPOSDB' (5) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required. 51 2013-08-05 19:18:24.88 spid15s Recovery of database 'GPOSDB' (5) is 0% complete (approximately 261 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 52 2013-08-05 19:18:25.02 spid15s Recovery of database 'GPOSDB' (5) is 1% complete (approximately 7 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 53 2013-08-05 19:18:30.50 spid15s Recovery of database 'GPOSDB' (5) is 57% complete (approximately 3 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 54 2013-08-05 19:18:30.50 spid15s 214 transactions rolled forward in database 'GPOSDB' (5). This is an informational message only. No user action is required. 55 2013-08-05 19:18:30.61 spid15s Recovery of database 'GPOSDB' (5) is 57% complete (approximately 3 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required. 56 2013-08-05 19:18:30.71 spid5s Recovery of database 'GPOSDB' (5) is 100% complete (approximately 0 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required. 57 2013-08-05 19:18:30.71 spid5s 0 transactions rolled back in database 'GPOSDB' (5). This is an informational message only. No user action is required. 58 2013-08-05 19:18:30.71 spid5s Recovery is writing a checkpoint in database 'GPOSDB' (5). This is an informational message only. No user action is required. 59 2013-08-05 19:18:30.73 spid5s Recovery is complete. This is an informational message only. No user action is required. 60 2013-08-05 19:18:31.64 spid51 Using 'xpstar90.dll' version '2005.90.5000' to execute extended stored procedure 'xp_sqlagent_monitor'. This is an informational message only; no user action is required.
下面這個是電腦正常重啟的時候的SQL ERRORLOG
1 2013-08-05 21:59:51.28 Server Microsoft SQL Server 2005 - 9.00.5000.00 (Intel X86) 2 Dec 10 2010 10:56:29 3 Copyright (c) 1988-2005 Microsoft Corporation 4 Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 5 6 2013-08-05 21:59:51.28 Server (c) 2005 Microsoft Corporation. 7 2013-08-05 21:59:51.28 Server All rights reserved. 8 2013-08-05 21:59:51.28 Server Server process ID is 1228. 9 2013-08-05 21:59:51.28 Server Authentication mode is MIXED. 10 2013-08-05 21:59:51.28 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'. 11 2013-08-05 21:59:51.28 Server This instance of SQL Server last reported using a process ID of 3700 at 2013-8-5 21:58:42 (local) 2013-8-5 13:58:42 (UTC). This is an informational message only; no user action is required. 12 2013-08-05 21:59:51.28 Server Registry startup parameters: 13 2013-08-05 21:59:51.28 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf 14 2013-08-05 21:59:51.28 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG 15 2013-08-05 21:59:51.28 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf 16 2013-08-05 21:59:51.37 服務器 SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required. 17 2013-08-05 21:59:51.39 服務器 Detected 2 CPUs. This is an informational message; no user action is required. 18 2013-08-05 21:59:53.82 服務器 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. 19 2013-08-05 21:59:53.89 服務器 Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required. 20 2013-08-05 21:59:53.90 服務器 The Microsoft Distributed Transaction Coordinator (MS DTC) service could not be contacted. If you would like distributed transaction functionality, please start this service. 21 2013-08-05 21:59:53.90 服務器 Database mirroring has been enabled on this instance of SQL Server. 22 2013-08-05 21:59:53.91 spid5s Starting up database 'master'. 23 2013-08-05 21:59:54.34 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required. 24 2013-08-05 21:59:54.93 spid5s Starting up database 'mssqlsystemresource'. 25 2013-08-05 21:59:55.24 spid5s The resource database build version is 9.00.5000. This is an informational message only. No user action is required. 26 2013-08-05 21:59:55.68 spid5s Server name is 'HS'. This is an informational message only. No user action is required. 27 2013-08-05 21:59:55.68 spid8s Starting up database 'model'. 28 2013-08-05 21:59:56.09 服務器 A self-generated certificate was successfully loaded for encryption. 29 2013-08-05 21:59:56.10 服務器 Server is listening on [ 'any' <ipv4> 1433]. 30 2013-08-05 21:59:56.10 服務器 Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ]. 31 2013-08-05 21:59:56.10 服務器 Server named pipe provider is ready to accept connection on [ \\.\pipe\sql\query ]. 32 2013-08-05 21:59:56.10 服務器 Server is listening on [ 'any' <ipv4> 1434]. 33 2013-08-05 21:59:56.10 服務器 Dedicated admin connection support was established for listening remotely on port 1434. 34 2013-08-05 21:59:56.10 服務器 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. 35 2013-08-05 21:59:56.11 服務器 SQL Server is now ready for client connections. This is an informational message; no user action is required. 36 2013-08-05 21:59:56.13 spid11s Starting up database 'msdb'. 37 2013-08-05 21:59:56.13 spid12s Starting up database 'GPOSDB'. 38 2013-08-05 21:59:56.67 spid8s Clearing tempdb database. 39 2013-08-05 22:00:24.12 spid8s Starting up database 'tempdb'. 40 2013-08-05 22:00:33.17 spid5s Recovery is complete. This is an informational message only. No user action is required. 41 2013-08-05 22:00:33.17 spid11s The Service Broker protocol transport is disabled or not configured. 42 2013-08-05 22:00:33.17 spid11s The Database Mirroring protocol transport is disabled or not configured. 43 2013-08-05 22:00:33.30 spid11s Service Broker manager has started. 44 2013-08-05 22:00:42.62 spid51 Using 'xpsqlbot.dll' version '2005.90.5000' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required. 45 2013-08-05 22:00:45.54 spid51 Using 'xpstar90.dll' version '2005.90.5000' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required. 46 2013-08-05 22:00:47.02 spid51 Using 'xplog70.dll' version '2005.90.5000' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required. 47 2013-08-05 22:05:43.46 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 48 2013-08-05 22:05:43.46 登錄 Login failed for user 'sa'. [客戶端: 211.149.156.95] 49 2013-08-05 22:07:25.21 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 50 2013-08-05 22:07:25.21 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 51 2013-08-05 22:07:25.31 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 52 2013-08-05 22:07:25.31 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 53 2013-08-05 22:07:25.40 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 54 2013-08-05 22:07:25.40 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 55 2013-08-05 22:07:29.17 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 56 2013-08-05 22:07:29.17 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 57 2013-08-05 22:07:29.26 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 58 2013-08-05 22:07:29.26 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 59 2013-08-05 22:07:32.26 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 60 2013-08-05 22:07:32.26 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 61 2013-08-05 22:07:32.35 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 62 2013-08-05 22:07:32.35 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 63 2013-08-05 22:07:32.45 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 64 2013-08-05 22:07:32.45 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 65 2013-08-05 22:07:32.54 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 66 2013-08-05 22:07:32.54 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 67 2013-08-05 22:07:32.63 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 68 2013-08-05 22:07:32.63 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 69 2013-08-05 22:07:32.73 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 70 2013-08-05 22:07:32.73 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 71 2013-08-05 22:07:32.82 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 72 2013-08-05 22:07:32.82 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 73 2013-08-05 22:07:32.91 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 74 2013-08-05 22:07:32.91 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 75 2013-08-05 22:07:33.00 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 76 2013-08-05 22:07:33.00 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 77 2013-08-05 22:07:33.09 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 78 2013-08-05 22:07:33.09 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 79 2013-08-05 22:07:33.19 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 80 2013-08-05 22:07:33.19 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 81 2013-08-05 22:07:33.28 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 82 2013-08-05 22:07:33.28 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 83 2013-08-05 22:07:33.37 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 84 2013-08-05 22:07:33.37 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 85 2013-08-05 22:07:36.42 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 86 2013-08-05 22:07:36.42 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 87 2013-08-05 22:07:36.51 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 88 2013-08-05 22:07:36.51 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 89 2013-08-05 22:07:36.60 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 90 2013-08-05 22:07:36.60 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 91 2013-08-05 22:07:36.69 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 92 2013-08-05 22:07:36.69 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 93 2013-08-05 22:07:42.65 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 94 2013-08-05 22:07:42.65 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 95 2013-08-05 22:07:42.74 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 96 2013-08-05 22:07:42.74 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 97 2013-08-05 22:07:42.84 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 98 2013-08-05 22:07:42.84 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 99 2013-08-05 22:07:42.93 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 100 2013-08-05 22:07:42.93 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 101 2013-08-05 22:07:43.02 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 102 2013-08-05 22:07:43.02 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 103 2013-08-05 22:07:43.11 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 104 2013-08-05 22:07:43.11 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 105 2013-08-05 22:07:43.21 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 106 2013-08-05 22:07:43.21 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 107 2013-08-05 22:07:43.30 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 108 2013-08-05 22:07:43.30 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 109 2013-08-05 22:07:43.39 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 110 2013-08-05 22:07:43.39 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 111 2013-08-05 22:07:43.48 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 112 2013-08-05 22:07:43.48 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 113 2013-08-05 22:07:43.58 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 114 2013-08-05 22:07:43.58 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 115 2013-08-05 22:07:43.67 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 116 2013-08-05 22:07:43.67 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 117 2013-08-05 22:07:46.64 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 118 2013-08-05 22:07:46.64 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 119 2013-08-05 22:07:49.73 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 120 2013-08-05 22:07:49.73 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 121 2013-08-05 22:07:49.82 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 122 2013-08-05 22:07:49.82 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 123 2013-08-05 22:07:49.92 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 124 2013-08-05 22:07:49.92 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 125 2013-08-05 22:07:50.01 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 126 2013-08-05 22:07:50.01 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 127 2013-08-05 22:07:50.10 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 128 2013-08-05 22:07:50.10 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 129 2013-08-05 22:07:50.19 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 130 2013-08-05 22:07:50.19 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 131 2013-08-05 22:07:50.29 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 132 2013-08-05 22:07:50.29 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 133 2013-08-05 22:07:50.38 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 134 2013-08-05 22:07:50.38 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 135 2013-08-05 22:07:50.47 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 136 2013-08-05 22:07:50.47 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 137 2013-08-05 22:07:50.56 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 138 2013-08-05 22:07:50.56 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 139 2013-08-05 22:07:50.66 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 140 2013-08-05 22:07:50.66 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 141 2013-08-05 22:07:50.75 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 142 2013-08-05 22:07:50.75 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 143 2013-08-05 22:07:50.84 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 144 2013-08-05 22:07:50.84 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 145 2013-08-05 22:07:50.93 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 146 2013-08-05 22:07:50.93 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 147 2013-08-05 22:07:51.03 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 148 2013-08-05 22:07:51.03 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 149 2013-08-05 22:07:51.12 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 150 2013-08-05 22:07:51.12 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 151 2013-08-05 22:07:51.21 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 152 2013-08-05 22:07:51.21 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 153 2013-08-05 22:07:53.91 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 154 2013-08-05 22:07:53.91 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 155 2013-08-05 22:07:54.00 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 156 2013-08-05 22:07:54.00 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 157 2013-08-05 22:07:54.09 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 158 2013-08-05 22:07:54.09 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 159 2013-08-05 22:07:54.18 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 160 2013-08-05 22:07:54.18 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 161 2013-08-05 22:07:54.28 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 162 2013-08-05 22:07:54.28 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 163 2013-08-05 22:07:54.37 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 164 2013-08-05 22:07:54.37 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 165 2013-08-05 22:07:54.46 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 166 2013-08-05 22:07:54.46 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 167 2013-08-05 22:07:54.55 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 168 2013-08-05 22:07:54.55 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 169 2013-08-05 22:07:54.65 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 170 2013-08-05 22:07:54.65 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 171 2013-08-05 22:07:54.74 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 172 2013-08-05 22:07:54.74 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 173 2013-08-05 22:07:54.83 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 174 2013-08-05 22:07:54.83 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 175 2013-08-05 22:07:54.92 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 176 2013-08-05 22:07:54.92 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 177 2013-08-05 22:07:57.96 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 178 2013-08-05 22:07:57.96 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 179 2013-08-05 22:07:58.06 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 180 2013-08-05 22:07:58.06 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 181 2013-08-05 22:08:01.45 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 182 2013-08-05 22:08:01.45 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 183 2013-08-05 22:08:01.54 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 184 2013-08-05 22:08:01.54 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 185 2013-08-05 22:08:01.63 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 186 2013-08-05 22:08:01.63 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 187 2013-08-05 22:08:01.73 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 188 2013-08-05 22:08:01.73 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 189 2013-08-05 22:08:01.82 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 190 2013-08-05 22:08:01.82 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 191 2013-08-05 22:08:01.91 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 192 2013-08-05 22:08:01.91 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 193 2013-08-05 22:08:05.01 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 194 2013-08-05 22:08:05.01 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 195 2013-08-05 22:08:05.10 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 196 2013-08-05 22:08:05.10 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 197 2013-08-05 22:08:05.20 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 198 2013-08-05 22:08:05.20 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 199 2013-08-05 22:08:05.29 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 200 2013-08-05 22:08:05.29 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 201 2013-08-05 22:08:05.38 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 202 2013-08-05 22:08:05.38 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 203 2013-08-05 22:08:05.47 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 204 2013-08-05 22:08:05.47 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 205 2013-08-05 22:08:05.56 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 206 2013-08-05 22:08:05.56 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 207 2013-08-05 22:08:05.66 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 208 2013-08-05 22:08:05.66 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 209 2013-08-05 22:08:05.75 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 210 2013-08-05 22:08:05.75 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 211 2013-08-05 22:08:05.84 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 212 2013-08-05 22:08:05.84 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 213 2013-08-05 22:08:05.93 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 214 2013-08-05 22:08:05.93 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 215 2013-08-05 22:08:06.03 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 216 2013-08-05 22:08:06.03 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 217 2013-08-05 22:08:06.12 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 218 2013-08-05 22:08:06.12 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 219 2013-08-05 22:08:06.21 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 220 2013-08-05 22:08:06.21 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 221 2013-08-05 22:08:06.31 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 222 2013-08-05 22:08:06.31 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 223 2013-08-05 22:08:06.40 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 224 2013-08-05 22:08:06.40 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 225 2013-08-05 22:08:06.49 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 226 2013-08-05 22:08:06.49 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 227 2013-08-05 22:08:06.58 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 228 2013-08-05 22:08:06.58 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 229 2013-08-05 22:08:06.67 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 230 2013-08-05 22:08:06.67 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 231 2013-08-05 22:08:06.77 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 232 2013-08-05 22:08:06.77 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 233 2013-08-05 22:08:06.86 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 234 2013-08-05 22:08:06.86 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 235 2013-08-05 22:08:06.95 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 236 2013-08-05 22:08:06.95 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45] 237 2013-08-05 22:08:07.04 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 238 2013-08-05 22:08:07.04 登錄 Login failed for user 'sa'. [客戶端: 59.188.137.45]
可以看到兩者的區別
不正常的比正常的多出了下面這幾個語句
1 25 transactions rolled forward in database 'msdb' (4). This is an informational message only. No user action is required. 2 2013-08-05 19:18:14.78 spid5s 0 transactions rolled back in database 'msdb' (4). This is an informational message only. No user action is required. 3 2013-08-05 19:18:14.78 spid5s Recovery is writing a checkpoint in database 'msdb' (4). This is an informational message only. No user action is required. 4 2013-08-05 19:18:19.86 spid15s Analysis of database 'GPOSDB' (5) is 5% complete (approximately 4 seconds remain). This is an informational message only. No user action is required. 5 2013-08-05 19:18:23.85 spid15s Analysis of database 'GPOSDB' (5) is 82% complete (approximately 0 seconds remain). This is an informational message only. No user action is required. 6 2013-08-05 19:18:24.88 spid15s Analysis of database 'GPOSDB' (5) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required. 7 2013-08-05 19:18:24.88 spid15s Recovery of database 'GPOSDB' (5) is 0% complete (approximately 261 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 8 2013-08-05 19:18:25.02 spid15s Recovery of database 'GPOSDB' (5) is 1% complete (approximately 7 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 9 2013-08-05 19:18:30.50 spid15s Recovery of database 'GPOSDB' (5) is 57% complete (approximately 3 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 10 2013-08-05 19:18:30.50 spid15s 214 transactions rolled forward in database 'GPOSDB' (5). This is an informational message only. No user action is required. 11 2013-08-05 19:18:30.61 spid15s Recovery of database 'GPOSDB' (5) is 57% complete (approximately 3 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required. 12 2013-08-05 19:18:30.71 spid5s Recovery of database 'GPOSDB' (5) is 100% complete (approximately 0 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required. 13 2013-08-05 19:18:30.71 spid5s 0 transactions rolled back in database 'GPOSDB' (5). This is an informational message only. No user action is required. 14 2013-08-05 19:18:30.71 spid5s Recovery is writing a checkpoint in database 'GPOSDB' (5). This is an informational message only. No user action is required. 15 2013-08-05 19:18:30.73 spid5s Recovery is complete. This is an informational message only. No user action is required.
SQLSERVER不斷分析和恢復我們的業務數據庫GPOSDB
我還截取了一些關鍵日志
1 2013-08-05 19:19:26.42 spid12s Analysis of database 'GPOSDB' (5) is 5% complete (approximately 7 seconds remain). This is an informational message only. No user action is required. 2 2013-08-05 19:19:28.58 spid12s Analysis of database 'GPOSDB' (5) is 49% complete (approximately 2 seconds remain). This is an informational message only. No user action is required. 3 2013-08-05 19:19:31.44 spid12s Analysis of database 'GPOSDB' (5) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required. 4 2013-08-05 19:19:31.44 spid12s Recovery of database 'GPOSDB' (5) is 0% complete (approximately 261 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 5 2013-08-05 19:19:31.58 spid12s Recovery of database 'GPOSDB' (5) is 1% complete (approximately 7 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 6 2013-08-05 19:19:35.60 spid12s Recovery of database 'GPOSDB' (5) is 57% complete (approximately 3 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 7 2013-08-05 19:19:35.60 spid12s 214 transactions rolled forward in database 'GPOSDB' (5). This is an informational message only. No user action is required. 8 2013-08-05 19:19:35.87 spid12s Recovery of database 'GPOSDB' (5) is 57% complete (approximately 3 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required. 9 2013-08-05 19:19:35.94 spid5s Recovery of database 'GPOSDB' (5) is 100% complete (approximately 0 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required. 10 2013-08-05 19:19:35.94 spid5s 0 transactions rolled back in database 'GPOSDB' (5). This is an informational message only. No user action is required. 11 2013-08-05 19:19:35.94 spid5s Recovery is writing a checkpoint in database 'GPOSDB' (5). This is an informational message only. No user action is required. 12 2013-08-05 19:19:36.00 spid5s Recovery is complete. This is an informational message only. No user action is required. 13 2013-08-05 19:20:07.03 spid51 Autogrow of file 'GPOSDB_log' in database 'GPOSDB' was cancelled by user or timed out after 31000 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size. 14 2013-08-05 19:20:22.60 服務器 SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required. 15 16 --------------------------------------------------------------------------------- 17 2013-08-05 19:24:20.08 登錄 錯誤: 18456，嚴重性: 14，狀態: 16。 18 2013-08-05 19:24:20.08 登錄 Login failed for user 'dreamlink'. [客戶端: 127.0.0.1] 19 2013-08-05 19:24:21.64 spid12s Analysis of database 'GPOSDB' (5) is 94% complete (approximately 5 seconds remain). This is an informational message only. No user action is required. 20 2013-08-05 19:24:22.03 登錄 錯誤: 18456，嚴重性: 14，狀態: 16。 21 2013-08-05 19:24:22.03 登錄 Login failed for user 'dreamlink'. [客戶端: 127.0.0.1] 22 2013-08-05 19:24:30.62 spid12s Analysis of database 'GPOSDB' (5) is 99% complete (approximately 0 seconds remain). This is an informational message only. No user action is required. 23 2013-08-05 19:24:31.55 spid12s Analysis of database 'GPOSDB' (5) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required. 24 2013-08-05 19:24:31.56 spid12s Recovery of database 'GPOSDB' (5) is 0% complete (approximately 261 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 25 2013-08-05 19:24:32.57 spid12s Recovery of database 'GPOSDB' (5) is 1% complete (approximately 83 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 26 2013-08-05 19:24:38.31 spid12s Recovery of database 'GPOSDB' (5) is 7% complete (approximately 87 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 27 2013-08-05 19:24:41.96 登錄 錯誤: 18456，嚴重性: 14，狀態: 16。 28 2013-08-05 19:24:41.96 登錄 Login failed for user 'dreamlink'. [客戶端: 127.0.0.1] 29 2013-08-05 19:24:41.96 登錄 錯誤: 18456，嚴重性: 14，狀態: 16。 30 2013-08-05 19:24:41.96 登錄 Login failed for user 'dreamlink'. [客戶端: 127.0.0.1] 31 2013-08-05 19:24:45.10 spid12s Recovery of database 'GPOSDB' (5) is 12% complete (approximately 94 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 32 2013-08-05 19:24:47.46 spid12s Recovery of database 'GPOSDB' (5) is 17% complete (approximately 77 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 33 2013-08-05 19:24:47.87 spid12s Recovery of database 'GPOSDB' (5) is 22% complete (approximately 56 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 34 2013-08-05 19:24:48.10 spid12s Recovery of database 'GPOSDB' (5) is 28% complete (approximately 40 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 35 2013-08-05 19:24:48.40 spid12s Recovery of database 'GPOSDB' (5) is 36% complete (approximately 28 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 36 2013-08-05 19:24:49.06 spid12s Recovery of database 'GPOSDB' (5) is 46% complete (approximately 19 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 37 2013-08-05 19:24:49.68 spid12s Recovery of database 'GPOSDB' (5) is 58% complete (approximately 12 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 38 2013-08-05 19:24:50.42 spid12s Recovery of database 'GPOSDB' (5) is 73% complete (approximately 6 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 39 2013-08-05 19:24:54.18 spid12s Recovery of database 'GPOSDB' (5) is 86% complete (approximately 3 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 40 2013-08-05 19:24:54.18 spid12s 214 transactions rolled forward in database 'GPOSDB' (5). This is an informational message only. No user action is required. 41 2013-08-05 19:24:54.42 spid12s Recovery of database 'GPOSDB' (5) is 86% complete (approximately 3 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required. 42 2013-08-05 19:24:54.48 spid4s Recovery of database 'GPOSDB' (5) is 100% complete (approximately 0 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required. 43 44 ----------------------------------------------------------------------------- 45 013-08-05 19:20:47.85 登錄 Login failed for user 'bwsa'. [客戶端: 61.147.103.137] 46 2013-08-05 19:20:47.90 登錄 錯誤: 18456，嚴重性: 14，狀態: 5。 47 2013-08-05 19:20:47.90 登錄 Login failed for user 'bwsa'. [客戶端: 61.147.103.137] 48 2013-08-05 19:20:47.95 登錄 錯誤: 18456，嚴重性: 14，狀態: 5。 49 2013-08-05 19:20:47.95 登錄 Login failed for user 'bwsa'. [客戶端: 61.147.103.137] 50 2013-08-05 19:20:47.78 spid51 錯誤: 17054，嚴重性: 16，狀態: 1。 51 2013-08-05 19:20:47.78 spid51 The current event was not reported to the Windows Events log. Operating system error = 1717(接口未知。). You may need to clear the Windows Events log if it is full. 52 2013-08-05 19:20:47.95 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。
關鍵的日志：
2013-08-05 19:20:22.60 服務器 SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
2013-08-05 19:20:47.78 spid51 The current event was not reported to the Windows Events log. Operating system error = 1717(接口未知。). You may need to clear the Windows Events log if it is full.
還有下面這個非常重要的日志
1 013-08-05 21:27:51.24 登錄 錯誤: 18456，嚴重性: 14，狀態: 5。 2 2013-08-05 21:27:51.24 登錄 Login failed for user 'xxx'. [客戶端: 210.56.63.37] 3 2013-08-05 21:33:05.25 spid53 Autogrow of file 'GPOSDB_log' in database 'GPOSDB' was cancelled by user or timed out after 30391 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size. 4 2013-08-05 21:41:53.20 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。 5 2013-08-05 21:41:53.20 登錄 Login failed for user 'sa'. [客戶端: 124.31.204.74] 6 2013-08-05 21:41:53.34 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。
2013-08-05 21:33:05.25 spid53 Autogrow of file 'GPOSDB_log' in database 'GPOSDB' was cancelled by user or timed out after 30391 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
2013-08-05 19:20:07.03 spid51 Autogrow of file 'GPOSDB_log' in database 'GPOSDB' was cancelled by user or timed out after 31000 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
--------------------------------華麗的分割線--------------------------------------------
方向
我認為連接超時就是跟GPOSDB_log的自動增長有關，先解決這個增長問題
看一下LDF文件的大小
由于GPOSDB這個數據庫本來是運行在SQLSERVER2000上的，后來要升級到SQLSERVER2005
就用附加分離方法附加到SQL2005上，但是考慮到以后有可能將GPOSDB又運行在SQL2000上
所以沒有把GPOSDB的數據庫兼容級別設置為90（SQL2005），而且這個數據庫已經用了3年多
所以22.5GB的事務日志文件大小不奇怪，但是數據量不多，mdf文件大小才大概26MB
因為這個數據庫一直使用默認的設置，即默認的文件增長設置（按數據文件和事務日志文件大小的10%來增長）
而在《SQLSERVER企業級平臺管理實踐》里提到
設置數據庫自動增長注意要點
要設置成按固定大小增長，而不能按比例 對于小的數據庫，設置一次增長50MB到100MB，大的數據庫，一次增長100MB到200MB
因為這個數據庫才大概26MB的大小（mdf文件26MB），屬于小型數據庫，我就設置事務日志文件一次增長50MB
而數據文件大小的增長設置可以不用動
然后我就使用下面的SQL語句，截斷事務日志，收縮事務日志文件，由于我不知道事務日志文件壓縮多少
我就設置了收縮到10MB
1 --腳本 2 USE GPOSDB 3 GO 4 --截斷日志 5 BACKUP LOG GPOSDB TO DISK='d:\gposdblogbackup2013-8-5.trn' with TRUNCATE_ONLY 6 GO 7 8 --查看一下數據庫信息 9 EXEC sp_helpdb @dbname='gposdb' 10 GO 11 12 --收縮事務日志文件到10MB 13 DBCC SHRINKFILE(GPOSDB_log,10) 14 GO 15 16 --再查看一下數據庫信息 17 EXEC sp_helpdb @dbname='gposdb' 18 GO
執行完SQL語句之后，再看一下事務日志文件的大小和數據庫情況
----------------------------華麗的分割線-----------------------------------
關于另外的那兩個錯誤
我的筆記本電腦里的SQLSERVER 也出現了下面兩個SQL日志信息，所以本人覺得下面兩個錯誤問題不大
2013-08-05 00:37:12.04 spid11s The current event was not reported to the Windows Events log. Operating system error = 31(連到系統上的設備沒有發揮作用。). You may need to clear the Windows Events log if it is full.
2013-08-05 19:20:47.78 spid51 The current event was not reported to the Windows Events log. Operating system error = 1717(接口未知。). You may need to clear the Windows Events log if it is full.
而下面這個SQL日志信息我估計是當時掉電造成SQLSERVER terminating
2013-08-05 19:20:22.60 服務器 SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
-------------------------------華麗的分割線------------------------------
這個解決辦法有沒有效明天就知道了
因為客戶的辦公室那里電壓不穩定，剛好UPS又壞了，經常停電，一停電電腦又關機了
我擔心這樣每天這樣毫無防御地關機會影響到硬盤，電腦經常突然掉電硬盤又可能損壞了而造成上面的情況
明天用HDTUNE檢查一下硬盤看一下有沒有壞道
由于隨筆是在酒店寫的，所以格式就將就一下吧O(∩_∩)O
-------------------------------------華麗的分割線-------------------------
因為下面兩個錯誤沒有錯誤號，由于本人資歷尚淺，希望知道下面兩個錯誤的錯誤原因的可以告訴我一聲
2013-08-05 00:37:12.04 spid11s The current event was not reported to the Windows Events log. Operating system error = 31(連到系統上的設備沒有發揮作用。). You may need to clear the Windows Events log if it is full.
2013-08-05 19:20:47.78 spid51 The current event was not reported to the Windows Events log. Operating system error = 1717(接口未知。). You may need to clear the Windows Events log if it is full.
如有不對的地方，敬請拍磚O(∩_∩)O
---------------------------------------------------------
2013-8-6補充
今天早上我到客戶那里做了2個事情
1：把數據文件自動增長設置為一次自動增長50MB（雖然只有25MB大小而且數據量不像事物日志文件那樣猛增，
為保險起見還是設置一下比較好）
2：使用HDTUNE檢查硬盤，完整掃描了一次硬盤之后發現沒有壞道
看了一下事物日志文件，大小為75MB，而且觀察了到現在為止還沒有發生連接超時的情況
原因：我懷疑是SQLSERVER需要格式化的硬盤空間太大，因為每次增長10%，那么事物日志文件每次需要增長22.5GB*10%=2.25GB
而且客戶的電腦配置比較低，估計因為SQLSERVER格式化的時候出現問題，30秒都沒有格式化好這部分空間
Autogrow of file 'GPOSDB_log' in database 'GPOSDB' was cancelled by user or timed out after 31000 milliseconds
---------------------------------------------------------
2013-8-7補充
今天再看2013-8-6和2013-8-7這兩天的Windows日志和SQL ERROR LOG，沒有發現彈出“連接超時”對話框和'GPOSDB_log'事務日志文件
增長失敗的日志信息
還有事務日志文件現在有250MB大小
證明事務日志文件增長了，而且增長還算順利，從中也證明了我這個方法是有效的o(∩_∩)o
2013-11-21補充：
2013-08-05 22:05:43.46 登錄 錯誤: 18456，嚴重性: 14，狀態: 7。
http://blogs.msdn.com/b/apgcdsd/archive/2011/09/27/kerberos-ntlm-sql-server.aspx
當你看到Login failed for user <username>時，這其實是一個授權(authorization)錯誤。
此時，SQL Server已經識別出了要求登錄用戶，但是由于SQL Server內部的安全相關的設置，登錄操作沒有成功。
我們常見的18456錯誤就屬于這類錯誤： Error: 18456, Severity: 14, State: XX.
我們可以通過State:XX來判斷到底是什么原因導致的授權失敗。
但是，由于擔心惡意的客戶端可能通過state來猜測失敗原因并采取相應手段來攻擊服務器，State是不能返回客戶端的。
它會被寫入SQL的ERROELOG文件。
失敗state對應的原因：
1 通用錯誤
2 遠程登錄的登錄在服務器中不存在
3，4 解密內存中加密的密碼失敗
5 SQL登錄不存在
6 連接的SQL登錄不符合登錄類型列表
7 登錄功能關閉
8 密碼不符合
9密碼不可用（登錄時正在修改密碼）
10 賬戶策略驗證失敗
如果以下密碼策略檢查評估返回假：
（1）登錄成功
（2）密碼已過期
（3）用戶需要更改密碼
然后我們設置登錄狀態，并返回一個失敗state10
11 windows驗證沒有被授權可以連接
12 SQL驗證沒有被授權可以連接
13 服務器暫停，禁止所有登陸
14 登錄不能用于此連接的接口類型
15 連接字符串中指定的數據庫無效
16個默認數據庫是無效的
17默認語言為無效
18這種登錄類型密碼不能改變
19無法解密加密的內存中的新密碼
文章列表
全站熱搜
留言列表
