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?
 
Vitor MontalvãoConnect With a Mentor MSSQL 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
 
RiteshShahConnect With a Mentor Commented:
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
srinivas_ganamurAuthor Commented:
the default isolation level is read commited ...right
0
 
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
 
BodestoneConnect With a Mentor Commented:
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
 
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
All Courses

From novice to tech pro — start learning today.