Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 7,2000 » Data Corruption » Frequently Database goes suspect mode!
Add to briefcase
30 posts, Page 1 of 3
123»»»

Frequently Database goes suspect mode! Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
ComITSolutions
Posted Tuesday, May 12, 2009 3:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 31, 2010 7:52 AM
Points: 19, Visits: 43
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.

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..
Post #714808
GilaMonster
Posted Tuesday, May 12, 2009 6:54 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996
Can you check the previous error log. Looks like the DB was suspect before the restart, need to see if there's anything there that hints as to why it was marked suspect.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #714959
ComITSolutions
Posted Tuesday, May 12, 2009 7:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 31, 2010 7:52 AM
Points: 19, Visits: 43
Aftermy post system restarted many times. I dont have previous error log file. But when it happens Again I will make sure i will post both
Post #714970
GilaMonster
Posted Tuesday, May 12, 2009 7:30 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996
Any idea why allow_updates is getting set to 1? If something's messing badly with the system tables it could be the cause of the suspect status.

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #715017
ComITSolutions
Posted Tuesday, May 12, 2009 7:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 31, 2010 7:52 AM
Points: 19, Visits: 43
No Idea. Actually my application uses 5 databases, for past 2-3 months I have observed minimum 3-4 times database in suspect mode in each branch, and corrupting database in not consistent with database.
What I mean to say is, any one of 5 database required for my application goes in suspect mode. In some instance even distribution database also I faced this problem. So I am not able figure out. Next time I will check each and every minute point and post here.

For so many days I was recovering the suspected database using 
use master

go

sp_configure 'allow updates', 1

reconfigure with override

go

DBCC REBUILD_LOG( 'db_name', 'full name to new log file')

Use master

go

sp_configure 'allow updates', 0

Go


It is my mistake that I never tried to find the root cause for this problem.
Now it is frustrating
Post #715084
GilaMonster
Posted Tuesday, May 12, 2009 8:09 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:28 PM
Points: 37,730, Visits: 29,996
Have you checked for hardware-related errors in the system event logs, RAID controller logs, etc?

Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
Post #715104
ComITSolutions
Posted Tuesday, May 12, 2009 8:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 31, 2010 7:52 AM
Points: 19, Visits: 43
Yes, But I didn't find any thing. This is not happening in one place/System !!
Post #715127
ComITSolutions
Posted Tuesday, May 12, 2009 8:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 31, 2010 7:52 AM
Points: 19, Visits: 43
I forgot to mention, I am not doing regular database maintenance like shrinking, re indexing. The database size is apprx. 1.4 gb
Post #715153
Paul Randal
Posted Tuesday, May 12, 2009 1:46 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 1:23 PM
Points: 1,905, Visits: 1,601
You shouldn't be rebuilding the log - you're going to cause corruption and data loss that way. See Corruption: Last resorts that people try first....

It's likely that something in the h/w setup in each location is causing corruption. Gail - it's pretty hard to cause a suspect database by messing with the system tables.

You need to put the suspect databases into emergency mode and run DBCC CHECKDB on them to find out the corruption.

Do you have backups? Rebuilding the log is a horrible solution to dealing with corruption.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
Post #715436
ComITSolutions
Posted Tuesday, May 12, 2009 10:56 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, January 31, 2010 7:52 AM
Points: 19, Visits: 43
Thanks for the link Paul.

When my application starts, Physical files back-up is taken(auto). But the problem is transactions entered after back-up. There is no backup plan strategy, now I am working on it, because for so many days the application was in implementation.

The pain is, I restored the back-up file by attaching them. When my application try to insert a record the following error returned.

Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x000005246cc000 in file 'D:\Package\Database\BranchToHo_data.MDF

Connection Broken

Could you please guide the kind of Back-up Plan that I can Introduce.

Everyday minimum of 1000 records are updated in each database and
Data transferred minimum 2 times.

Thanks for your advice.
Post #715654
« Prev Topic | Next Topic »

Add to briefcase
30 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.