May 14, 2009 at 4:22 pm
CHECK your disk controller and find if disk caching is enabled or not?
May 15, 2009 at 2:40 am
nilmov (5/14/2009)
CHECK your disk controller and find if disk caching is enabled or not?
Yes, I have checked and attached screen shot of the settings.
Enable write caching on the disk option is enabled.
May 15, 2009 at 8:39 am
As you said that the DB goes to suspect now and then and also the h/w check came clean.
Only thing I can think of right now is disk controller.
Refer this KB which briefs about the impact of disk controller caching on SQL Server .
May 15, 2009 at 9:11 am
nilmov (5/15/2009)
As you said that the DB goes to suspect now and then and also the h/w check came clean.
Only thing I can think of right now is disk controller.
Refer this KB which briefs about the impact of disk controller caching on SQL Server .
http://support.microsoft.com/kb/86903%5B/quote%5D
Thanks for the link nilmov. I have read the Article and noted the contents.
In the article it is said that,
Even if the server uses a UPS, this does not guarantee the security of the cached writes..
And we have observed, many times Power and UPS failure in the branches, Is it better if I disable Disk Caching Option?
May 15, 2009 at 9:31 am
Yes.
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 15, 2009 at 10:15 pm
Thank you, Paul Randal,Gilamonster and nilmov.
I will disable disk write cache, and keep it under observation.
Now I have introduced Back-up plan.
Thanks again for the support and knowledge bestowed on me.
May 21, 2009 at 5:34 am
I too have same problem. Our database is going to suspect mode frequently but i have checked my server log files there is no error. My Database is working fine from morning to evening but when i come and start the server next day morning it is in suspect mode. I have checked the hardware issues but there is no error in Dump file also. I have checked the OS files, there is no problem. So what i can do please help me.
I too have small setup. I have One server in that We have installed Application, sql server 2000 and Symantec end point Mr4.
Here Is the Log file..
2009-05-21 08:48:02.60 spid8 Starting up database 'msdb'.
2009-05-21 08:48:02.60 spid9 Starting up database 'pubs'.
2009-05-21 08:48:02.62 spid10 Starting up database 'Northwind'.
2009-05-21 08:48:02.62 spid11 Starting up database 'adv_tvs'.
2009-05-21 08:48:02.65 spid12 Starting up database 'wheelzalerts'.
2009-05-21 08:48:03.28 server SQL server listening on TCP, Shared Memory, Named Pipes.
2009-05-21 08:48:03.28 server SQL Server is ready for client connections
2009-05-21 08:48:03.92 spid5 Clearing tempdb database.
2009-05-21 08:48:06.65 spid11 Error: 9003, Severity: 20, State: 1
2009-05-21 08:48:06.65 spid11 The LSN (29623:1009:1) passed to log scan in database 'adv_tvs' is invalid..
2009-05-21 08:48:07.06 spid11 Error: 3414, Severity: 21, State: 1
2009-05-21 08:48:07.06 spid11 Database 'adv_tvs' (database ID 7) could not recover. Contact Technical Support..
2009-05-21 08:48:09.35 spid5 Starting up database 'tempdb'.
2009-05-21 08:48:09.98 spid4 Recovery complete.
2009-05-21 08:48:09.98 spid4 SQL global counter collection task is created.
2009-05-21 08:48:10.92 spid51 Using 'xpsqlbot.dll' version '2000.80.194' to execute extended stored procedure 'xp_qv'.
2009-05-21 09:02:55.10 spid53 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2009-05-21 09:04:00.01 spid53 Error: 15457, Severity: 0, State: 1
2009-05-21 09:04:00.01 spid53 Configuration option 'allow updates' changed from 0 to 1. Run the RECONFIGURE statement to install..
2009-05-21 09:04:00.12 spid53 Error: 15457, Severity: 0, State: 1
2009-05-21 09:04:00.12 spid53 Configuration option 'allow updates' changed from 1 to 0. Run the RECONFIGURE statement to install..
2009-05-21 09:04:34.40 spid4 Starting up database 'adv_tvs'.
2009-05-21 09:04:34.43 spid4 Bypassing recovery for database 'adv_tvs' because it is marked BYPASS.
2009-05-21 09:04:34.95 spid4 SQL Server is terminating due to 'stop' request from Service Control Manager.
May 28, 2009 at 11:36 pm
Hi,
After removing Disk cache setting, It was bit relaxing, but Yesterday, again once of the banch system reported database corruption. The eror log says
2009-05-28 12:46:15.90 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-28 12:46:15.90 server Copyright (C) 1988-2002 Microsoft Corporation.
2009-05-28 12:46:15.90 server All rights reserved.
2009-05-28 12:46:15.90 server Server Process ID is 3768.
2009-05-28 12:46:15.90 server Logging SQL Server messages in file 'D:\Package\Database\ErrorLog'.
2009-05-28 12:46:15.96 server SQL Server is starting at priority class 'normal'(2 CPUs detected).
2009-05-28 12:46:16.03 server SQL Server configured for thread mode processing.
2009-05-28 12:46:16.03 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2009-05-28 12:46:16.04 spid4 Starting up database 'master'.
2009-05-28 12:46:16.53 server Using 'SSNETLIB.DLL' version '8.0.2039'.
2009-05-28 12:46:16.53 spid5 Starting up database 'model'.
2009-05-28 12:46:16.57 spid4 Server name is 'ACLRNH\SBS'.
2009-05-28 12:46:16.57 spid4 Skipping startup of clean database id 4
2009-05-28 12:46:16.57 spid4 Skipping startup of clean database id 5
2009-05-28 12:46:16.57 spid4 Skipping startup of clean database id 7
2009-05-28 12:46:16.57 spid4 Skipping startup of clean database id 8
2009-05-28 12:46:16.57 spid4 Skipping startup of clean database id 10
2009-05-28 12:46:16.57 spid4 Skipping startup of clean database id 11
2009-05-28 12:46:16.57 spid4 Skipping startup of clean database id 12
2009-05-28 12:46:16.57 spid9 Starting up database 'CommonInfo'.
2009-05-28 12:46:16.57 spid8 Starting up database 'ACLData'.
2009-05-28 12:46:16.57 server SQL server listening on 192.168.1.2: 4594.
2009-05-28 12:46:16.57 server SQL server listening on 117.198.36.27: 4594.
2009-05-28 12:46:16.57 server SQL server listening on 127.0.0.1: 4594.
2009-05-28 12:46:16.59 server SQL server listening on TCP, Shared Memory, Named Pipes.
2009-05-28 12:46:16.59 server SQL Server is ready for client connections
2009-05-28 12:46:17.20 spid5 Error: 9003, Severity: 20, State: 1
2009-05-28 12:46:17.20 spid5 The LSN (11:50:1) passed to log scan in database 'model' is invalid..
So again I am back to square one.:unsure:
May 29, 2009 at 10:44 am
Why did that system restart?
Do all the drives have appropriate battery-backup on them to allow writes to be flushed before powering down? Even without write-caching, if a drive is in the middle of writing something out and the power fails, it must be able to finish the write.
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 30, 2009 at 12:06 am
We have good backup ups and also we stop working before half an hour and we will take database backup then we shutdown the server. Its our daily routine, we are doing this from last 2 years. Now only we are facing this problem. I was on leave for past 10 days with in that time something happened to our database so our staff had recovered the database using the rebuild LDF query, then we are facing this problem. I have observed one morething, When this database goes to suspect mode our LDF size will be more than 1GB. We have enough space thats not a problem, but i don't know why this problem arrising..
Please help me..
May 30, 2009 at 12:31 am
Shripad - start a new thread please.
Do you have backups you can restore from? You said you have - why don't you restore from them?
From what you've said, you have IO subsystem caused corruption of the log file.
But please start a new thread in this forum to deal with this.
Thanks
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 30, 2009 at 1:15 am
Anyone who is experienced in 823 will be able to tell you that you need to get your hardware checked ....823 / torn page issue comes when the page or soem portion of the page is not readable ...
actually , the 8kb page is divided into 16 sectors 512 bytes each.if any or more sectors are ot readable while fetching from the disk , 823 comes up and the recovery is skipped ..
Paul can better explain you about 823 than me ....
But run checkdb and find out what page(s) is(are) corrupt .
then we can run dbcc page on those pages to find out the objects (or checkdb itself will tell)..
in any case this database is corrupt ...
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
June 1, 2009 at 12:58 am
Dear Paul,
I can restore the backup, its too easy to tell but its difficult to loose the data. My friends didn't took backup when i was on leave. I have to restore old data so that i will loose 10 days data. So is there any way i can check this issue? Can you tell me where exactly problem came? What kind of precautions i have to take to avoid the same in future?
June 1, 2009 at 1:01 am
Hi,
We have checked the DBCC command also there is no problem with database now. We have installed SQl server 2000 SP4 also. We have a job to check the consistency of database. So we are checking this on daily bases.
June 1, 2009 at 1:22 am
Shripad (6/1/2009)
Dear Paul,I can restore the backup, its too easy to tell but its difficult to loose the data. My friends didn't took backup when i was on leave. I have to restore old data so that i will loose 10 days data. So is there any way i can check this issue? Can you tell me where exactly problem came? What kind of precautions i have to take to avoid the same in future?
Please start a new thread with your problem rather than hijacking an existing one.
Thanks
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
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply