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:

I deleted all of the user databases

Did a Repair on the SQL installation

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.

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 SmithDec 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?
–
MarianDec 5 '12 at 8:41

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.
–
swasheckDec 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 WhiteDec 5 '12 at 16:25

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

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.