SQL Server 2005 서비스가 시작될 때 기록되는 ERRORLOG를 하나씩 살펴 봤습니다. SQL Engine 이 시작될 때 어떠한 정보들이 로그 파일에 기록되는지 열어 봅니다.
[환경]
Windows Server 2003 (x86)
SQL Server 2005
[ERRORLOG]
첫 로그 시작 부분을 통해 서비스 시작 시간, SQL Server 엔진 버전, OS 버전 정보를 확인할 수 있습니다.
2010-01-23 12:36:39.930 Server Microsoft SQL Server 2005 - 9.00.3282.00 (Intel X86)
Aug 5 2008 01:01:05
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Aug 5 2008 01:01:05
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
SQL Server 의 Windows ProcessID, 인증 모드, ERRORLOG 가 위치한 전체 경로를 확인할 수 있습니다.
2010-01-23 12:36:39.930 Server (c) 2005 Microsoft Corporation.
2010-01-23 12:36:39.930 Server All rights reserved.
2010-01-23 12:36:39.930 Server Server process ID is 4376.
2010-01-23 12:36:39.930 Server Authentication mode is MIXED.
2010-01-23 12:36:39.930 Server Logging SQL Server messages in file 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2010-01-23 12:36:39.930 Server All rights reserved.
2010-01-23 12:36:39.930 Server Server process ID is 4376.
2010-01-23 12:36:39.930 Server Authentication mode is MIXED.
2010-01-23 12:36:39.930 Server Logging SQL Server messages in file 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
이전에 실행된 PID와 날짜, 시간 정보를 확인할 수 있습니다.
2010-01-23 12:36:39.930 Server This instance of SQL Server last reported using a process ID of 712 at 2010-01-23 오후 12:08:21 (local) 2010-01-23 오전 3:08:21 (UTC). This is an informational message only; no user action is required.
레지스트리 또는 Command 명령으로 입력된 시작 파라미터 설정 값을 볼 수 있습니다. 파라미터가 잘못되었을 경우 이 로그를 통해 확인할 수 있습니다.
2010-01-23 12:36:39.930 Server Registry startup parameters:
2010-01-23 12:36:39.930 Server -d E:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2010-01-23 12:36:39.930 Server -e E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2010-01-23 12:36:39.930 Server -l E:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2010-01-23 12:36:39.930 Server -d E:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2010-01-23 12:36:39.930 Server -e E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2010-01-23 12:36:39.930 Server -l E:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
CPU 개수, AWE 활성 정보를 볼 수 있습니다.
2010-01-23 12:36:39.960 서버 SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2010-01-23 12:36:39.960 서버 Detected 8 CPUs. This is an informational message; no user action is required.
2010-01-23 12:36:40.120 서버 Address Windowing Extensions is enabled. This is an informational message only; no user action is required.
2010-01-23 12:36:40.200 서버 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-01-23 12:36:39.960 서버 Detected 8 CPUs. This is an informational message; no user action is required.
2010-01-23 12:36:40.120 서버 Address Windowing Extensions is enabled. This is an informational message only; no user action is required.
2010-01-23 12:36:40.200 서버 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.
DTC 트랜잭션 초기화 로그가 확인됩니다.
2010-01-23 12:36:40.210 서버 Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2010-01-23 12:36:41.530 서버 Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2010-01-23 12:36:41.530 서버 Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
데이터베이스 미러링을 사용할 수 있습니다. SP1 이후 버전 또는 RTM에서는 Trace Flag 1400 활성화 후 가능한 기능입니다.
2010-01-23 12:36:41.530 서버 Database mirroring has been enabled on this instance of SQL Server.
이 단계부터 SYSTEM, USER 데이터베이스가 시작됩니다. master 데이터베이스가 시작됩니다.
2010-01-23 12:36:41.530 spid5s Starting up database 'master'.
2010-01-23 12:36:42.170 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2010-01-23 12:36:42.170 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
Default Trace 가 sa 계정으로 시작되고 resource 데이터베이스가 시작되었습니다.
2010-01-23 12:36:42.200 spid5s SQL Trace ID 1 was started by login "sa".
2010-01-23 12:36:42.200 spid5s Starting up database 'mssqlsystemresource'.
2010-01-23 12:36:42.210 spid5s The resource database build version is 9.00.3282. This is an informational message only. No user action is required.
2010-01-23 12:36:42.200 spid5s Starting up database 'mssqlsystemresource'.
2010-01-23 12:36:42.210 spid5s The resource database build version is 9.00.3282. This is an informational message only. No user action is required.
NETBIOS 이름 표시, model 데이터베이스 실행, tempdb 가 생성 되며 Service Broker 프로토콜 시작 여부를 확인합니다.
2010-01-23 12:36:42.320 spid5s Server name is 'laigodb'. This is an informational message only. No user action is required.
2010-01-23 12:36:42.320 spid5s The NETBIOS name of the local node that is running the server is 'laigodb01'. This is an informational message only. No user action is required.
2010-01-23 12:36:42.320 spid9s Starting up database 'model'.
2010-01-23 12:36:42.350 spid9s Clearing tempdb database.
2010-01-23 12:36:42.460 spid9s Starting up database 'tempdb'.
2010-01-23 12:36:42.480 spid9s Analysis of database 'tempdb' (2) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2010-01-23 12:36:42.810 spid12s The Service Broker protocol transport is disabled or not configured.
2010-01-23 12:36:42.810 spid12s The Database Mirroring protocol transport is disabled or not configured.
2010-01-23 12:36:42.820 spid12s Service Broker manager has started.
2010-01-23 12:36:42.930 서버 A self-generated certificate was successfully loaded for encryption.
2010-01-23 12:36:42.320 spid5s The NETBIOS name of the local node that is running the server is 'laigodb01'. This is an informational message only. No user action is required.
2010-01-23 12:36:42.320 spid9s Starting up database 'model'.
2010-01-23 12:36:42.350 spid9s Clearing tempdb database.
2010-01-23 12:36:42.460 spid9s Starting up database 'tempdb'.
2010-01-23 12:36:42.480 spid9s Analysis of database 'tempdb' (2) is 100% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2010-01-23 12:36:42.810 spid12s The Service Broker protocol transport is disabled or not configured.
2010-01-23 12:36:42.810 spid12s The Database Mirroring protocol transport is disabled or not configured.
2010-01-23 12:36:42.820 spid12s Service Broker manager has started.
2010-01-23 12:36:42.930 서버 A self-generated certificate was successfully loaded for encryption.
1433 서비스 포트 Listening 확인, Named pipe provider 가 준비되며 만약 DAC(Dedicated Admin Connection)가 활성화 되어 있다면 DAC 서비스 포트도 여기서 보이게 됩니다.
2010-01-23 12:36:42.950 서버 Server is listening on [ 192.168.100.100 <ipv4> 1433].
2010-01-23 12:36:42.950 서버 Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2010-01-23 12:36:42.950 서버 Server named pipe provider is ready to accept connection on [ \\.\pipe\$$\laigodb\sql\query ].
2010-01-23 12:36:42.980 서버 SQL Server is now ready for client connections. This is an informational message; no user action is required.
2010-01-23 12:36:42.950 서버 Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2010-01-23 12:36:42.950 서버 Server named pipe provider is ready to accept connection on [ \\.\pipe\$$\laigodb\sql\query ].
2010-01-23 12:36:42.980 서버 SQL Server is now ready for client connections. This is an informational message; no user action is required.
msdb, 사용자 데이터베이스가 시작되는 단계입니다. 처리되지 않은 트랜잭션이 있을 경우 자동 복구됩니다.
2010-01-23 12:36:43.010 spid22s Starting up database 'msdb'.
2010-01-23 12:36:43.010 spid25s Starting up database 'laigodb'.
2010-01-23 12:36:49.650 spid25s Analysis of database 'laigodb' (5) is 38% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2010-01-23 12:39:49.400 spid25s Recovery of database 'laigodb' (5) is 8% complete (approximately 1071 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2010-01-23 12:39:49.920 spid25s CHECKDB for database 'laigodb' finished without errors on 2008-11-24 17:27:34.347 (local time). This is an informational message only; no user action is required.
2010-01-23 12:58:42.140 spid5s Recovery of database 'laigodb' (5) is 100% complete (approximately 0 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2010-01-23 12:44:42.900 spid52 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2010-01-23 12:58:42.140 spid5s 1 transactions rolled back in database 'laigodb' (5). This is an informational message only. No user action is required.
2010-01-23 12:58:42.140 spid5s Recovery is writing a checkpoint in database 'laigodb' (5). This is an informational message only. No user action is required.
2010-01-23 12:58:48.750 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2010-01-23 12:58:48.750 spid5s Recovery is complete. This is an informational message only. No user action is required.
2010-01-23 12:36:43.010 spid25s Starting up database 'laigodb'.
2010-01-23 12:36:49.650 spid25s Analysis of database 'laigodb' (5) is 38% complete (approximately 0 seconds remain). This is an informational message only. No user action is required.
2010-01-23 12:39:49.400 spid25s Recovery of database 'laigodb' (5) is 8% complete (approximately 1071 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2010-01-23 12:39:49.920 spid25s CHECKDB for database 'laigodb' finished without errors on 2008-11-24 17:27:34.347 (local time). This is an informational message only; no user action is required.
2010-01-23 12:58:42.140 spid5s Recovery of database 'laigodb' (5) is 100% complete (approximately 0 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
2010-01-23 12:44:42.900 spid52 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2010-01-23 12:58:42.140 spid5s 1 transactions rolled back in database 'laigodb' (5). This is an informational message only. No user action is required.
2010-01-23 12:58:42.140 spid5s Recovery is writing a checkpoint in database 'laigodb' (5). This is an informational message only. No user action is required.
2010-01-23 12:58:48.750 spid5s Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2010-01-23 12:58:48.750 spid5s Recovery is complete. This is an informational message only. No user action is required.
[참고자료]
SQL Server 2005 Practical Troubleshooting - by Henderson
작성자 : Lai Go / 작성일자 : 2010.02.22
