Having experienced problem in application I would like to know how to reset SQL Express 2005 when getting "NOLOCK" message error 601?

Question

Having experienced problem in application I would like to know how to reset SQL Express 2005 when getting "NOLOCK" message error 601?

philgendorf

SSC Journeyman

Points: 76
More actions
April 13, 2009 at 10:23 am

#199127

Error code shown:
Failed to retrieve data for this request. (Microsoft.SqlServer.Express.SmoEnum)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)
Could not continue scan with NOLOCK due to data movement. (Microsoft SQL Server, Error: 601)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.4035&EvtSrc=MSSQLServer&EvtID=601&LinkId=20476%5B/i%5D
Link doesn't help us, we have tried stopping and restarting SQL engine and rebooting laptop, to no avail. Now can't even establish ODBC link to any databases stored there. Any suggestions on how to get going again?
Thanks
Peter

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Paul Randal One Orange Chip Points: 29544 More actions · Answer 1

Do you get this error whenever you try to do anything with the database?

What's the output from running 'DBCC CHECKDB (yourdbname) WITH ALL_ERRORMSGS, NO_INFOMSGS' from a connection to the database?

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Jeffrey Williams SSC Guru Points: 90314 More actions · Answer 2

The error that you received is one of the known issues with using the NOLOCK hint on a query. The only real fix is to rewrite the query without using NOLOCK - or modify the isolation level.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

philgendorf SSC Journeyman Points: 76 More actions · Answer 3

Yes we get this when trying to do anything with any database on the SQl server. Unfortunately my colleague with this is in Peru so I am relaying your responses to her so I will ask her to run the 'DBCC CHECKDB" and reply with what she gets. Thanks Peter

Paul Randal One Orange Chip Points: 29544 More actions · Answer 4

But he also says he can't even connect to the database now - that's why I think there's some corruption in there too. That error can also crop up (when it's severity 21) when some kinds of corruption are there.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Paul Randal One Orange Chip Points: 29544 More actions · Answer 5

So my guess then is that there's system table corruption in master - possibly in a table holding login info. Make sure she run's DBCC on the system databases too.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

philgendorf SSC Journeyman Points: 76 More actions · Answer 6

Should she try running DBCC CHECHDB with the option to

REPAIR_ALLOW_DATA_LOSS

OR REPAIR_FAST

OR REPAIR_REBUILD

The databases aren't production, just training ones so any data loss is not significant and if the repair restores the login info she can get working again. If she uses the Repair_Rebuild option it should get her going again with minimum fuss and as the database is only small (X00MB) it shouldn't take too long to run and repair. Am I on the right path? Should the repair function solve the corruption issue? Since she is currently travelling back to Lima I will hear from her later today.

Thanks

Peter

Paul Randal One Orange Chip Points: 29544 More actions · Answer 7

No - never run repair without first getting the list of corruptions and deciding on a course of action to take.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Paul Randal One Orange Chip Points: 29544 More actions · Answer 8

Btw - REPAIR_FAST doesn't do anything in SQL 2005 onwards. I removed the code when I rewrote DBCC CHECKDB and left the option in for backwards compatibility reasons.

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

philgendorf SSC Journeyman Points: 76 More actions · Answer 9

OK Thanks. I have amended my email to colleague so she only runs the analysis routine and gets the results back to me.

Peter

philgendorf SSC Journeyman Points: 76 More actions · Answer 10

Hello Paul,

Its taken a while to get the info back from her, however it could not process the DBCC CHECKDB on any database (including master) and reported a problem with accessing the mssqlsystemresource database.

A quick search gave a couple of possibilities, including (inexplicably) that defragging the HDD can help. She set up a hard disk check, including looking for bad sectors, and then after rebooting and stopping SQL services ran a defrag. This did not help.

She was working at altitude (over 4000 metres) and its possible there was a corruption in the system DB. Where we are at now is she will return to Canada on the weekend and we will look at retesting her SQL or just reloading it (seems a bit extreme however will fix it) unless you have any suggestions.

Cheers

Peter

Paul Randal One Orange Chip Points: 29544 More actions · Answer 11

She should be able to reinstall the resource database from the install media - it's a non-changing database.

Thanks

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

philgendorf SSC Journeyman Points: 76 More actions · Answer 12

Hi Paul,

She was unsuccessful in reinstalling the resource database, tried to uninstall and then reinstall and has dug a deeper hole. The best action I see is to follow the instructions in http://msdn.microsoft.com/en-us/library/aa337087(sql.90).aspx to uninstall SQL Express and then start again. The instructions look good, is there anything else I need to be aware of before starting?

Thanks

Peter

Paul Randal One Orange Chip Points: 29544 More actions · Answer 13

From what I've seen, that should do it - hopefully!

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005