{"id":140437,"date":"2023-06-29T08:22:28","date_gmt":"2023-06-29T08:22:28","guid":{"rendered":"https:\/\/www.stellarinfo.com\/blog\/?p=140437"},"modified":"2023-06-29T08:23:35","modified_gmt":"2023-06-29T08:23:35","slug":"resolve-error-601-in-sql-server-database","status":"publish","type":"post","link":"https:\/\/www.stellarinfo.com\/blog\/resolve-error-601-in-sql-server-database\/","title":{"rendered":"How to Resolve Error 601 in SQL Server Database?"},"content":{"rendered":"

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:<\/p>

Msg 601, Level 12, State 1, Line 1<\/strong><\/p>

Could not continue scan with NOLOCK due to data movement.<\/strong><\/p>

This error can be related to a locking problem or corruption in the database.<\/p>

Let’s see how to resolve this error.<\/p>

Solutions to fix SQL database error in SQL Server<\/h2>

Follow the below solutions to fix SQL database error 601.<\/strong><\/p>

Cancel the Query<\/h2>

You can cancel the query and submit it later or kill the process that has the query with the NOLOCK hint.<\/p>

In SQL Server Management Studio, press the Cancel icon to cancel the query.<\/p>

\"Cancel<\/figure>

To detect which query is blocking, you can use the following query:<\/p>

SELECT<\/code><\/p>

  r.session_id SessionID,<\/code><\/p>

  r.blocking_session_id BlockingSessionID,<\/code><\/p>

  r.start_time,<\/code><\/p>

  r.status,<\/code><\/p>

  r.command,<\/code><\/p>

  t.text queryText<\/code><\/p>

FROM<\/code><\/p>

  sys.dm_exec_requests r<\/code><\/p>

  CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t<\/code><\/p>

WHERE<\/code><\/p>

  r.blocking_session_id > 0;<\/code><\/p>

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.<\/p>

To kill the session blocking, you can use the kill command.<\/strong><\/p>

  KILL 34<\/code><\/p>

The kill command kills the session ID.<\/code><\/p>

Repair and Restore the Database<\/h2>

If the database is damaged, then you can also encounter SQL database error 601<\/strong>. To verify if the database is corrupt, you can use the DBCC command<\/a> as given below.<\/p>

DBCC CHECKDB (stellardb) WITH NO_INFOMSGS;<\/code><\/pre>
The above command will check if the database stellardb is corrupt or not.<\/p>
If there are errors or database is corrupt, then you can follow the different solutions:<\/strong><\/p>
    \n
  1. Restore the entire database from the backup.<\/li>\n\n\n\n
  2. Restore the damaged page.<\/li>\n\n\n\n
  3. Use the CHECKDB command to repair the database.<\/li>\n\n\n\n
  4. Use a third-party SQL repair tool to repair the damaged database.<\/li>\n<\/ol>
    Restore the Entire Database from Backup<\/h2>
    If your database is corrupt, the easiest option is to restore the database from backup<\/a>.<\/p>
    To back up a database, use the following T-SQL sentences:<\/p>
    BACKUP DATABASE stellardb<\/code><\/p>
    TO DISK = 'f:\\backups\\stellardb.bak' WITH FORMAT;<\/code><\/p>
    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.<\/p>
    To restore the database, you can use the RESTORE command.<\/p>
    RESTORE DATABASE stellardb<\/code><\/p>
    FROM DISK = 'f:\\backups\\stellardb.bak' WITH REPLACE;<\/code><\/p>
    If you do not feel comfortable with the T-SQL codes, you can use the SQL Server Management Studio.<\/p>
    \"Click<\/figure>
    In the Object Explorer, <\/strong>right-click and select Tasks > Back Up.<\/strong><\/p>
    You can select the path and backup type.<\/p>
    \"Path<\/figure>
    On the Media Options <\/strong>page, you can select the overwrite option.<\/p>
    \"Backup<\/figure>
    To restore from the backup, right-click on the Databases node in the SSMS Object Explorer <\/strong>and select the Restore Database <\/strong>option.<\/strong><\/p>
    \"Click<\/figure>
    Select the Device <\/strong>option, press the browse button, and select the backup device.<\/p>
    \"Selecting<\/figure>
    Restore the Damaged Page<\/h2>
    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.<\/strong><\/p>
    \"Restoring<\/figure>
    Press the Add button and write the File ID and the Page ID. Then, press OK.<\/p>
    \"Adding<\/figure>
    You can find the page ID when you run the DBCC CHECKDB command. It will show you the page or pages with errors.<\/p>
    In order to get the File ID, use the following query:<\/p>
    SELECT name as FileName, file_id AS FileID<\/p>
    FROM sys.database_files;<\/p>
    Use a Third-Party SQL Repair Tool<\/h2>
    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:<\/p>