This is the Guest post by my friend and SQL Server DBA Daniel Jones
Elimination of SQL Server Database Suspect Error 926
Microsoft SQL server comprises of multiple tempting features within it. These characteristics make the server one of the most reliable database application in market. It is widely considered as a back-end for saving data of Windows application. However, while accessing SQL server users may experience variety of errors and thus leading to interruption in work. These errors may get resolved either from some server commands or from the server management studio. If required, then third party utility may also be needed for troubleshooting errors. In this blog, we are going to discuss about SQL server error 926 and measures to fix it.
When a user is operating SQL server for managing his database, he/she might face some issues while mounting the database. The database may be inaccessible and thus, you may not be able to mount it. Well, such type of scenario leads to occurrence of error code 926 in SQL Server.
Well, there are some other scenarios when such error occurs and they are highlighted below:
- When the server user is attaching database
- While restoring database in the SQL server
- Extracting transactions from database log
Why SQL Server Error 926 Occurs?
There is one or the another reason behind occurrence of each server error. Similar is the case with this 926 error. The most common reason behind this error is the corruption in MDF file of the server. Besides this, there are some other factors also that are responsible for such error in one or the other way:
- The database might be marked as suspect
- Corruption in header part of the system file
- Some malware defect in hardware of machine
- Due to sudden shut down of the server application
Tip: If this error is not resolved immediately then, this may result in loss of your important data. Therefore, it is strongly recommended to server users that instead of ignoring such error they should resolve them as soon as possible.
Solutions To Fix Microsoft SQL Server Error 926
A server user can himself rectify the error code 926 of the database with help of any one solution, which are discussed below:
Solution1: Fix Error 926 with Server Management Studio
Follow these steps for fixing error with inbuilt features/options of the management studio:
- Launch SQL Server Management Studio on your computer system
- Go to Object Explorer >> Server Instance and choose Stop option
- Now minimize the management studio and open Control Panel on computer
- In panel screen, change the view screen to Large icons and click on Administrative Tools for displaying further list
- From the list of tools, select Services option for proceeding further
- Search for SQL Server (MSSQLSERVER), right-click on it and choose Stop option from the menu
- Close the control panel window and launch My Computer or Computer on Windows. In computer, go to the following location:
- Select MSDBDATA and MSDBlog files and move them to some other place
Note: You are supposed to move these files not copy them to another location
- Go to location where you had moved the file in Step (8) and copy them back to order place i.e.,
- Restore the management studio window and go to Object Explorer section. Now right-click on Server Instance and choose Start option
- Click on Refresh button of the Management studio in order to update the changes
Tip: You can also press F5 to refresh the data of the database file
Solution 2: Fix SQL Server Error Code 926 By Executing Commands in SSMS
The procedure includes the execution of some SQL commands in its query window for fixing the problem.
- Launch SSMS application on your machine and click on New Query button of it
- Type and execute the following command where, sp_resetstatus will turn off the suspect flag on database. Moreover, this cmd will allow you to change the current mode and status of database in sys.database.
EXEC sp_resetstatus 'DB_name'
Run the following command on server for setting the database in Emergency mode, which will enable only sysadmin users to operate it
ALTER DATABASE DB_Name SET EMERGENCY
- Now it’s time to examine the integrity of all database objects with help of following command:
DBCC checkdb 'DB_Name'
- After completely analyzing the database integrity it’s time to set the server mode in single user. Hence, run the below-mentioned command to do so:
ALTER DATABASE DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
- Write the following command whose purpose is to repair the error and execute it. However, this command allow the server to remove the data that is required for aiding the file
DBCC checkdb ('DB_Name' ,REPAIR_ALLOW_DATA_LOSS)
- Finally the last command of the whole procedure whose aim is to set the database mode back to multiple users so that all other users can again access the database
ALTER DATABASE DB_Name SET MULTI_USER
All the solutions that are discussed above will most probably result in data loss from the server. These may fix SQL Server error 926 but will also result in some loss of essential data. Therefore, the best and appropriate way to resolve this error is third party tool like SysTools SQL Recovery. It guarantees that data will be extracted without any loss or change & thus you can remove SQL server 926 error without any compensation.