SQL 질문과 답변 게시판
안녕하세요.
다름이 아니라 SQL Server Enterprise Manger에서 SQL Server 에이전트의 작업에서 DB 유지 관리 계획으로
매일 DB 백업을 실시하고 있는데 한 10일 전부터 Back UP 작업이 실패한다고 뜹니다.
도무지 원인 파악하기가 힘들어서 이렇게 요청드립니다.
쿼리 분석기에서 로그 파일 확인 결과 아래와 같은 로그파일이 확인되었습니다.
더 필요하신 자료 있으면 요청하시면 바로 올려드리겠습니다.
ERRORLOG.1
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2010-07-13 16:49:49.87 server Microsoft SQL Server 2000 - 8.00.2282 (Intel X86)
Dec 30 2008 02:22:41
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2010-07-13 16:49:49.89 server Copyright (C) 1988-2002 Microsoft Corporation.
2010-07-13 16:49:49.89 server All rights reserved.
2010-07-13 16:49:49.89 server Server Process ID is 764.
2010-07-13 16:49:49.89 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2010-07-13 16:49:49.89 server SQL Server is starting at priority class 'normal'(4 CPUs detected).
2010-07-13 16:49:49.96 server SQL Server configured for thread mode processing.
2010-07-13 16:49:49.98 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2010-07-13 16:49:50.06 server Attempting to initialize Distributed Transaction Coordinator.
2010-07-13 16:49:51.09 spid4 Starting up database 'master'.
2010-07-13 16:49:51.23 server Using 'SSNETLIB.DLL' version '8.0.2039'.
2010-07-13 16:49:51.23 spid5 Starting up database 'model'.
2010-07-13 16:49:51.23 server SQL server listening on 192.168.1.6: 1433.
2010-07-13 16:49:51.23 server SQL server listening on 127.0.0.1: 1433.
2010-07-13 16:49:51.24 server SQL server listening on TCP, Shared Memory, Named Pipes.
2010-07-13 16:49:51.24 server SQL Server is ready for client connections
2010-07-13 16:49:51.24 spid4 Server name is 'CN_PAS'.
2010-07-13 16:49:51.24 spid8 Starting up database 'msdb'.
2010-07-13 16:49:51.24 spid9 Starting up database 'pubs'.
2010-07-13 16:49:51.24 spid10 Starting up database 'Northwind'.
2010-07-13 16:49:51.24 spid11 Starting up database 'PAS'.
2010-07-13 16:49:51.26 spid12 Starting up database 'INTRANET'.
2010-07-13 16:49:51.26 spid13 Starting up database 'KAV'.
2010-07-13 16:49:51.26 spid14 Starting up database 'label'.
2010-07-13 16:49:51.28 spid15 Starting up database 'PAS3'.
2010-07-13 16:49:51.43 spid5 Clearing tempdb database.
2010-07-13 16:49:51.43 spid15 Analysis of database 'PAS3' (14) is 100% complete (approximately 0 more seconds)
2010-07-13 16:49:51.84 spid5 Starting up database 'tempdb'.
2010-07-13 16:49:51.84 spid5 Analysis of database 'tempdb' (2) is 100% complete (approximately 0 more seconds)
2010-07-13 16:49:52.45 spid4 Recovery complete.
2010-07-13 16:49:52.45 spid4 SQL global counter collection task is created.
2010-07-13 16:49:53.60 spid51 Using 'xpsqlbot.dll' version '2000.80.2039' to execute extended stored procedure 'xp_qv'.
2010-07-13 16:50:33.39 spid64 Using 'xpstar.dll' version '2000.80.2282' to execute extended stored procedure 'xp_sqlagent_enum_jobs'.
2010-07-13 17:01:20.35 server SQL Server terminating because of system shutdown.
(37 row(s) affected)
ERRORLOG.2
---------------------------------------------------------------------------------------------------------------------------------
2010-07-13 16:49:13.59 server Microsoft SQL Server 2000 - 8.00.2282 (Intel X86)
Dec 30 2008 02:22:41
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2010-07-13 16:49:13.59 server Copyright (C) 1988-2002 Microsoft Corporation.
2010-07-13 16:49:13.59 server All rights reserved.
2010-07-13 16:49:13.59 server Server Process ID is 6712.
2010-07-13 16:49:13.59 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2010-07-13 16:49:13.59 server SQL Server is starting at priority class 'normal'(4 CPUs detected).
2010-07-13 16:49:13.71 server SQL Server configured for thread mode processing.
2010-07-13 16:49:13.73 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2010-07-13 16:49:13.84 server Attempting to initialize Distributed Transaction Coordinator.
2010-07-13 16:49:15.04 spid4 Starting up database 'master'.
2010-07-13 16:49:15.26 server Using 'SSNETLIB.DLL' version '8.0.2039'.
2010-07-13 16:49:15.26 spid5 Starting up database 'model'.
2010-07-13 16:49:15.26 spid4 Server name is 'CN_PAS'.
2010-07-13 16:49:15.26 spid8 Starting up database 'msdb'.
2010-07-13 16:49:15.26 spid9 Starting up database 'pubs'.
2010-07-13 16:49:15.26 spid10 Starting up database 'Northwind'.
2010-07-13 16:49:15.26 spid11 Starting up database 'PAS'.
2010-07-13 16:49:15.26 spid12 Starting up database 'INTRANET'.
2010-07-13 16:49:15.26 spid13 Starting up database 'KAV'.
2010-07-13 16:49:15.26 spid14 Starting up database 'label'.
2010-07-13 16:49:15.26 spid15 Starting up database 'PAS3'.
2010-07-13 16:49:15.37 spid5 Clearing tempdb database.
2010-07-13 16:49:15.42 spid15 Analysis of database 'PAS3' (14) is 100% complete (approximately 0 more seconds)
2010-07-13 16:49:15.45 server SQL server listening on 192.168.1.6: 1433.
2010-07-13 16:49:15.45 server SQL server listening on 127.0.0.1: 1433.
2010-07-13 16:49:15.60 server SQL server listening on TCP, Shared Memory, Named Pipes.
2010-07-13 16:49:15.60 server SQL Server is ready for client connections
2010-07-13 16:49:15.87 spid5 Starting up database 'tempdb'.
2010-07-13 16:49:16.04 spid5 Analysis of database 'tempdb' (2) is 100% complete (approximately 0 more seconds)
2010-07-13 16:49:17.07 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:17.07 spid53 Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
2010-07-13 16:49:17.18 spid53 DBCC TRACEON 1717, server process ID (SPID) 53.
2010-07-13 16:49:25.75 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:25.75 spid53 Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install..
2010-07-13 16:49:25.79 spid53 DBCC TRACEOFF 1717, server process ID (SPID) 53.
2010-07-13 16:49:26.35 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:26.35 spid53 Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
2010-07-13 16:49:26.37 spid53 DBCC TRACEON 1717, server process ID (SPID) 53.
2010-07-13 16:49:27.70 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:27.70 spid53 Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install..
2010-07-13 16:49:27.70 spid53 DBCC TRACEOFF 1717, server process ID (SPID) 53.
2010-07-13 16:49:27.75 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:27.75 spid53 Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
2010-07-13 16:49:29.29 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:29.29 spid53 Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install..
2010-07-13 16:49:29.87 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:29.87 spid53 Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
2010-07-13 16:49:33.26 spid53 Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install..
2010-07-13 16:49:33.75 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:33.75 spid53 Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
2010-07-13 16:49:34.56 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:34.56 spid53 Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install..
2010-07-13 16:49:36.71 spid4 Recovery complete.
2010-07-13 16:49:36.71 spid4 SQL global counter collection task is created.
2010-07-13 16:49:38.82 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:38.82 spid53 Configuration option 'allow updates' changed from 1 to 1. Run the RECONFIGURE statement to install..
2010-07-13 16:49:41.45 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:41.45 spid53 Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install..
2010-07-13 16:49:41.98 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:41.98 spid53 Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
2010-07-13 16:49:42.00 spid53 DBCC TRACEON 1717, server process ID (SPID) 53.
2010-07-13 16:49:42.03 spid53 DBCC TRACEOFF 1717, server process ID (SPID) 53.
2010-07-13 16:49:42.03 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:42.03 spid53 Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install..
2010-07-13 16:49:42.51 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:42.51 spid53 Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
2010-07-13 16:49:42.53 spid53 DBCC TRACEON 1717, server process ID (SPID) 53.
2010-07-13 16:49:42.95 spid53 DBCC TRACEOFF 1717, server process ID (SPID) 53.
2010-07-13 16:49:43.00 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:43.00 spid53 Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install..
2010-07-13 16:49:43.53 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:43.53 spid53 Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
2010-07-13 16:49:43.56 spid53 DBCC TRACEON 1717, server process ID (SPID) 53.
2010-07-13 16:49:44.67 spid53 Using 'xpstar.dll' version '2000.80.2282' to execute extended stored procedure 'xp_instance_regread'
2010-07-13 16:49:44.84 spid53 DBCC TRACEOFF 1717, server process ID (SPID) 53.
2010-07-13 16:49:44.84 spid53 Error: 15457, Severity: 0, State: 1
2010-07-13 16:49:44.84 spid53 Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install..
2010-07-13 16:49:46.73 spid4 SQL Server is terminating due to 'stop' request from Service Control Manager.
(82 row(s) affected)
ERRORLOG.3
-----------------------------------------------------------------------------------
2010-07-13 12:10:03.12 server Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2010-07-13 12:10:03.12 server Copyright (C) 1988-2002 Microsoft Corporation.
2010-07-13 12:10:03.12 server All rights reserved.
2010-07-13 12:10:03.12 server Server Process ID is 1480.
2010-07-13 12:10:03.12 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2010-07-13 12:10:03.15 server SQL Server is starting at priority class 'normal'(4 CPUs detected).
2010-07-13 12:10:03.28 server SQL Server configured for thread mode processing.
2010-07-13 12:10:03.28 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2010-07-13 12:10:03.35 server Attempting to initialize Distributed Transaction Coordinator.
2010-07-13 12:10:07.76 spid3 Starting up database 'master'.
2010-07-13 12:10:08.01 server Using 'SSNETLIB.DLL' version '8.0.2039'.
2010-07-13 12:10:08.01 spid5 Starting up database 'model'.
2010-07-13 12:10:08.04 server SQL server listening on 192.168.1.6: 1433.
2010-07-13 12:10:08.04 server SQL server listening on 127.0.0.1: 1433.
2010-07-13 12:10:08.06 server SQL server listening on TCP, Shared Memory, Named Pipes.
2010-07-13 12:10:08.06 server SQL Server is ready for client connections
2010-07-13 12:10:08.06 spid3 Server name is 'CN_PAS'.
2010-07-13 12:10:08.06 spid8 Starting up database 'msdb'.
2010-07-13 12:10:08.07 spid9 Starting up database 'pubs'.
2010-07-13 12:10:08.07 spid10 Starting up database 'Northwind'.
2010-07-13 12:10:08.07 spid11 Starting up database 'PAS'.
2010-07-13 12:10:08.15 spid12 Starting up database 'INTRANET'.
2010-07-13 12:10:08.15 spid13 Starting up database 'KAV'.
2010-07-13 12:10:08.17 spid14 Starting up database 'label'.
2010-07-13 12:10:08.35 spid15 Starting up database 'PAS3'.
2010-07-13 12:10:08.46 spid5 Clearing tempdb database.
2010-07-13 12:10:08.85 spid15 Analysis of database 'PAS3' (14) is 100% complete (approximately 0 more seconds)
2010-07-13 12:10:09.01 spid5 Starting up database 'tempdb'.
2010-07-13 12:10:09.12 spid5 Analysis of database 'tempdb' (2) is 100% complete (approximately 0 more seconds)
2010-07-13 12:10:09.87 spid3 Recovery complete.
2010-07-13 12:10:09.87 spid3 SQL global counter collection task is created.
2010-07-13 12:10:11.40 spid51 Using 'xpsqlbot.dll' version '2000.80.2039' to execute extended stored procedure 'xp_qv'.
2010-07-13 12:26:56.59 spid76 Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure 'sp_MSgetversion'.
2010-07-13 12:40:45.93 spid85 Using 'xplog70.dll' version '2000.80.2039' to execute extended stored procedure 'xp_msver'.
2010-07-13 12:42:09.37 spid76 Error: 15457, Severity: 0, State: 1
2010-07-13 12:42:09.37 spid76 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..
2010-07-13 16:48:55.76 spid3 SQL Server is terminating due to 'stop' request from Service Control Manager.
(40 row(s) affected)
ERRORLOG.4
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2010-07-13 00:00:22.00 backup Database backed up: Database: INTRANET, creation date(time): 2007/09/06(11:48:33), pages dumped: 12293, first LSN: 286:512:1, last LSN: 286:514:1, number of dump devices: 1, device information: (FILE=65, TYPE=DISK: {'E:\BackUp_DB\intranet\intranet'}).
2010-07-13 10:51:55.27 backup Database log truncated: Database: pas.
2010-07-13 10:56:16.57 backup Database log truncated: Database: pas3.
2010-07-13 11:00:26.89 backup Database log truncated: Database: intranet.
2010-07-13 11:27:41.49 backup Database backed up: Database: PAS, creation date(time): 2007/07/07(15:01:05), pages dumped: 1266178
, first LSN: 763:3491:1, last LSN: 763:4910:1, number of dump devices: 1 , device information: (FILE=1, TYPE=DISK:'D:\BackupDB\PAS\aaa.bak'}).
2010-07-13 11:54:18.64 spid95 Error: 15457, Severity: 0, State: 1
2010-07-13 11:54:18.64 spid95 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..
2010-07-13 11:54:20.94 spid95 Using 'xplog70.dll' version '2000.80.2039' to execute extended stored procedure 'xp_msver'.
2010-07-13 11:55:27.13 spid130 Error: 15457, Severity: 0, State: 1
2010-07-13 11:55:27.13 spid130 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..
2010-07-13 12:06:20.94 server SQL Server terminating because of system shutdown.
(78 row(s) affected)
![[레벨:76]](http://www.sqler.com/modules/point/icons/warrock_17x17/76.gif "포인트:11599point (15%), 레벨:76/100"){kind=icon}
라이고
![[레벨:6]](http://www.sqler.com/modules/point/icons/warrock_17x17/6.gif "포인트:75point (11%), 레벨:6/100"){kind=icon}
Irin답변 감사드립니다.
매일 DB Backup을 진행하고 있으며 약 6월 23일 경부터 DB Backup 작업이 실패하고 있습니다.
쿼리분석기에서 Backup은 정상적으로 실행됩니다.
현재 Agent에서 예약된 작업 중 하나의 데이터 베이스에서만 실행이 되지 않습니다. SQL Server Agent
서비스 실행 권한(SQLSERVERAGENT Properties)을 Local System account로 지정되어 있습니다.
또한 SQL Server Agent의 작업에서 해당 DB 유지관리 계획의 Owner를 sa가 아닌 Windows의 administrator 계정으로
실행을 해도 작업이 이루어지지 않고 있습니다.
현재 서버는 중국쪽에 위치하고 있으며 한국에서 원격 Windows administrator 계정으로 작업 중입니다.
IrinSteup Id Step Name Run At Result Notifications Run
0 [Job Outcome] 7/15/2010 14:51PM Failed 00:0
Content : The job failed. The Job was invoked by User CN_PAS\Administrator. The last step to run was step 1 (단계 1).
Steup Id Step Name Run At Result Notifications Run
1 단계 1 7/15/2010 14:51PM Failed 00:0
Content : Executed as user: NT AUTHORITY\SYSTEM. Failed executing API: CreateProcess. [SQLSTATE 42000]
(Error 22029). The step failed.
위와 같은 실패 오류 메시지가 기록되어집니다.
![[레벨:92]](http://www.sqler.com/modules/point/icons/warrock_17x17/92.gif "포인트:17229point (81%), 레벨:92/100"){kind=icon}
jevida(강성욱)
혹시 SQL버젼이 뭐에요?? 예전에 2000 쓸때 백업 파일 이름중에 ' _S' 들어가니깐 ....쿼리분석기에서는 바로 백업 되는데..Agent에서는 에러가 뜨더라구요.... (왠지 쓰고나니 질문 과 관련없느 대답인듯...)
![[레벨:17]](http://www.sqler.com/modules/point/icons/warrock_17x17/17.gif "포인트:593point (21%), 레벨:17/100"){kind=icon}
DK
![[레벨:50]](http://www.sqler.com/modules/point/icons/warrock_17x17/50.gif "포인트:5149point (73%), 레벨:50/100"){kind=icon}
석이
Irin네.. 트랜잭션 로그 백업은 포함되어 있지 않고 에러 발생한 DB의 복구 모델은 Full 모델입니다.
그리고 석이님 출장가신 차장님한테 물어보니 가능하다고 하십니다. 대신 제 노트북에서 중국으로 원격으로
들어가셔야 할 듯 합니다. ㅠ_ㅠ;
Irin석이님과 답변 주신 라이고님, 강성욱님, DK님 감사드립니다.
sqlmaint.exe 파일 불량으로 인한 백업 작업 실패였습니다.
그럼 좋은하루 되세요.
매일 Full (전체) 백업인가요?
쿼리분석기에서 Backup 하게 되면 정상적으로 되는지 확인해 보시기 바랍니다. 실패한다면 Errorlog 올려 주시구요.
Agent 에 등록된 예약된 작업에서만 실패한다면 SQL Server Agent 서비스의 실행 권한(관리도구-서비스) 이 무엇인지?
그리고 Agent 서비스를 재시작한 뒤 문제가 해결되는지 한 번 살펴봐야겠습니다.