December 9, 2005 at 8:26 am
On Windows 2000 and 2003 servers as well as XP development stations that were previously on MSSQL SP3x, we have experienced suspect databases after applying MSSQL SP4.
The servers have experienced an issue or two before, but we thought they were DTC related. The XP development stations have not experienced any suspect databases, (at least not in the last two years).
Resolving the suspect database:
A. stop mssql, stop antivirus, start mssql, wait 60 seconds, start antivirus
B. detach suspect db, delete ldf, attach mdf.
Has anyone else noticed an increase in suspect databases or a start in suspect databases where it did not happen before SP4 or maybe a MS security patch, (or even antivirus)?
[font="Arial"]Clifton G. Collins III[/font]
December 10, 2005 at 9:39 am
I haven't seen an increasing trend since SP4 was released and on't know of any bugs that would cause it.
What does it say in the errorlog before the database is thrown into suspect mode?
Why are you deleting the ldf?
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
December 10, 2005 at 8:32 pm
Have you checked your antivirus to make sure it's still excluding the data and log files? We had someone change that a month or so ago while they were "improving" the ePO policies. That caused all kinds of interesting things in the environment until we figured it out.
Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.
December 12, 2005 at 12:23 pm
Servers and workstations with different antivirus have experienced corruption. In all cases, the *.mdf, *.ndf and *.ldf files were supposed to be excluded. However, based on procedure A success, it does appear that the antivirus is configured incorrectly or ignoring. We do not use ePo, so there is no other 'cook' in the kitchen with antivirus.
There are no meaningful errors logged before the database is thrown into suspect mode. The only indication of an error is during the MSSQL startup while it is 'recovering' databases. I can force this error to be logged on attach:
Error: 9003, Severity: 20, State: 1
The LSN (137:46:1) passed to log scan in database 'censored' is invalid.
We resorted to deleting the ldf and loosing any transactions in order to recover the databases on development workstations. We did this because no other Microsoft supplied database repair tool would correct the mdf + ldf pair. It would be extremely useful to have a MS tool that would correct or assist in correcting corrupt databases.
[font="Arial"]Clifton G. Collins III[/font]
December 12, 2005 at 5:58 pm
Do you have any backups? You should be able to restore to the point the log is corrupted by the AV software.
Did you run CHECKDB after deleting the transaction logs?
Regards
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
January 25, 2006 at 2:19 pm
What antivirus have you guys used and had issues with?
January 25, 2006 at 2:39 pm
I have tested with Network Associates VirusScan 7 and 8 reproducing the error on workstations and servers. The error does not subside until I exclude the directories that contain data files as well as *.mdf, *.ndf, *.ldf files.
I have tested with the AVG free antivirus and have not seen/reproduced the error.
[font="Arial"]Clifton G. Collins III[/font]
January 26, 2006 at 8:45 am
We are using Trend Micro. Never had any issue with databases getting corrupted.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply