June 18, 2021 at 6:00 am
While executing DBCC CHECKDB i am getting following error
"Check terminated. A failure was detected while collecting facts. Possibly tempdb".
Checked for estimated tempdb following was the result.
Estimated TEMPDB space needed for CHECKALLOC (KB)
19762
Estimated TEMPDB space needed for CHECKTABLES (KB)
1
Auto growth is enabled for tempdb still error repeats.
what is the reason and how to solve this error?
June 18, 2021 at 4:47 pm
Quick google of that error, it sounds like there is more to the error message. Is the full error message:
Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
If so, I would check the logs and check previous error messages as there is likely something in there telling you exactly what is wrong such as:
The Database ID 85, Page (1:3403), slot 0 for LOB data type node does not exist. This is usually caused by transactions that can read uncommitted data on a data page. Run DBCC CHECKTABLE
NOTE - the above messages were taken from this link:
They have some solutions there such as making sure tempdb has enough space in both the data file and log file, running DBCC CHECKTABLE(<table name>), and using TABLOCK hints. But step 1 is to look at the logs to see what is ACTUALLY wrong and go from there.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 18, 2021 at 6:27 pm
I seem to recall that IT Researches uses SQL 2008 R2 Express. The space needed for tempdb is 20 GB which is above the database limit for Express. I am not sure that this limit applies to tempdb, though. But it could be a clue.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 19, 2021 at 9:39 am
All other errors were solved. Now when I execute DBCC Checkedb i will get only this error.
Msg 8921, Level 16, State 1, Line 1Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
June 19, 2021 at 9:00 pm
So how big is tempdb? What autogrowth settings does it have? How much free space is there on disk where tempdb is located?
Is this Express Edition or not?
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
June 22, 2021 at 7:48 am
My tempdb size = 200mb
Autogrowth set to "By 10 percent, unrestricted growth"
Will be using express edition.
June 22, 2021 at 2:21 pm
How much free disk space do you have? Can tempdb grow?
Also, is that 200MB the data size, the log size, or combined? Knowing the individual values would help.
Lastly, what are the messages in your log that are happening prior to the CHECK TERMINATED error? Reviewing your logs will give you a better understanding of what failed and why. In general, when I see a cryptic failure message like what you are seeing, IF the command isn't giving me enough information in the messages tab, I review the logs for previous errors, warnings, and information messages and then try to rule out if they are related. I expect that there is going to be something in the log telling you what is wrong. Even the message you get from DBCC is telling you to check the log ("Check previous errors").
If you are uncertain if the information is relevant to your error or not, I would say post some stuff here. Copy-paste the output from the DBCC command to a text file and upload it to the forum (removing any confidential data). Also, are you running DBCC CHECKDB with the options ALL_ERRORMSGS and NO_INFOMSGS? I almost always run it that way to strip out the fluff and make reviewing the results easier.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
June 22, 2021 at 6:05 pm
Since you are moving to SQL 2017 Standard, why not copy a backup and restore on SQL 2017 to run DBCC CHECKDB on this machine.
As I said before, I suspect that you are bitten by the 10 GB limit in Express. (DBCC indicates that it needs more tempdb than 10 GB.)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
October 20, 2021 at 10:22 am
When the following query was run ,
SELECT * FROM [msdb].[dbo].[suspect_pages]
we found that few pages in a table was corrupted.
If it is a data page we can get information about the corrupted rows in that page using DBCC page command as mentioned in
Now we have found that a text mixed page of type 3 is corrupted.
The corrupted page error can be cleared by rebuilding the indexes. In our table there are both clustered and non clustered indexes . Non clustered index rebuild was successful. but clustered index rebuild gave errors.
How to get the row details of a text mixed page?
or How to delete this corrupted page in SQL?
We know that table can be restored from the last saved backup or error can be cleared by using DBCC CHECKDB command.We want to know if any other solution is possible.
October 20, 2021 at 2:22 pm
There are other solutions for sure, but they include risk. For example, you can take the database offline and modify it in a hex editor to correct the bad data.
If you google database corruption recovery, you can find a lot of articles on how to recover.
Step 1 though is to take a new full backup as if you make things worse, you want to be able to restore to current state. I would also recommend doing all of the editing on a fresh restore copy of the database.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
October 20, 2021 at 9:16 pm
Edit the database file in a hex editor is certainly an option. ....but it is not for the faint of heart, but requires good knowledge of SQL Server internals. I have never done this myself, and I suspect that this is true for most people in this forum.
As Brian says, make sure that you take a backup of the database in its current state before you do anything. DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS should remove the bad page - but it may throw away the entire table.
Better may be to build a new database and copy data over, and try to get as much as data you can from the corrupted table. If you are lucky, an old backup of the database may have that page with more or less the correct data.
Keep in mind that corruption does not happen out of the blue. The reason is almost always faulty hardware, often the I/O subsystem.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply