I bet you don’t know. The damage done by corruption can be significant enough to take your organization completely down. Repairing SQL Server corrupt databases isn’t always easy either. This should be reason enough to learn about the different types of corruption and decide which ones should be fixed immediately or scheduled for repair at a later time after other priorities are handled.
There are basically three types of database corruption: page splitting, index corruption, and allocation map (AM) fragmentation. Page splitting is quite common among SANs with automatic virtual disk extending capabilities enabled on their hosts because it’s triggered by the fact that the physical file size has grown beyond what was expected, but still remains smaller than what was allocated in Windows when adding additional disk space.
What causes page splitting?
Usually it’s due to the operating system adding the additional disk space but not allocating that space to SQL Server within the guest OS, which, in turn, will not return correct information on database size for full path names of database files which can result in corrupt data or index pages.
Splitting also occurs if Windows starts extending a file that was already extended by some other application (like VMware?). A new virtual machine with only one 2 GB disk created by VMware added 15 GB more to its first disk…then another 20 GB on top of it.
The last operation happened outside of VMware and since there were two applications trying to extend the same file at different points (VMware and Windows), an internal mechanism in Windows that’s responsible for extending files may have split a data or index page.
Index corruption can be triggered by a number of reasons, but the most common ones are:
1) A heap/clustered index being dropped using DROP INDEX while data is being inserted.
2) Adding, removing, modifying non-clustered indexes concurrently with large INSERTs.
3) Replication related operations (maybe?).
Allocation map fragmentation, unlike the first two types that may be fixed automatically (page splitting can sometimes cause lots of problems), needs to be taken care of manually because it requires rebuilding the allocation map during DBCC CHECKDB which is usually done on larger databases when SQL Server won’t start due to too many errors found.
An allocation map for a 10 GB database that’s fragmented into 1 KB blocks with one block being allocated 1 KB at a time and the rest of them is split into smaller units up to 8 KB. The row marked as “Other” resulted from splitting three blocks that were originally 32 KB each, and the row marked as “Unknown” is just beyond repair: there’re too many fragments and they’re too small (0-8 KB) to fix this corruption by using CHECKDB WITH REPAIR_ALLOW_DATA_LOSS.
How can you find out what type of corruption your databases have? First, check the SQL error log for corruption-related errors and look at the corrupted pages in DBCC PAGE. If this doesn’t help, use CHECKDB WITH REPAIR_ALLOW_DATA_LOSS to automatically fix page splitting and high index fragmentation, or just disable automatic checking with SET AUTOCHECKDATASOURCE OFF and run DBCC CHECKDB WITH PHYSICAL_ONLY.
Another example of allocation map fragmentation where all data pages are in one contiguous block with only allocation metadata not fitting together properly due to extended file size.
Fixing badly fragmented indexes is usually straightforward there’re no logical inconsistencies that prevent data retrieval throughout fragmented indexes. But if you find an allocation map that’s corrupted beyond repair – it may happen, but that’s one of the rarest cases! – Then you’ll have to rebuild all indexes on the database.
DBCC DBREINDEX with ALL_INDEXES option for rebuilding all indexes splitted into smaller fragments. Currently there’re no tools available outside of SQL Server for doing this automatically, so unless your database is quite small and index rebuilds are fast enough to not cause performance degradation, maybe try fixing fragmentation manually?
It shows how big each index page can get in our 10 GB database with maximum row size of 900 bytes (800 bytes plus 1 byte per row overhead). As you see, it’s quite easy to get a 20 GB single index page.
To avoid this from happening, consider using a columnstore index because they don’t support updates and deletes – not even for heap tables where data can be stored in a much more compressed format. Always use the same size pointer pages when adding an index to an existing table with CLUSTERED or NONCLUSTERED option (the example presented here doesn’t assume that).
After the initial nonclustered index creation, SQL Server will automatically split data pages so memory reallocation times will be minimal following subsequent DML operations on your database which thus provides best possible performance for transactional workloads.
Conclusion:
SERTs are the worst nightmare of every DBA, but you need to plan your actions carefully in order to successfully fix all allocation map corruption problems.
It’s quite easy to get a fragmented database by just executing a few turns of DBCC CHECKDB WITH NO_INFOMSGS or even turning on automatic checking with SET AUTOCHECKDATASOURCE ON. If this happens, the best solution is – exclude all databases from autochecking using “Data” registry key so SQL Server won’t complain about disabled autochecks when it runs DBCC CHECKDB next time.
