SQL Server Performance Forum – Threads Archive

Suspect Databases

I work with over a dozen high volume OLTP SQL 2000 Databases on Windows 2K Advanced Server with very solid hardware (details will be included if requested). Every database has the same design but different user data. Each of these database servers communicate with its own logic tier (cluster of machines with similar hardware). Some of these databases have been marked Suspect by SQL Server (recovered, of course) while others never have had problems. One database has been marked Suspect half a dozen times in the last 6 months. We have done all sorts of hardware tests and came up with nothing. We have contemplated software troubles, but have not been able to pinpoint the exact cause. Whenever a database enters suspect mode, the SQL Server Log and Application Event Log would begin with 1. Could not find the index entry for RID xxx in index page (x<img src=’/community/emoticons/emotion-12.gif’ alt=’:x’ />xxx)…; then 2. Error while undoing logged operation in…. (several tries); and 3. The log for database xxx is not available..; followed by 4. Marks database suspect. This is followed by the automatic recovery process, but that also fails. We demand high concurrency in the system thus designers and developers have adopted transaction level read uncommit or operation level nolock hints. The dirty reads may attribute to the missing index (or is the index just not updated often enough??)… It occurs to me that the actual cause is the "error while undoing logged operation…". Does anyone have any insights?

one possible path to follow is your log drives, we had a couple of problems where the log drive seemed to go off line ( a scssi issue ) this will cause the database to be marked suspect.
Other than that I’d be running the dbcc commands to re-assure myself!
You should try restoring your latest backups to another server to make sure your backups are good.

Is there any autoshrink on in database properties or some job doin shrink in production time?
Luis Martin
Moderator
SQL-Server-Performance.com

Autoshrink is on for all these databases. There are no reported problems with DBCC checkdb and checktable commands. There are no event logs of detected problems on the log disk array…

Anyway I suggest to autoshink off and do it in a job in available windows. I had serius problems with that. Luis Martin
Moderator
SQL-Server-Performance.com

Thanks, I am going to give that a try. What kind of problems would autoshrink cause?

From time to time I have checkalloc problems.
Other problem is all maitenance plan (update statistisc, defrag) is not usefull after a shrink.
That’s why the recommedations are: in the following order: backup, shrink, and then all manteinance plan.
Luis Martin
Moderator
SQL-Server-Performance.com

There is some overhead associated with the automatic growing and shrinking of files. Therefore, I#%92d recommend that you take a good guess at the size and usage of your database before making the settings. For example, if you are using an OLTP application that will begin with 100MB of data and will grow approximately 10MB per week, be sure to take that into account when you make the settings. I do HIGHLY recommend that you leave the autogrow and autoshrink options enabled. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>