{"id":2976,"date":"2022-12-29T07:00:00","date_gmt":"2022-12-29T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=2976"},"modified":"2023-11-26T18:04:45","modified_gmt":"2023-11-26T15:04:45","slug":"pos-elegchoyme-tin-akeraiotita-ton-vas","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/pos-elegchoyme-tin-akeraiotita-ton-vas\/","title":{"rendered":"How we check the integrity of databases and how we fix any corruption in SQL Server"},"content":{"rendered":"

In a database there is always the possibility of having one corruption<\/strong>. This can be either physical <\/strong>e.g. from some disk damage or either logical <\/strong>e.g. wrong values in a table field. In the article we will see how we can detect it and how to fix it. <\/p>\n\n\n\n

When we go to access data that has corruption an error message will be returned. But if we don't call this data often, we will be slow to detect it. This will make it more difficult to fix.<\/p>\n\n\n\n

How do we find out if a database shows any corruption?<\/h5>\n\n\n\n

With the transact SQL command CHECKDB <\/strong>and setting the name of the base we check for any type of corruption.<\/p>\n\n\n\n

DBCC CHECKDB (test_db)<\/pre>\n\n\n\n
Once it is completed it will show us the result, or we can see at any time from the errorlog <\/strong>by running the following command:<\/p>\n\n\n\n
exec xp_readerrorlog 0,1,\"CHECKDB\",null,null,null,\"DESC\"<\/pre>\n\n\n\n
Its result will be something like this, we see that in this case it says that it found 0 errors.<\/p>\n\n\n\n
DBCC CHECKDB (test_db) executed by dbadmin found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds.  Internal database snapshot has split point LSN = 0000002d:000012be:0001 and first LSN = 0000002d:000012bc:0001.<\/code><\/pre>\n\n\n\n
We can give parameters to the CHECKDB command such as not to display informational messages, to lock the entire table to run faster without using tempdb, to use up to 2 cpu cores, etc. :<\/p>\n\n\n\n
DBCC CHECKDB (test_db) WITH NO_INFOMSGS,TABLOCK,MAXDOP=2;<\/pre>\n\n\n\n
How do you fix corruption<\/h2>\n\n\n\n
Depending on what type of corruption we have, we follow different steps.<\/p>\n\n\n\n
As we said at the beginning of the article, a physical or logical corruption, from then on, you can also divide into other categories such as:<\/p>\n\n\n\n