Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
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. –  Martin Smith Dec 4 '12 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 '12 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. –  Martin Smith Dec 5 '12 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 '12 at 15:55
4  
I would run a full DBCC CHECKDB on all databases. There is a reasonable chance that there is a corruption in the system tables, either in the user database, master, or resourcedb. System tables are scanned at READ UNCOMMITTED and a corruption there could trigger this error. The suggestion to run a trace on SQL statements and errors is also a good one. –  Paul White Dec 5 '12 at 16:25
show 1 more comment

2 Answers

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.)

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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