Data Export error

I am exporting data(using GUI) from suspect database (emergency mode) to new database (blank database no tables, pro,views). At the time of export i got the below error. So i am not able to export data.

Error:
Could not continue scan with NOLOCK due to data movement.

Please let me know to how to fix above issue.
Export-error.JPG
srinivas_ganamurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RiteshShahCommented:
please have a look at below thread

http://support.microsoft.com/kb/815008
0
srinivas_ganamurAuthor Commented:
Hi ritesh,

your saying i have set as a read commited in destination database? coz source db is in suspect mode (emergency).
0
srinivas_ganamurAuthor Commented:
the default isolation level is read commited ...right
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

RiteshShahCommented:
yes
0
srinivas_ganamurAuthor Commented:
Hi Ritesh,

after setting isolation level i am getting same error:

Please find the attached image

i have done isolation setting in both database (suspect database & blank datbase).

I need to export all data from suspect DB to Blank DB.
error.bmp
0
srinivas_ganamurAuthor Commented:
i have used below query:

USE axlive_copy
go
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
0
srinivas_ganamurAuthor Commented:
Any suggestions from Experts for my issue.
0
RiteshShahCommented:
actually I am bit stumped in this case. let us wait for any other expert who can show you the ray of hope
0
PCIIainCommented:
The indexes for that table may be busted. (It's an outside possibility, but a possibility.)

Try

ALTER INDEX ALL
    ON axlive.dbo.address
    REBUILD
0
BodestoneCommented:
Have you tried detaching the source DB, making a copy of the data and log files under a different name, re-attaching them as a different database and using that as the source?
0
srinivas_ganamurAuthor Commented:
Hi pcllain,

the output of your query:

Msg 3908, Level 16, State 1, Line 1
Could not run BEGIN TRANSACTION in database 'AXLIVE' because the database is in bypass recovery mode.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why u just don't recover from suspect mode?

EXEC sp_resetstatus 'AXLIVE'
ALTER DATABASE AXLIVE SET EMERGENCY
DBCC checkdb('AXLIVE')
ALTER DATABASE AXLIVE SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('AXLIVE', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE AXLIVE SET MULTI_USER

Good luck
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
srinivas_ganamurAuthor Commented:
Hi VMontalvao,

I have tried your option also, check the below link (it is my post in EE)

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_25732666.html

0
PCIIainCommented:
Hmmm, I don't think the data can have moved as per the previous error, (because you are in bypass mode), so I am assuming that there's an incorrect index on that file which the copy routine is using, which then gives it a bad leaf value and therefore assumes the data has changed.

It's a big assumption.

You COULD try dropping the indexes (if it will let you) and turning the data into a heap, before copying. (You'll have to re-create the indexes in the new database, so get a script for creating them before dropping them.)

I'm not confident that it will let you do that either though.
0
srinivas_ganamurAuthor Commented:
Hi PCllain,

i am not able to script the database(suspect DB), please find the attached error image (at the time of script dataabse)
script-database.bmp
0
srinivas_ganamurAuthor Commented:
Hi Bodestone,

i have tried your option

use axlive
go
DBCC CheckDB
GO

Output:

Msg 7985, Level 16, State 2, Line 2
System table pre-checks: Object ID 4. Could not read and latch page (1:4206648) with latch type SH. Check statement terminated due to unrepairable error.
DBCC results for 'AXLIVE'.
Msg 5256, Level 16, State 1, Line 2
Table error: alloc unit ID 0, page (1:4206648) contains an incorrect page ID in its page header. The PageId in the page header = (0:0).
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'AXLIVE'.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
srinivas, keep in mind that you can't do nothing with a suspect database. Focus only in trying to recover it.

Good luck
0
srinivas_ganamurAuthor Commented:
Hi VMontalvao,

yes your correct, i am tryieng to export data (last option, using export wizard) to new DB. in that i am getting NOLOCk error)...i don't knw how to resolve this error.
0
BodestoneCommented:
Are you able to generate database scripts for the suspect database?
Right click, tasks, Generate Scripts.
If so you could use that to generate the full database DDL to re-create all the objects in the destination database including indexes.

You could then just try transferring data rather than all objects.

If that fails this would allow you to drop the indexes as suggested by PCIIain knowing that you had a copy of the DDL allowing recreation at any point.
0
srinivas_ganamurAuthor Commented:
--
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.