Can index fragmentation cause the database to enter suspect mode?

Question

Can index fragmentation cause the database to enter suspect mode?

IT researcher

SSCertifiable

Points: 7582
More actions
May 17, 2024 at 11:43 am

#4421928

My database was in suspect mode, and when I checked using DBCC CHECKDB, it returned errors.
As mentioned in the link below, I used the REPAIR_ALLOW_DATA_LOSS option with the DBCC CHECKDB command, but few errors persisted.
https://www.stellarinfo.com/support/kb/index.php/article/procedure-to-recover-sql-database-from-suspect-mode
Later, I dropped and recreated the indexes. This resolved the issue.
My question is whether index fragmentation could have caused the database to go into suspect mode (Drive health check showed no issues).

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

andreas.kreuzberg SSCertifiable Points: 6191 More actions · Answer 1

Hm,

have you checked your errorlog, when your database changed to suspect mode. Was this the time, when the index maintenance run?

Or where some other events on the time, when the database changed to suspect mode? What about job scheduler on this time?

I can't believe this would happen without any impact from outside.

Best regards,

Andreas

Jeff Moden SSC Guru Points: 1004052 More actions · Answer 2

IT researcher wrote:

Later, I dropped and recreated the indexes. This resolved the issue.
My question is whether index fragmentation could have caused the database to go into suspect mode (Drive health check showed no issues).

Considering the fact that Online Rebuilds of Clustered Indexes in the Enterprise Version of SQL Server 2012 actually caused index corruption, NOTHING would surprise me here, even though they supposedly had the fix incorporated for good in SP3.

Are you using any form of "Resumable" or "Online" Index Creations or Rebuilds? Anything being done with "Wait at low priority"?

I know of no problems with those things but those would be where I'd start looking just based on the history of SQL Server 2012.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Perry Whittle SSC Guru Points: 234011 More actions · Answer 3

IT researcher wrote:

My database was in suspect mode, and when I checked using DBCC CHECKDB, it returned errors.
As mentioned in the link below, I used the REPAIR_ALLOW_DATA_LOSS option with the DBCC CHECKDB command, but few errors persisted.
https://www.stellarinfo.com/support/kb/index.php/article/procedure-to-recover-sql-database-from-suspect-mode
Later, I dropped and recreated the indexes. This resolved the issue.
My question is whether index fragmentation could have caused the database to go into suspect mode (Drive health check showed no issues).

That link contains some very bad advice, ideal situation would be restore from backup, repair allow data loss absolute last ditch. DBCC CHECKDB would produce an output detailing which object_id(s) and index_id(s) are affected, did you look at these at all.

I would be more concerned about the data that has just been lost by pages being relinked and the corrupt pages now removed.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

IT researcher SSCertifiable Points: 7582 More actions · Answer 4

Error: 9001<c/> Severity: 21<c/> State: 5.
Error during rollback. shutting down database (location: 1).
Database was shutdown due to error 3314 in routine 'XdesRMReadWrite::RollbackToLsn'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.
During undoing of a logged operation in database <c/> an error occurred at log record ID (203210:9224:339). Typically<c/> the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup<c/> or repair the database.

I have noted these errors shown above in the Error Log.

Johan Bijnens SSC Guru Points: 135290 More actions · Answer 5

After errorlog investigation:

first thing to do is to take another LOG backup ( if db recovery model <> simple )

Then restore from full backup and add the needed DIFF and LOG backups.

Repair allow data loss: Which data did you lose ?

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Perry Whittle SSC Guru Points: 234011 More actions · Answer 6

Original DBCC CHECKDB output would be helpful, with no outputs available would be safe to say data loss is unknown

if you knew what table(s) or index(es) were affected could restore backup and run row comparison between objects to ascertain what’s been removed

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