Could not continue scan with NOLOCK due to data movement

[vuyiswamb]

Good Morning Friends,

I have a SP that is Supposed to Restore a Database from a Backup. In some Clients this works well but in those particular client its a Problem. The version is SQL 2005.

Here is the Code that fails

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go




-- Restore a backed up database
-- Given: the filename and path where the backedup database file is located and the name of the database to restore it as
-- The entry will be restored and a row placed into oDirect.dbo.tbl_dbref - which keeps track of the databases
-- The users for the database must also be restored!
ALTER PROCEDURE [dbo].[sp_RestoreDatabase] 

	@dbname char(32), 				-- the database name to restore as
	@filename char(64), @path char(256)		-- the location of the backuped up database file	(on the SQL Server)

AS

set nocount on

declare @sql nvarchar(3000)

execute('sp_ClearDatabaseConnections ' + @dbname)

-- Restore the database
select @sql = 			' RESTORE DATABASE ' + ltrim(rtrim( @dbname )) + ' FROM DISK = ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @filename )) + ''' ' 
select @sql = ltrim(rtrim(@sql)) + 	'    WITH RECOVERY, '
select @sql = ltrim(rtrim(@sql)) + 	'    MOVE ''' + 'TNGoedit_Data' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '.mdf' + ''' , '	-- logical file name to physical name
select @sql = ltrim(rtrim(@sql)) + 	'    MOVE ''' + 'TNGoedit_Log' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '_log.ldf' + ''' '	-- logical file name to physical name
--select @sql = ltrim(rtrim(@sql)) + 	'    MOVE ''' + ltrim(rtrim(@dbname)) + '_Data' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '.mdf' + ''' , '	-- logical file name to physical name
--select @sql = ltrim(rtrim(@sql)) + 	'    MOVE ''' + ltrim(rtrim(@dbname)) + '_Log' + ''' TO ''' + ltrim(rtrim(@path)) + ltrim(rtrim( @dbname )) + '_log.ldf' + ''' '	-- logical file name to physical name
print @sql
execute ( @sql )

-- Was the command successful or was there a problem
if ( (select @@Error) = 0 ) begin
	-- Put an entry into oDirect.dbo.tbl_dbRef
	-- execute ( 'sp_DataSet_Save ''' + @xml + ''' ' ) 	

	-- TODO: restore the users

  select 'Restore Successful' [Result]
end
else begin
   select 'Restore Unsuccessful' [Result]
end


/*	-- Example of a restore
RESTORE FILELISTONLY
   FROM DISK = 'C:\Inetpub\wwwroot\oBooking\Databases\oBookingMasterDB' 

RESTORE DATABASE tst
   FROM DISK = 'C:\Inetpub\wwwroot\oBooking\Databases\oBookingMasterDB' 
   WITH RECOVERY,
   MOVE 'LBS_Data' TO 'C:\Inetpub\wwwroot\oBooking\Databases\tst.mdf',	-- logical file name to physical name
   MOVE 'LBS_Log' TO 'C:\Inetpub\wwwroot\oBooking\Databases\tst_log.ldf'	-- logical file name to physical name
*/

And i get the Error

Exception caught in: ExecuteStoredProc: Could not continue scan with NOLOCK due to data movement. RESTORE could not start database 'Testv3'. RESTORE DATABASE is terminating abnormally. Database 'Testv3' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

Thank you.

[Geek-Master]

Looking at the error message about NOLOCK, I came across this article from Microsoft http://support.microsoft.com/kb/815008. But, this is for SQL Server 2000, which you said you are using 2005. Although, in the same article Microsoft said you can run this TRACE "DBCC TRACEON (9134, -1)" to ignore the row that has a lock on it. Not sure if this will help.

[vuyiswamb]

Thank you very much for your help.

The Problem is that the Database that i wanted to restore from was Corrupt.

Thank you