Troubleshooting tips for cross-platform load?

[JMCraig]

Hi Folks,

After successfully doing a cross-platform load in test mode, it failed when we wanted to do it and go live on the new Linux box (moving from a Sun Solaris install).

Everything works right up until the end as ONLINE DATABASE processes the transaction log. Then it fails (apparently without any good reason):

...
Cross-platform conversion for database client_data: 15805282 pages completed.
Completed cross-platform conversion for user objects.
Started cross-platform conversion for log records.
Adaptive Server cannot load this database because the database that was dumped was not quiescent when
the dump was performed. Run sp_flushstats before DUMP DATABASE and ensure that the database is not updated during the dump.

The trouble is, I did do the sp_flushstats and there were no updates after that. The script then had a built-in waitfor delay of 2 minutes and then the checkpoint.

Now, I actually initiated the DUMP DATABASE through another session. But the DB was in single-user mode and there was no one using it. Could the use of another session be the problem? (I switched the session that had done the sp_flushstats back to the master DB via USE master just seconds before beginning the DUMP DATABASE.)

Should I have waited longer to do the checkpoint (it's a big DB: 66GB with about half the pages actually used)?

Is there any way to know if the backup produced by DUMP DATABASE got the elusive "quiescent" rating without doing the LOAD and ONLINE DATABASE commands and waiting for them to finish? (It'd sure be nice if there were a mode you put the DB into that would hold it in a quiescent state rather than a bunch of specialized steps you have to do.)

Because of the size of the DB and the fact that it's in production and works fine and presumably has DBCC checks being done occassionally (although I haven't verified that with the client), I did not do the DBCC's that Sybase recommends before preparing to do the cross-platform dump. Could that be the problem?

Might it help to set the DB to truncate log on checkpoint?

At any rate, any suggestions anyone has would be most gratefully accepted.

John

J M Craig
Alpha-G Consulting, LLC

http://www.alphagconsulting.com

nsjmcraig@netscape.net

 

[JMCraig]

Hi Folks,

Well, in case anyone needs a solution to this problem, here's what works for me:

/* get rid of transaction log entries
--you're going to do a full dump anyway so it presumably doesn't matter */
dump transaction testdb with truncate_only
go
/* verify that the log is empty--should be no more than 16 pages of reserved space */
use testdb
go
sp_spaceused syslogs
go
/* in addition to setting to single user mode, set to truncate the log on checkpoint too--just in case. (This is probably not necessary, but after days of experimenting, I'm not interested in finding the absolute minimal set of commands that'll do the job: this works and it takes no additional time.) */
use master
go
sp_dboption testdb, 'trunc', true
go
sp_dboption testdb, 'single', true
go
use testdb
go
checkpoint
go
/* one more dump of transaction log--get rid of the option change entries */
dump tran testdb with truncate_only
go
/* now start the steps that Sybase documents */
sp_flushstats
go
/* wait for everything to settle at this point, then: */
checkpoint
go
/* do the actual dump */
dump database testdb to '/data/sybdump/testdb.bkp'
go

A dump created this way will report something similar to the following during the cross-platform conversion (note that there's only one entry in the transaction log [first and last entries are the same ID and it's a checkpoint]):

Started estimating recovery log boundaries for database 'xpl_testdb'.
Database 'xpl_testdb', checkpoint=(33135568, 12), first=(33135568, 12),
last=(33135568, 12).

I hope this saves someone some trouble!

As far as I can tell, there's no way to know if the dump will produce a file that will load in cross-platform mode other than be ensuring that the transaction log is empty (via sp_spaceused: on a 2K-page-size DB like this one, it reports no more than 32K of data [one extent]).

John Craig
Alpha-G Consulting, LLC

http://www.alphagconsulting.com