[RESOLVED] SQL Server 2000 Database suspect

[ComITSolutions]

Hi all,

I have developed a mini erp solution in VB 6, service pack 5. Using MSSql Server as Back-end.

My client Head office Uses MSSQL Server 2000 and its branches uses MSDE 2000. In Head office no problem what so ever, but at branches every now and then the database goes in suspect mode. What could be the reason?

There is data transfer between Ho to branches and vice-versa using replication.

Both at ho and branches Sql Service Pack 4 is Updated.

OS
Ho Windows Sever 2003
Branches XP Service Pack 2.

Thanx

[dglienna]

Hope they aren't doing it at the same time. That might be a problem

[ComITSolutions]

Data transfer can be activated from only Branch. There is a separate publication to send(PUSH).
and also Separate Publication at Ho for Receving(PULL) . The program is written in such way that both can not be activated at the same time.

[ComITSolutions]

here is the errorlog file content

2009-05-11 10:24:14.55 server Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 2)

2009-05-11 10:24:14.55 server Copyright (C) 1988-2002 Microsoft Corporation.
2009-05-11 10:24:14.55 server All rights reserved.
2009-05-11 10:24:14.55 server Server Process ID is 2136.
2009-05-11 10:24:14.55 server Logging SQL Server messages in file 'D:\Package\Database\ErrorLog'.
2009-05-11 10:24:14.58 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2009-05-11 10:24:14.91 server SQL Server configured for thread mode processing.
2009-05-11 10:24:14.91 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2009-05-11 10:24:15.09 spid3 Starting up database 'master'.
2009-05-11 10:24:15.38 server Using 'SSNETLIB.DLL' version '8.0.2039'.
2009-05-11 10:24:15.38 spid5 Starting up database 'model'.
2009-05-11 10:24:15.42 spid3 Server name is 'ACLXXX\SBS'.
2009-05-11 10:24:15.42 spid3 Skipping startup of clean database id 4
2009-05-11 10:24:15.42 spid3 Skipping startup of clean database id 5
2009-05-11 10:24:15.42 spid3 Skipping startup of clean database id 8
2009-05-11 10:24:15.42 spid3 Skipping startup of clean database id 9
2009-05-11 10:24:15.42 spid3 Skipping startup of clean database id 10
2009-05-11 10:24:15.42 spid3 Skipping startup of clean database id 13
2009-05-11 10:24:15.42 spid3 Skipping startup of clean database id 15
2009-05-11 10:24:15.42 spid3 Skipping startup of clean database id 16

2009-05-11 10:24:15.42 spid3 Starting up database 'BranchToHo'.
2009-05-11 10:24:15.75 spid3 Bypassing recovery for database 'BranchToHo' because it is marked SUSPECT.

2009-05-11 10:24:15.85 server SQL server listening on 192.168.0.1: 4593.
2009-05-11 10:24:15.85 server SQL server listening on 192.168.1.158: 4593.
2009-05-11 10:24:15.85 server SQL server listening on 117.195.10.216: 4593.
2009-05-11 10:24:15.85 server SQL server listening on 127.0.0.1: 4593.
2009-05-11 10:24:15.95 spid5 Clearing tempdb database.
2009-05-11 10:24:16.11 server SQL server listening on TCP, Shared Memory, Named Pipes.
2009-05-11 10:24:16.11 server SQL Server is ready for client connections
2009-05-11 10:24:16.49 spid5 Starting up database 'tempdb'.
2009-05-11 10:24:16.56 spid3 Recovery complete.
2009-05-11 10:24:16.56 spid3 SQL global counter collection task is created.
2009-05-11 10:24:16.63 spid3 Launched startup procedure 'sp_MSrepl_startup'
2009-05-11 10:24:25.10 spid51 Using 'xpstar.dll' version '2000.80.2039' to execute extended stored procedure 'sp_MSgetversion'.
2009-05-11 10:24:25.17 spid51 Starting up database 'msdb'.
2009-05-11 10:24:25.42 spid51 Starting up database 'distribution'.
2009-05-11 10:25:02.97 spid51 Starting up database 'ACLDATA'.
2009-05-11 10:25:03.13 spid51 Starting up database 'CommonInfo'.
2009-05-11 10:25:03.19 spid51 Starting up database 'DILDATA'.
2009-05-11 10:25:03.27 spid51 Starting up database 'DownLoads'.
2009-05-11 10:25:03.35 spid51 Starting up database 'DPLData'.
2009-05-11 10:25:03.42 spid51 Starting up database 'HoToBranch'.

2009-05-11 10:27:23.45 spid51 Error: 15457, Severity: 0, State: 1
2009-05-11 10:27:23.45 spid51 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..
2009-05-11 10:27:23.69 spid51 Using 'xplog70.dll' version '2000.80.2039' to execute extended stored procedure 'xp_msver'.
2009-05-11 10:27:46.28 spid51 Error: 15457, Severity: 0, State: 1
2009-05-11 10:27:46.28 spid51 Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
2009-05-11 10:28:38.59 spid51 Error: 947, Severity: 16, State: 1
2009-05-11 10:28:38.59 spid51 Error while closing database 'BranchToHo' cleanly..

[ComITSolutions]

After searching for solution in the net and trying our self, finaly found the problem and resolved. The database was going suspect mode during sudden power failure and UPS Failure.

To avoid database going to suspect mode I have made the following setting Changes

Open MyComputer select Drive on which sql server/MSDE is installed Look for Hardware tab in Properties(i.e. Right click Properties and select Hardware tab). Click Properties Button Select Policies Tab and uncheck Enable Write caching is enabled if checked.


from past 3 months we have not got single database in suspect mode. I just waited to verify and confirm before posting the solution.


Note: By doing this the performance of sql server is reduced but data is safe.