Database corruption can manifest itself in different ways. For example, you might encounter error messages when trying to save data, edit data, error messages running queries such as mismatched data between an index and the table. Corruption can occur at any time and can be caused by SQL Server itself, Windows, a sudden power outage, or issues with storage to name a few. That leads me to tell you that your Sage X3 database should frequently be checked for signs of corruption.
.
How do we check for database corruption?
Microsoft has a special command for that named DBCC Checkdb (Database Consistency Check). Please do not run off and execute it during business hours. It is a command that will use resources and could cause performance delays on your SQL Server. It is meant to be run off hours.
Microsoft has a lot more information about DBCC Checkdb but for my blog here I will provide some basic information. DBCC Checkdb executes 3 other procedures; DBCC CheckAlloc, DBCC CheckTable, and DBCC Checkcatalog. DBCC CheckAlloc checks the allocation of all pages in the database and internal structures used to track these pages and the relationship between the two. DBCC CheckTable checks the allocation of the page and structures that make up a table or indexed view. You could use this command singly if you are checking one table, if you want to check all tables, use DBCC Checkdb. DBCC Checkcatalog checks for catalog consistency within the database. Catalogs are tables that describe objects such as databases, tables views, etc...
How do we use it?
The basic syntax is DBCC Checkdb ('databasename'). With this syntax you will get informational data as well as any errors discovered. There are optional parameters you can include that will suppress informational messages and only include errors. That would look like this: DBCC Checkdb ('databasename') WITH NO_INFOMSGS, ALL_ERRORMSGS. I prefer running DBCC Checkdb to return all informational messages as well as errors messages first, then if there are any error messages include the other two parameters.
How do we know if there is corruption?
If you run DBCC Checkdb manually into a SQL Query window, check the Messages output tab. In the output tab, look for occurrences of the word "error". You will see a description of the type of problem following the word "error".
How do we fix the corruption?
If you run into error messages how should they be fixed? In some cases, depending on the error message, the output will tell you to run another DBCC command. Otherwise, you have a couple of options.
Microsoft suggests restoring your database from a backup, that's the first option. If you have not been regularly checking for database corruption its very likely you have backed up a database that contains the corruption as well. In that case, you may need to consider restoring an older backup versus the second option.
The second option is to run the DBCC Checkdb command and include a repair parameter. It is also quite possible that even running the DBCC Checkdb with a repair parameter, that it will not fix the problem or, it could fix the part of the problem and cause some data loss(as defined by the repair parameter REPAIR_ALLOW_DATA_LOSS) . There is also a REPAIR_REBUILD repair parameter that attempts to fix the corruption with no possibility of data loss, according to Microsoft documentation, but that does not mean it will repair everything completely.
In summary
To summarize, databases are at risk to becoming corrupted. You should be running DBCC Checkdb every day and checking for errors. If you go for months without checking your database, and then one day you run DBCC Checkdb and it returns errors. Then, you run DBCC Checkdb and try to repair the problems encountered but they cannot be fixed. What will you do then, restore from a backup from months ago? What if your backup also contains the corruption, then what? Your data is very important, and it needs to be checked for corruption on a regular basis. If data isn't being entered that often maybe you can get by running DBCC Checkdb weekly.
In cases when repair is not working, my suggestion is to open a case with Microsoft to have them look at it. I have worked with Microsoft over the years on a few cases, and they always seem to have tools I have never seen available anywhere else, that they use to run diagnostics and/or fix things. I have not worked with Microsoft on a data corruption issue before, but I have talked to a few consultants and they also recommend opening a case with Microsoft when data corruption cannot be fixed with the DBCC Checkdb or when restoring from backup is not a viable option. There are 3rd party companies that can help recover corrupt mdf and ldf files, however I have not had any personal experience with them.