Summary: The SQL database error 601 is a common error that can occur due to a locking problem or corruption in the database. In this post, we will discuss different ways to resolve this error. In addition, we will also mention a SQL database repair tool that can quickly and easily repair the corrupt database.
When you try to execute a query, you cannot read it because the data is locked with the NOLOCK hint. Also, you cannot access the data if it is being used by another transaction. In such situations, you receive the error 601. The complete error message is similar to the following:
Msg 601, Level 12, State 1, Line 1
Could not continue scan with NOLOCK due to data movement.
This error can be related to a locking problem or corruption in the database.
Let’s see how to resolve this error.
Solutions to fix SQL database error in SQL Server
Follow the below solutions to fix SQL database error 601.
Cancel the Query
You can cancel the query and submit it later or kill the process that has the query with the NOLOCK hint.
In SQL Server Management Studio, press the Cancel icon to cancel the query.
To detect which query is blocking, you can use the following query:
SELECT
r.session_id SessionID,
r.blocking_session_id BlockingSessionID,
r.start_time,
r.status,
r.command,
t.text queryText
FROM
sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE
r.blocking_session_id > 0;
This query will show the Session ID, Blocking Session ID, the time when the query started, the status of the request, the command, and the query. Most of the information comes from the system view sys.dm_exec_request, but the SQL text comes from the sys.dm_exec_sql_text.
To kill the session blocking, you can use the kill command.
KILL 34
The kill command kills the session ID.
Repair and Restore the Database
If the database is damaged, then you can also encounter SQL database error 601. To verify if the database is corrupt, you can use the DBCC command as given below.
DBCC CHECKDB (stellardb) WITH NO_INFOMSGS;The above command will check if the database stellardb is corrupt or not.
If there are errors or database is corrupt, then you can follow the different solutions:
- Restore the entire database from the backup.
- Restore the damaged page.
- Use the CHECKDB command to repair the database.
- Use a third-party SQL repair tool to repair the damaged database.
Restore the Entire Database from Backup
If your database is corrupt, the easiest option is to restore the database from backup.
To back up a database, use the following T-SQL sentences:
BACKUP DATABASE stellardb
TO DISK = 'f:\backups\stellardb.bak' WITH FORMAT;
The above command will back up the database named stellardb in the f:\backups drive in a file, named stellardb.bak. The WITH FORMAT option overwrites the backup if it already exists.
To restore the database, you can use the RESTORE command.
RESTORE DATABASE stellardb
FROM DISK = 'f:\backups\stellardb.bak' WITH REPLACE;
If you do not feel comfortable with the T-SQL codes, you can use the SQL Server Management Studio.
In the Object Explorer, right-click and select Tasks > Back Up.
You can select the path and backup type.
On the Media Options page, you can select the overwrite option.
To restore from the backup, right-click on the Databases node in the SSMS Object Explorer and select the Restore Database option.
Select the Device option, press the browse button, and select the backup device.
Restore the Damaged Page
If you do not want to restore the entire database, then you can restore the damaged page only. To do that, right-click the database and select Restore > Page.
Press the Add button and write the File ID and the Page ID. Then, press OK.
You can find the page ID when you run the DBCC CHECKDB command. It will show you the page or pages with errors.
In order to get the File ID, use the following query:
SELECT name as FileName, file_id AS FileID
FROM sys.database_files;
Use a Third-Party SQL Repair Tool
If the previous solutions did not work, you can use a third-party SQL repair tool, such as Stellar Repair for MS SQL. There are several editions of this software:
- Corporate edition – used to fix corrupted database files.
- Technician edition – can also reset passwords.
- Toolkit edition – can also restore backups, repair databases, reset passwords, analyze logs, and convert the database format.
In this example, we will be using Stellar Repair for MS SQL – Technician.
Open the Stellar Repair for MS SQL Technician software.
The software requires you to stop the SQL Server or set the database offline. Otherwise, the software will not be able to repair the database.
We strongly recommend to set the database offline.
You can press the Find button to find the database file to repair. Once found, make a copy of the file and select it. Then, press Repair.
Once repaired, press the Save icon. You can create a new database or replace the current one. You can also save the data to other formats, like Excel, CSV, and HTML.
Conclusion
In this blog post, we have discussed different ways to fix SQL database error 601 in MS SQL. This error can occur due to a simple locking issue. However, if the error occurs due to database corruption, then restoring from backup and restoring the damaged page can be useful options. Use the page restore option if only one page is damaged and restore the entire database if multiple pages are damaged. If nothing works, then use Stellar Repair for MS SQL which can repair the corrupt database and restore all the data.
