Jake Hawkes
2007-03-22 20:13:33 UTC
Hi all,
Apologies for the cross-posting....
I have a problem that I've not seen before, and am quite baffled with.
First off, the details:
SQLserver 11.0.3, on solaris 2.6.
We are doing a hardware upgrade, from sparc20 to sparc 220R, with a StorEdge
5200 array, running Veritas 3.5 (I think).
I have created about 14 2gig volumes with Veritas, created the devices in
device fragment for each device, each 2046 meg in size. Then, I load the
database from tape, from the old machine (same sybase version, same OS
version, different hardware, different RAID)
1> load database archive from "/dev/rmt/0cbn" with file = "archive"
Now, sp_helpdb shows more device fragments than before, all on the same
devices, but this time, with sizes that are all over the place and dont make
sense, and some that show 0 k free. This then causes the whole database to
seem "full" and inserts fail, with the error that a particular segment is
full.
Unfortunately, I dont have with me the output of sp_helpdb or
sp_helpsegment, but I will have those in a few hours time (when my flight
lands).
When this first occured, I thought it was several things.
1) Initially, I had created the database with "2048" as the size in the
"create database" command, and I thought that it was too big for Sybase,
since some of the segments seemed to add up to 2048, on the same device. I
changed to 2046 and that didn't help.
2) Initially, I had created the database with the "for load" option, and I
thought that perhaps the old segment structure from the previous machine
(which had totally different device layouts) had somehow messed things up.
I created the database without the "for load" option this time, and the
problem is still here.
Interestingly, the create database command took quite a while to complete
(45 mins) and I figured this was because the server was pre-allocating all
the segments etc, and that this would fix my problem, and indeed, after
creating the database, the segment map looked fine, but after the database
load, it is all messed up again.
My suspicion is that I am creating the database incorrectly, and that sybase
is having to create extra segments or something. The weird thing is, before
the database load, there are 10 segments, all the same size. After the
database load, there are more segments, and they all have different
sizes, and some of them have 0k free. Why wont Sybase use the other
segments which are empty?
I hope someone can shed some light on this.
I will follow up with output from sp_helpdb and sp_helpsegment as soon as I
can.
Best regards, and thanks in advance,
-Jake
for load
Apologies for the cross-posting....
I have a problem that I've not seen before, and am quite baffled with.
First off, the details:
SQLserver 11.0.3, on solaris 2.6.
We are doing a hardware upgrade, from sparc20 to sparc 220R, with a StorEdge
5200 array, running Veritas 3.5 (I think).
I have created about 14 2gig volumes with Veritas, created the devices in
disk init name = "archiveX", physname =
"/dev/vx/dsk/scada2dg/archiveX_dat", vdevno = 10, size = 1048575
(( where X is 1 - 10 ))"/dev/vx/dsk/scada2dg/archiveX_dat", vdevno = 10, size = 1048575
disk init name = "archivelogX", physname =
"/dev/vx/dsk/scada2dg/archiveX_log", vdevno = 20, size = 1048575
(( where X is 1 - 2 ))"/dev/vx/dsk/scada2dg/archiveX_log", vdevno = 20, size = 1048575
create database archive on
archive1 = 2046,
archive2 = 2046,
...etc...archive1 = 2046,
archive2 = 2046,
log on
archivelog1 = 2046,
archivelog2 = 2046
Which gives me a database. sp_helpdb shows me what I would expect. 1archivelog1 = 2046,
archivelog2 = 2046
device fragment for each device, each 2046 meg in size. Then, I load the
database from tape, from the old machine (same sybase version, same OS
version, different hardware, different RAID)
1> load database archive from "/dev/rmt/0cbn" with file = "archive"
Now, sp_helpdb shows more device fragments than before, all on the same
devices, but this time, with sizes that are all over the place and dont make
sense, and some that show 0 k free. This then causes the whole database to
seem "full" and inserts fail, with the error that a particular segment is
full.
Unfortunately, I dont have with me the output of sp_helpdb or
sp_helpsegment, but I will have those in a few hours time (when my flight
lands).
When this first occured, I thought it was several things.
1) Initially, I had created the database with "2048" as the size in the
"create database" command, and I thought that it was too big for Sybase,
since some of the segments seemed to add up to 2048, on the same device. I
changed to 2046 and that didn't help.
2) Initially, I had created the database with the "for load" option, and I
thought that perhaps the old segment structure from the previous machine
(which had totally different device layouts) had somehow messed things up.
I created the database without the "for load" option this time, and the
problem is still here.
Interestingly, the create database command took quite a while to complete
(45 mins) and I figured this was because the server was pre-allocating all
the segments etc, and that this would fix my problem, and indeed, after
creating the database, the segment map looked fine, but after the database
load, it is all messed up again.
My suspicion is that I am creating the database incorrectly, and that sybase
is having to create extra segments or something. The weird thing is, before
the database load, there are 10 segments, all the same size. After the
database load, there are more segments, and they all have different
sizes, and some of them have 0k free. Why wont Sybase use the other
segments which are empty?
I hope someone can shed some light on this.
I will follow up with output from sp_helpdb and sp_helpsegment as soon as I
can.
Best regards, and thanks in advance,
-Jake
for load