May 12, 2009 at 3:37 am
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..
May 12, 2009 at 6:54 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2009 at 7:01 am
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
May 12, 2009 at 7:30 am
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, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 12, 2009 at 7:59 am
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
May 12, 2009 at 8:09 am
Have you checked for hardware-related errors in the system event logs, RAID controller logs, etc?
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
May 12, 2009 at 8:24 am
Yes, But I didn't find any thing. This is not happening in one place/System !!
May 12, 2009 at 8:40 am
I forgot to mention, I am not doing regular database maintenance like shrinking, re indexing. The database size is apprx. 1.4 gb
May 12, 2009 at 1:46 pm
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
May 12, 2009 at 10:56 pm
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.
May 13, 2009 at 12:03 pm
Check this out: Importance of having the right backups
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
May 13, 2009 at 3:07 pm
ComITSolutions (5/12/2009)
When my application starts, Physical files back-up is taken(auto).
Do you mean that you just copy the database files (mdf and ldf) somewhere for a backup? Do you stop SQL or detach the database to do this?
Could you please guide the kind of Back-up Plan that I can Introduce.
As well as Paul's link, start with SQL Books Online, the section BACKUP DATABASE
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
May 13, 2009 at 10:42 pm
GilaMonster (5/13/2009)
Do you mean that you just copy the database files (mdf and ldf) somewhere for a backup? Do you stop SQL or detach the database to do this?
Yes, My application Stops Sql Server agent & SQl Server Takes Back-up(mdf and ldf files and starts SQL Server and Server Agent.
Thanks for the Link Paul & GilaMonster
May 13, 2009 at 10:50 pm
ComITSolutions (5/13/2009)
Yes, My application Stops Sql Server agent & SQl Server Takes Back-up(mdf and ldf files and starts SQL Server and Server Agent.
Don't. That's a very poor way to take SQL backups.
Schedule regular database backups (BACKUP DATABASE...). If point in time recovery is required, schedule regular log backups as well. Those backup files can then be copied to tape or whereever you're backing up to, with no downtime required.
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
May 14, 2009 at 12:16 am
I agree GilaMonster. with the help of links provided by you and Paul, Now I am working on back up plan.
GilaMonster (5/12/2009)
Have you checked for hardware-related errors in the system event logs, RAID controller logs, etc?
If same problem occurs again, what all things I should check and post here.
Paul Randal (5/12/2009)
It's likely that something in the h/w setup in each location is causing corruption.
What Hardware Configuration I should check?
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply