4

I have SQL Server 2008 R2 installed on a Windows 7 laptop. I create a new database via SQL Management Studio and when I right click and select Properties, I get the following message.

Cannot show requested dialog. 

Additional information:

|-> Cannot show requested dialog (SqlMgmt) 
    |-> An exception occurred while executing a Transact-SQL statement or batch 
        |-> Could not continue scan with NOLOCK due to data movement. (Microsoft SQL Server, Error: 601)

Any idea what is happening and what is the possible fix?

UPDATE

I am not entirely sure if this is a fix:

  1. I deleted all of the user databases
  2. Did a Repair on the SQL installation
  3. Created my databases again and it seemed to work without any issues.

However, I still do not what the root cause of the issue is. I had to fix it as soon as possible as the Sales Rep needed the laptop as quickly as possible. I really would like to hear what caused it to happen in the first place.

5
  • 4
    Is this something that happens all the time when you access that Properties dialogue or is it just a one off? Have you specifically set your object explorer connection up to use read uncommitted isolation level? When I right click a DB and select Properties and then look at the queries in profiler I don't see any nolock in the query text. Dec 4, 2012 at 17:18
  • @MartinSmith: I remember something about a bug/request in Connect that the Object Explorer couldn't be set to NOLOCK/Read Uncommitted. Are you sure you can change the properties of Object Explorer session?
    – Marian
    Dec 5, 2012 at 8:41
  • @Marian - No not really. There is an option "Tools -> Options -> Query Execution -> SQL Server -> Advanced => Set transaction isolation level"I haven't tested if Object Explorer uses the values set there. Dec 5, 2012 at 8:45
  • 3
    it would be interesting for you to start a trace and then attempt to replicate your issue. if it occurs again the trace would be helpful for both your problem and supplementing a connect bug.
    – swasheck
    Dec 5, 2012 at 15:55
  • @MartinSmith It is happening every single time I access the Properties dialog. It even happens if I try to access the Properties dialog of the System databases such as master, msdb or tempdb.
    – Kanini
    Dec 6, 2012 at 14:39

2 Answers 2

2

There's at least three possibilities:

  1. SQL Server Management Studio was running a query that it shouldn't have
  2. Something else was running a query it shouldn't have (like an SSMS add-in, which isn't likely based on the sounds of the question)
  3. The engine was experiencing difficulties executing a normal SSMS query (that it shouldn't have)

I'd troubleshoot #1 by running SSMS from another machine, pointed at this particular laptop. If it works okay, then the laptop's SSMS is the problem - reinstall that.

If the other machine's SSMS gets the same error when connecting to the laptop's engine, then uninstall and reinstall the SQL Server engine on the affected laptop. (It's some type of corruption issue, and not worth the $500 MS support call to troubleshoot - if it was a production server, I'd answer otherwise.)

1

The WITH(NOLOCK) hint (or the equivalent READ UNCOMMITTED transaction isolation level) causes SQL Server to circumvent transactions and to read tables even when being changed by another statement, and to not block them during read process.

SQL Server Management Studio also sends SQL statements to the database, likely in the NOLOCK / READ UNCOMMITTED mode to not block tables. If a change to the table occurs while being read in this mode, the SQL command may run into this error.

Probably something has been changing the tables SSMS was about to read.

SQL Server 2005 and later can be enabled for Snapshot mode (also referred to as Oracle style), where users read only rows already existing when their transaction/statement was started (row versioning), rather than locking against other reads/writes.

The transactional locking, enabled by default in SQL Server, makes especially long running queries critical and adds risk of severe delays or deadlocks. For this reason, the NOLOCK hint is common for complex or long running queries on SQL Server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.