Scan Lock on Suspect Database - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Scan Lock on Suspect Database

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-03-11 : 11:19:36

i keep getting an error when trying to move some data out of a suspect database i switched to recovery mode.

any ideas on how to get the data moved?


select * into test.dbo.mailable_new from response.dbo.mailable

Server: Msg 601, Level 12, State 3, Line 2
Could not continue scan with NOLOCK due to data movement.

Thanks!

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-11 : 11:24:17

Are you placing NOLOCK hint while moving data?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-11 : 11:27:41

No, he couldn't, could he?
He didn't post that code anyway.

Albert, create the table first with

select top 0 * into test.dbo.mailable_new from response.dbo.mailable

and then

insert test.dbo.mailable_new select * from response.dbo.mailable

Why? The "select into" approach places a lock on the table which maybe can't be done in suspect mode.

E 12°55'05.25"
N 56°04'39.16"

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-03-11 : 11:59:26

okay, so i tried:


set identity_insert response.dbo.mailable on response.dbo.mailable
insert test.dbo.mailable_new SELECT [RECTYPE], [address1],... FROM [Response].[dbo].[mailable]

(included all the fields except the UID (my identity field)

and got:

Server: Msg 601, Level 12, State 3, Line 3
Could not continue scan with NOLOCK due to data movement.

Same Error :(

also, i think it might be worth noteing that this is on a table that's about 150 Million records. on a few other tables i've moved, it's worked fine (in the 4-32million range)

any ideas, or do i just need to restore from a backup?

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2008-03-11 : 16:00:57

Albert,

Did you bother to read Peso's answer before posting again? It doesn't appear that you did, since you ignored his suggestion on how to resolve your problem.

Please take the time to read previous answers (and try any solutions presented) before posting yet another question. Also, when you *do* post a second time, please answer any questions asked of you in other replies.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2008-03-11 : 16:13:01

quote:
Originally posted by KenW

Albert,

Did you bother to read Peso's answer before posting again?

yes i did, however because the table has an identity column, i had to modify it a little inorder to allow sql to insert the identity from the old table. after figuring that part out, i posted my code, and got the same error as before.

and as for the other questions, your right, i should had addressed harsh_athalye.

harsh_athalye: no data is being moved. i suspect that because the database is in "Emergency Mode" SQL server is tripping an error for some reason. as i mentioned before, i can move smaller incraments of data, (smaller tables actually) it's just the large stuff i'm having a problem with.

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources