Summary: The SQL Server database Error 8967 is a common error in SQL Server on-premises when you restore your database, as and when the restored database gets corrupt. In this blog, we will show how to deal with this error. We will explain the error and show some possible solutions to this problem.
Getting started
Sometimes, it is not possible to restore the database because the restoring process has some errors. However, you can restore the database by ignoring the errors using the following sentences:
RESTORE DATABASE [db_name] FROM DISK = N’C:\Backup\db1.bak’
WITH CONTINUE_AFTER_ERROR;
The option continue_after_error allows restoring even if there are errors in the backup replicated in the database restored. In this scenario, if there were errors in the database restoration, it is a good practice to run a DBCC CHECKDB command.
The command will check if the database does not have errors. A common error in this scenario is the error 8967.
SQL server database error 8967
If the database was restored, but ignoring errors, your database might be corrupt and if you run the DBCC CHECKDB, you may find an error. The error 8967 is like the following:
|
Msg 8967, Level 16, State 216, Server SaleServer, Line 2 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors. |
|---|
DBCC is the Database Console Command. The DBCC oversees the following verifications:
- Maintenance used to maintain databases, data files, file groups, indexes.
- Informational tasks to send message about the status of the components
- Validation of the operations in the database, indexes, catalogues, file groups.
- Miscellaneous like trace flags
An error of level 16 in SQL Server means that the problem can be solved by the user. The error also mentions the tempdb system database. This system table is used to store temporary objects and internal user values. If it does not have enough space it can be a problem. Check the space for the tempdb then proceed further.
You can also check the SQL Server logs for additional information. Go to SQL Server Management Studio>Management>SQL Server Logs:
You may find the following message in the SQL Server Logs:
|
2017-07-26 08:13:49.21 spid58 DBCC encountered a page with an LSN greater than the current end of log LSN (142131:0:5) for its internal database snapshot. Could not read page (9647:-33648958), database ‘SalesDB’ (database ID 6), LSN = (-1302553601:2131886119:4432), type = 255, isInSparseFile = 1. Please re-run this DBCC command. |
|---|
The LSN is the Log Sequence Number. Every record in the log files has an LSN id. The error message will also show the pages with errors and identify the LSN with problems.
Possible solutions to fix error 8967
- DBCC CHECKDB with TABLOCK: One possible solution is to run the DBCC CHECKDB with the TABLOK option:
DBCC CHECKDB (SalesDB) WITH TABLOCK
The TABLOCK option will help you to LOCK the database and run the checkdb faster and avoiding the creation of internal database snapshots. The DBCC CHECKDB by default creates this database snapshot internally to makes some consistency checks.
- Switch to Simple Recovery Model: Other solution could be to switch to the Simple recovery model, checkpoint to truncate the log, and switch back to the Full recovery model. To modify the FULL recovery model to Simple, you can run the following T-SQL sentences:
ALTER DATABASE salesDB SET RECOVERY SIMPLE
GO
To return to FULL recovery model, run these T-SQL sentences:
ALTER DATABASE salesDB SET RECOVERY FULL
GO
Another way to change the recovery model is using the SSMS. Right-click the database and in properties go to the options page. Change the Recovery Model:
The recovery model is related to the type of transactions logged in the database logs. Simple will collect less information than a full mode.
- Use Stellar Repair for MS SQL: This SQL database repair software can easily fix the SQL server database error 8967.
How to use SQL repair software
- Download and install the software
- Select the MDF file and click Repair. If you want to recover the deleted records then, check the ‘Include Deleted Records‘ option.
- The software will repair the database and show the preview of repaired database objects
- You can save the database into New and Live database
This software with few clicks will repair the database and avoid the 8967 error message.
What Microsoft MVPs says about the software
“I would recommend this tool to repair corrupt SQL Server database. This saves lot of your time and effort and especially if you do not have expert knowledge about database and recovery. Considering how much downtime can cost such tools are great lifesavers.” (Shashank Singh, Microsoft MVP) Reference
“For my test database, the software worked flawlessly and I have to say that it was nice that I could browse the data inside the repair app as well.” (Gary Williams, IT and VMware Expert) Reference
Conclusion
In this article, we explained the SQL server database error 8967 that can be detected after restoring a database with errors. We also learned how to check the related messages in the SQL Error logs. Stellar Repair for MS SQL is a simple SQL recovery tool. It can be used to solve this problem.
If you have questions or comments, feel free to write to us.
