June 19, 2008 at 5:43 am
I am using SQL-2k,it's SQL SERVER AGENT stoped automatically therefore my maintanence plan was effected then the database of msdb marked suspected due this problem i am unable to take backup.
Kindly arrange to provide me some solution.
Error Log SQL SERVER AGENT as under:
2008-06-23 06:22:56.89 server Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2008-06-23 06:22:56.90 server Copyright (C) 1988-2000 Microsoft Corporation.
2008-06-23 06:22:56.90 server All rights reserved.
2008-06-23 06:22:56.90 server Server Process ID is 1548.
2008-06-23 06:22:56.92 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2008-06-23 06:22:56.93 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2008-06-23 06:22:57.00 server SQL Server configured for thread mode processing.
2008-06-23 06:22:57.00 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2008-06-23 06:22:57.03 server Attempting to initialize Distributed Transaction Coordinator.
2008-06-23 06:22:59.23 spid2 Starting up database 'master'.
2008-06-23 06:22:59.53 server Using 'SSNETLIB.DLL' version '8.0.311'.
2008-06-23 06:22:59.53 spid5 Starting up database 'model'.
2008-06-23 06:22:59.56 spid2 Server name is 'SERVER'.
2008-06-23 06:22:59.56 server SQL server listening on 192.168.1.1: 1433.
2008-06-23 06:22:59.56 spid8 Starting up database 'msdb'.
2008-06-23 06:22:59.56 server SQL server listening on 169.254.142.190: 1433.
2008-06-23 06:22:59.56 server SQL server listening on 127.0.0.1: 1433.
2008-06-23 06:22:59.56 spid9 Starting up database 'pubs'.
2008-06-23 06:22:59.57 spid10 Starting up database 'Northwind'.
2008-06-23 06:22:59.57 spid11 Starting up database 'Counter'.
2008-06-23 06:22:59.57 spid12 Starting up database 'IPO'.
2008-06-23 06:22:59.59 server SQL server listening on TCP, Shared Memory, Named Pipes.
2008-06-23 06:22:59.59 server SQL Server is ready for client connections
2008-06-23 06:22:59.62 spid13 Starting up database 'ECounter'.
2008-06-23 06:22:59.62 spid14 Starting up database 'SUBTREASURY'.
2008-06-23 06:22:59.62 spid15 Starting up database 'TREASURY'.
2008-06-23 06:22:59.82 spid8 Bypassing recovery for database 'msdb' because it is marked SUSPECT.
2008-06-23 06:22:59.85 spid5 Clearing tempdb database.
2008-06-23 06:22:59.89 spid8 Starting up database 'POSPCC'.
2008-06-23 06:23:00.26 spid5 Starting up database 'tempdb'.
2008-06-23 06:23:00.28 spid14 Starting up database 'POSPCCBACKUP'.
2008-06-23 06:23:00.29 spid10 Starting up database 'ROUTING'.
2008-06-23 06:23:00.76 spid9 Starting up database 'POST'.
2008-06-23 06:23:01.82 spid10 Starting up database 'BPRO'.
2008-06-23 06:23:02.23 spid12 Starting up database 'BPLOG'.
2008-06-23 06:23:02.95 spid13 Starting up database 'SIGN'.
2008-06-23 06:23:03.81 spid13 Starting up database 'SOSB'.
2008-06-23 06:23:04.56 spid13 Starting up database 'SECT37'.
2008-06-23 06:23:04.92 spid9 Starting up database 'eIOD'.
2008-06-23 06:23:16.48 spid8 Recovery is checkpointing database 'POSPCC' (12)
2008-06-23 06:23:16.75 spid2 Recovery complete.
2008-06-23 12:44:42.87 spid53 Using 'xpstar.dll' version '2000.80.194' to execute extended stored procedure 'sp_MSgetversion'.
2008-06-23 12:45:11.10 spid53 Error: 15457, Severity: 0, State: 1
2008-06-23 12:45:11.10 spid53 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..
2008-06-23 12:45:11.98 spid53 Using 'xplog70.dll' version '2000.80.194' to execute extended stored procedure 'xp_msver'.
2008-06-19 18:25:41 - ! [298] SQLServer Error: 4060, Cannot open database requested in login 'msdb'. Login fails. [SQLSTATE 42000]
2008-06-19 18:25:41 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start
2008-06-19 18:25:41 - ? [098] SQLServerAgent terminated (normally)
Subesh
June 19, 2008 at 6:27 am
Do you have a backup of msdb?
If you look in the SQL log file (the error log), are there any entries that indicate why the database is suspect?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 23, 2008 at 2:27 am
Hi,
Gila,i have not back-up of msdb database and the Error Log as under: Plssssss
2008-06-23 06:22:56.89 server Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
2008-06-23 06:22:56.90 server Copyright (C) 1988-2000 Microsoft Corporation.
2008-06-23 06:22:56.90 server All rights reserved.
2008-06-23 06:22:56.90 server Server Process ID is 1548.
2008-06-23 06:22:56.92 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2008-06-23 06:22:56.93 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2008-06-23 06:22:57.00 server SQL Server configured for thread mode processing.
2008-06-23 06:22:57.00 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2008-06-23 06:22:57.03 server Attempting to initialize Distributed Transaction Coordinator.
2008-06-23 06:22:59.23 spid2 Starting up database 'master'.
2008-06-23 06:22:59.53 server Using 'SSNETLIB.DLL' version '8.0.311'.
2008-06-23 06:22:59.53 spid5 Starting up database 'model'.
2008-06-23 06:22:59.56 spid2 Server name is 'SERVER'.
2008-06-23 06:22:59.56 server SQL server listening on 192.168.1.1: 1433.
2008-06-23 06:22:59.56 spid8 Starting up database 'msdb'.
2008-06-23 06:22:59.56 server SQL server listening on 169.254.142.190: 1433.
2008-06-23 06:22:59.56 server SQL server listening on 127.0.0.1: 1433.
2008-06-23 06:22:59.56 spid9 Starting up database 'pubs'.
2008-06-23 06:22:59.57 spid10 Starting up database 'Northwind'.
2008-06-23 06:22:59.57 spid11 Starting up database 'Counter'.
2008-06-23 06:22:59.57 spid12 Starting up database 'IPO'.
2008-06-23 06:22:59.59 server SQL server listening on TCP, Shared Memory, Named Pipes.
2008-06-23 06:22:59.59 server SQL Server is ready for client connections
2008-06-23 06:22:59.62 spid13 Starting up database 'ECounter'.
2008-06-23 06:22:59.62 spid14 Starting up database 'SUBTREASURY'.
2008-06-23 06:22:59.62 spid15 Starting up database 'TREASURY'.
2008-06-23 06:22:59.82 spid8 Bypassing recovery for database 'msdb' because it is marked SUSPECT.
2008-06-23 06:22:59.85 spid5 Clearing tempdb database.
2008-06-23 06:22:59.89 spid8 Starting up database 'POSPCC'.
2008-06-23 06:23:00.26 spid5 Starting up database 'tempdb'.
2008-06-23 06:23:00.28 spid14 Starting up database 'POSPCCBACKUP'.
2008-06-23 06:23:00.29 spid10 Starting up database 'ROUTING'.
2008-06-23 06:23:00.76 spid9 Starting up database 'POST'.
2008-06-23 06:23:01.82 spid10 Starting up database 'BPRO'.
2008-06-23 06:23:02.23 spid12 Starting up database 'BPLOG'.
2008-06-23 06:23:02.95 spid13 Starting up database 'SIGN'.
2008-06-23 06:23:03.81 spid13 Starting up database 'SOSB'.
2008-06-23 06:23:04.56 spid13 Starting up database 'SECT37'.
2008-06-23 06:23:04.92 spid9 Starting up database 'eIOD'.
2008-06-23 06:23:16.48 spid8 Recovery is checkpointing database 'POSPCC' (12)
2008-06-23 06:23:16.75 spid2 Recovery complete.
2008-06-23 12:44:42.87 spid53 Using 'xpstar.dll' version '2000.80.194' to execute extended stored procedure 'sp_MSgetversion'.
2008-06-23 12:45:11.10 spid53 Error: 15457, Severity: 0, State: 1
2008-06-23 12:45:11.10 spid53 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..
2008-06-23 12:45:11.98 spid53 Using 'xplog70.dll' version '2000.80.194' to execute extended stored procedure 'xp_msver'.
2008-06-19 18:25:41 - ! [298] SQLServer Error: 4060, Cannot open database requested in login 'msdb'. Login fails. [SQLSTATE 42000]
2008-06-19 18:25:41 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start
2008-06-19 18:25:41 - ? [098] SQLServerAgent terminated (normally)
Gila pls help me as possible mine Mob is 9968250487 and 9897549320, i am from india
June 23, 2008 at 6:11 am
There's nothing useful in there. Look through your older logs, see if there's any entry related to msdb that doesn't simply say 'bypassing recovery because msdb is marked suspect'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 25, 2008 at 6:24 am
Hi,
Gila, how r u.i m Subesh from India.i had worked on guidline for the purpose that i have taken a backup of 'msdb' from my another office and restore on suspected one.it has been restore and than re-start the SQL SERVER AGENT;this was started and my data-base safely recovered and all programm functionning properly.
Gila,heartest thx,Gila i m working in Indian Government(Postal Dept.) as System Administrator,if u will have a chance for visiting in India,simply ring me pls.
Subesh
June 25, 2008 at 6:32 am
subesh_bulbul (6/25/2008)
i had worked on guidline for the purpose that i have taken a backup of 'msdb' from my another office and restore on suspected one.it has been restore and than re-start the SQL SERVER AGENT;this was started and my data-base safely recovered and all programm functionning properly.
Great. Note that you will have to recreate any jobs, DTS packages, SSIS packages that were on the server that had the damaged database. You've also lost any backup hostory that you had.
I would strongly suggest you ensure that you hav backups of all databases, including the system databases (master, model, msdb) on all of your servers
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 26, 2008 at 12:06 am
Hi, Gila;lot of thnx for nice suggestions.We will again meet on the line.
Once Again Hearteast thnx.
Subesh
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply