April 13, 2009 at 10:23 am
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
April 13, 2009 at 10:32 am
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
April 13, 2009 at 10:48 am
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
April 13, 2009 at 10:58 am
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
April 13, 2009 at 10:58 am
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
April 13, 2009 at 11:02 am
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
April 13, 2009 at 11:37 am
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
April 13, 2009 at 11:48 am
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
April 13, 2009 at 11:49 am
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
April 13, 2009 at 12:06 pm
OK Thanks. I have amended my email to colleague so she only runs the analysis routine and gets the results back to me.
Peter
April 15, 2009 at 7:12 pm
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
April 16, 2009 at 12:17 am
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
April 22, 2009 at 2:30 pm
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
April 23, 2009 at 8:54 am
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
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply