restore db stuck loading

Hi

I am trying to copy a 20 GB database from server 1 to 2. I took a full
backup, copied to server2 and restored ( ready for use) using EM.

After the message restore completed, I see the database's status as LOADING.
We are on Sql Server 2000 (8.00.818) on Windows 2000 Adv Servers.

I ran dbcc on the source DB and it returned no errors. I tried the command
'restore db with recovery ' which didnot help. I get an message saying that
the data file is only partially restored.. It seems the last resort is to
change the status in system tables.

Any ideas?

Thanks
Ragu

[Erland Sommarskog]

(ra*****@sbcglobal.net) writes:

I am trying to copy a 20 GB database from server 1 to 2. I took a full
backup, copied to server2 and restored ( ready for use) using EM.

After the message restore completed, I see the database's status as
LOADING. We are on Sql Server 2000 (8.00.818) on Windows 2000 Adv
Servers.

I ran dbcc on the source DB and it returned no errors. I tried the
command 'restore db with recovery ' which didnot help. I get an message
saying that the data file is only partially restored.. It seems the last
resort is to change the status in system tables.

What does sp_helpdb say about the database?

If you run the RESTORE command from Query Analyzer, do you get any
interesting messages?

I think that what may have happened is that the database was restored
in such a way, that SQL Server now anticiapates one or more transaction
logs to be applied.

An example command to restore a database from QA:

RESTORE DATABASE db FROM DISK = 'C:\temp\whatever.dmp' WITH
MOVE 'datafile' TO 'C:\datadir\db.mdf',
MOVE 'logilfe' TO 'D:\logdir\db.ldf',
REPLACE, STATS
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ragu]

Hi Thanks for the reply. sp_helpdb returns 0 rows. The same sql restore
using QA returns this error

"ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message 258)
General network error. Check your network documentation. [SQLSTATE
08S01] (Error 11) 10 percent restored. [SQLSTATE 01000] (Error 3211).
The step failed. "

But it restored fine once but when I tried again I got the above error.
The reasons could be 1. some timing limit ( Since I restore on the
server this should not be an issue.) 2. I see antivirus software trying
to scan the newly created data file.

Thanks for your time

Ragu

[Erland Sommarskog]

Ragu (ra*****@gmail.com) writes:

Hi Thanks for the reply. sp_helpdb returns 0 rows.
Then the database is not there.
The same sql restore using QA returns this error

"ConnectionRead (WrapperRead()). [SQLSTATE 01000] (Message 258)
General network error. Check your network documentation. [SQLSTATE
08S01] (Error 11) 10 percent restored. [SQLSTATE 01000] (Error 3211).
The step failed. "
QA does not say "The step failed", does it? But, OK, I assume that it does
say "General network error" and all that jazz. That indicates that a tragic
accident occurred on the SQL Server side, and SQL Server had to terminate
the process. If you look in SQL Server's error log you are likely to find
a stack dump related to the accident. No, don't expect this stack dump
to make you that much wiser. But look there anyway.
But it restored fine once but when I tried again I got the above error.
The reasons could be 1. some timing limit ( Since I restore on the
server this should not be an issue.) 2. I see antivirus software trying
to scan the newly created data file.

I would certainly recommend preventing the antivirus stuff from sneaking
in.

What is funny is that you say that the backup loaded OK once. Had it never
loaded well I would suspect an accident in transport over the network. But,
hm, if I re-read your original message, not even then did the load complete
entirely successfully?

I would turn off the antivirus thing and try again. If it fails again,
I would try copying again. If I could find away to avoid the network,
I would try that. 20 GB is too much for a DVD, but a removable disk
that you connect over USB2 could be an alternative.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ragu]

HI Erland :

1. I will try to turn off the virus and try again.
2. I ran the query in QA on the server console using VPN and Remote
Connection and one thing or other always gets disconnected. So I put
the restore sql in a job and executed the job instead. Hence the words
"step failed"
3. But the error is the same in QA also.
4. Sometimes even thoug we get the same error in QA, DB after 3 hrs and
several "Bypassing recovery for database 'DB' because it is marked IN
LOAD." and one " Recovery is checkpointing database " messages, the DB
gets restored fine. The only message in the error log is Database "DB'
restore completed.
5. And in other environements with fatser servers ,we donot get this
error at all with the same backup file.

