All Tasks->Restore Database....hangs forever

We have 3 SQL Server 2000 , SP3a installations. Log Shipping from primary to
two secondary servers. I performed a role change from the primary to the
secondary running script "sp_change_secondary_role" on secondary server.
Everything went fine. I later removed log shipping from all servers.
Now, I try to Restore a backup of the database on the ex-primary server by
right-click->All Tasks->Restore. It hangs, CPU usage 99%. I even deleted the
db, created an empty instance and tried to restore again. It hangs. I
restored using t-sql, but something is obviously wrong.
For what reason could sql exhibit this behavior?
Thank you for your help
JC
On Tue, 7 Jun 2005 06:47:04 -0700, John C <John
C@.discussions.microsoft.com> wrote:

>We have 3 SQL Server 2000 , SP3a installations. Log Shipping from primary to
>two secondary servers. I performed a role change from the primary to the
>secondary running script "sp_change_secondary_role" on secondary server.
>Everything went fine. I later removed log shipping from all servers.
>Now, I try to Restore a backup of the database on the ex-primary server by
>right-click->All Tasks->Restore. It hangs, CPU usage 99%. I even deleted the
>db, created an empty instance and tried to restore again. It hangs. I
>restored using t-sql, but something is obviously wrong.
>For what reason could sql exhibit this behavior?
>Thank you for your help
Sorry not a solution but just to confirm I have also seen this
behaviour of restore hanging enterprise manager (although these
servers were not log shipping) - using the SQL command equivalents in
Query Analyzer to restore provide a workaround.
|||John,
Do you ever cleanup the backup entries in tables in MSDB? Your request is
looking at all the entries in MSDB about this database. Probably should not
have the results that you see though.
Chris Wood
"John C" <John C@.discussions.microsoft.com> wrote in message
news:38E1E36C-CF4F-494E-978C-05DFC0E2D72C@.microsoft.com...
> We have 3 SQL Server 2000 , SP3a installations. Log Shipping from primary
> to
> two secondary servers. I performed a role change from the primary to the
> secondary running script "sp_change_secondary_role" on secondary server.
> Everything went fine. I later removed log shipping from all servers.
> Now, I try to Restore a backup of the database on the ex-primary server by
> right-click->All Tasks->Restore. It hangs, CPU usage 99%. I even deleted
> the
> db, created an empty instance and tried to restore again. It hangs. I
> restored using t-sql, but something is obviously wrong.
> For what reason could sql exhibit this behavior?
> Thank you for your help
> JC
|||Chris, this might be the problem. I just remembered that I had the same
problem when I tried to Delete the database by right-clicking. When I
selected "Delete backup history" the system hung. When I tried again and
unchecked this selection it did go hrough.
What tables do I have to clean though? Where in msdb is the backup history
stored?
Thank yu for your prompt reply
John C
"Chris Wood" wrote:

> John,
> Do you ever cleanup the backup entries in tables in MSDB? Your request is
> looking at all the entries in MSDB about this database. Probably should not
> have the results that you see though.
> Chris Wood
> "John C" <John C@.discussions.microsoft.com> wrote in message
> news:38E1E36C-CF4F-494E-978C-05DFC0E2D72C@.microsoft.com...
>
>
|||There are 4-6 tables that need to be cleaned up. Backupfile, backupmediaset,
backupmediafamily, backupset. Maybe a couple more, you will see when the
FKeys get in the way.
"John C" wrote:
[vbcol=seagreen]
> Chris, this might be the problem. I just remembered that I had the same
> problem when I tried to Delete the database by right-clicking. When I
> selected "Delete backup history" the system hung. When I tried again and
> unchecked this selection it did go hrough.
> What tables do I have to clean though? Where in msdb is the backup history
> stored?
> Thank yu for your prompt reply
> John C
> "Chris Wood" wrote: