Database goes into Supect or Loading state after Restore

This is the scenario:
I am do log shipping from one server to another. I have 9 databases
that I am shipping. 8 of them work flawlessly. I have one that for
some reason either always goes into a Suspect state or into a Loading
state. It just stays there until I do a manual restore. I have
created all of the store procedures exactly the same. The weird thing
is that I can do a manual restore of the exact same file that the
store procedure is trying to restore and it does it perfectly. The
only difference between this database and the others is the size. It
is a little over 6 gig versus 3 gig or less on the others.
I am stumped and not quite sure what to do. It is almost like the
stored procedure is either timing out or the job is advancing to the
next step before the restore is complete. Oh, one other thing, the
job is showing a status of completed successfully. The following is
the stored procedure I am using to restore the database:
CREATE PROCEDURE Restore_Database_W3Data_Backups AS
RESTORE DATABASE W3Data
FROM DISK = 'e:\sqlbackups\W3Data\W3Data.bak'
WITH
REPLACE,
STANDBY = 'e:\sqlbackups\W3Data\undo_W3Data.ldf',
MOVE 'w3data_dat' TO 'E:\SQL Data\MSSQL\Data\w3data_data.mdf',
MOVE 'w3data_log' TO 'E:\SQL Data\MSSQL\Data\w3data_log.ldf'
WAITFOR DELAY '00:00:05'
EXEC sp_dboption 'W3Data', 'single user', true
GO
Does Anyone have any ideas?
TIA!
Neil Mowatt
Network Administrator
The Nottingham Company
Hi
The restore fails if you run out of disk space on the Undo drive. How much
free space do you have on E:?
Regards
"Neil Mowatt" wrote:

> This is the scenario:
> I am do log shipping from one server to another. I have 9 databases
> that I am shipping. 8 of them work flawlessly. I have one that for
> some reason either always goes into a Suspect state or into a Loading
> state. It just stays there until I do a manual restore. I have
> created all of the store procedures exactly the same. The weird thing
> is that I can do a manual restore of the exact same file that the
> store procedure is trying to restore and it does it perfectly. The
> only difference between this database and the others is the size. It
> is a little over 6 gig versus 3 gig or less on the others.
> I am stumped and not quite sure what to do. It is almost like the
> stored procedure is either timing out or the job is advancing to the
> next step before the restore is complete. Oh, one other thing, the
> job is showing a status of completed successfully. The following is
> the stored procedure I am using to restore the database:
> CREATE PROCEDURE Restore_Database_W3Data_Backups AS
> RESTORE DATABASE W3Data
> FROM DISK = 'e:\sqlbackups\W3Data\W3Data.bak'
> WITH
> REPLACE,
> STANDBY = 'e:\sqlbackups\W3Data\undo_W3Data.ldf',
> MOVE 'w3data_dat' TO 'E:\SQL Data\MSSQL\Data\w3data_data.mdf',
> MOVE 'w3data_log' TO 'E:\SQL Data\MSSQL\Data\w3data_log.ldf'
> WAITFOR DELAY '00:00:05'
> EXEC sp_dboption 'W3Data', 'single user', true
> GO
>
> Does Anyone have any ideas?
> TIA!
> Neil Mowatt
> Network Administrator
> The Nottingham Company
>
|||I have over 140 Gig free. I don't think that is the problem. Thank
You though. Any other suggestions or ideas?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:<41C9445C-7C5E-4A5A-94BB-A698ED1BE18E@.microsoft.com>...[vbcol=seagreen]
> Hi
> The restore fails if you run out of disk space on the Undo drive. How much
> free space do you have on E:?
> Regards
> "Neil Mowatt" wrote:
|||neil@.ncfunds.com (Neil Mowatt) wrote in message news:<fbbc61a6.0409240644.a6ad096@.posting.google.c om>...[vbcol=seagreen]
> I have over 140 Gig free. I don't think that is the problem. Thank
> You though. Any other suggestions or ideas?
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:<41C9445C-7C5E-4A5A-94BB-A698ED1BE18E@.microsoft.com>...
Any other suggestions or ideas?