Thanks

Ragu

[Erland Sommarskog]

Ragu (ra*****@gmail.com) writes:

1. I will try to turn off the virus and try again.
If you have a virus on the machine, you should definitely turn if off! :-)
2. I ran the query in QA on the server console using VPN and Remote
Connection and one thing or other always gets disconnected. So I put
the restore sql in a job and executed the job instead. Hence the words
"step failed"
I see.

If you run QA on the same machine as the server, and you get disconnected
that is likely to be a crash and not a network issue.
4. Sometimes even thoug we get the same error in QA, DB after 3 hrs and
several "Bypassing recovery for database 'DB' because it is marked IN
LOAD." and one " Recovery is checkpointing database " messages, the DB
gets restored fine. The only message in the error log is Database "DB'
restore completed.

Three hours to restore a 20 GB database? That appears to be a tad long
to me.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ragu]

1. I meant virus scan not virus per se.
2. Normally it takes 15 -20 minues on production servers.
3. Reg getting disconnedt while on the server, there was no crash. I
think that the error message is misleading.
Thanks for your responses.

ragu

[Ragu]

Another update. In the follwoing scenerio the restore was succesful.

1. created the db say DB1 on the server.

2, Using QA, run the sql

RESTORE DATABASE DB1 FROM DISK = 'c:\db_backup\DB.bak' with
move 'DB_Data' to 'c:\SQL_DATA\DB_RC4_Data.MDF',
move 'DB_Log' to 'c:\SQL_DATA\DB_RC4_Log.LDF',
REPLACE, STATS

3. After 15 min , got the error [Microsoft][ODBC SQL Server
Driver][Named Pipes]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
10 percent restored.
Connection Broken

4. The db looks fine. The tables,row count match. The db is healthy.

5. The error logs show s the same messages like starting up
datasbase,Bypassing recovery for database Db1because it is marked IN
LOAD ( 2 times ), Recovery is checkpointing database ,Database
restored: over a period of 37 minutes. ( We got error in QA after 15
minutes )

Ragu
Though the QA gave the error, the db restored fine.

[Erland Sommarskog]

Ragu (ra*****@gmail.com) writes:

1. I meant virus scan not virus per se.

I understand that. But it looked funny! :-)

I will have to look into your other post tonight. It looks very strange
to me.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Ragu (ra*****@gmail.com) writes:

3. After 15 min , got the error [Microsoft][ODBC SQL Server
Driver][Named Pipes]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
10 percent restored.
Connection Broken

One thing is funny here. Did you really run the BACKUP command on a QA
which is on the same box as SQL Server? In such case I would not
expect Named Pipes in the error message.

The conclusion I am prepared to make, is that the network is flaky,
but SQL Server does not notice that client is gone and jogs along
with the restore. The recovery messages you get are funny, though.

If you really run QA on the server box, open Client Network Utility,
and check that you have share memory enabled (this is a check box
in the lower left corner).


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ragu]

HI I think you nailed the issue. Let us say if the database
Instance2\Database2 is running on DB server D02, and the network client
utility, there was entry for the Instance2\DB2 using namedpipes instead
of TCP. The share memory is enabled.

So The QA even though running on the same server connects as a client
using named pipes and that may be the issue.

I have removed the entry now and let me test it

Ragu

[Erland Sommarskog]

Ragu (ra*****@gmail.com) writes:

HI I think you nailed the issue. Let us say if the database
Instance2\Database2 is running on DB server D02, and the network client
utility, there was entry for the Instance2\DB2 using namedpipes instead
of TCP. The share memory is enabled.

So why does it not use shared memory? I have seen issues where shared
memory goes sour, but I seem to recall that in these cases you cannot
connect, or connection takes long time. If possible, may you should
retry rebooting the machine. (Not just only SQL Server. That's actually
how you can get shared memory to misbehave. Connect with QA on the local
server, restart the server without disconnecting the QA window.)


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Ragu]

No errors after this. tested few times and db got restored and no
errors were reported on QA. Thx for the help

ragu

[Erland Sommarskog]

Ragu (ra*****@gmail.com) writes:

No errors after this. tested few times and db got restored and no
errors were reported on QA. Thx for the help

Great to hear that it worked out!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp