load database xx with listonly=create_sql giving wrong ddl for database

[Former Member]

Database size increased by 7.5 MB . alter with non whole number ( like 7.5 MB) in my case .

When we use new feature to generate database schema using lisonly=create_sql .  It takes floar value and suggest to alter database size by 7 MB.

but if we generate ddl from sybase central or ddlegen then it suggest to alter database by 8 MB .--> Which looks correct .

As target DB size should be same size or bigger size to load database .

My Case :

sp_helpdb xx   --> See care fully xxx_data04 --7.5 MB

=========

xxx_data01            500.0 MB data only Jun 19 2009 12:25PM               30

xxx_log01             100.0 MB log only  Jun 19 2009 12:25PM not applicable  

xxx_data01           3000.0 MB data only Jun 19 2009 12:25PM               56

xxx_log01            1000.0 MB log only  Jun 19 2009 12:25PM not applicable  

xxx_data01           2000.0 MB data only Jun 19 2009 12:25PM               14

xxx_log01            2000.0 MB log only  Jun 19 2009 12:25PM not applicable  

xxx_data02           4000.0 MB data only Jun 19 2009 12:25PM              168

xxx_data01           2000.0 MB data only Jun 19 2009 12:25PM               28

xxx_data03           4000.0 MB data only Mar  1 2010  4:22PM                0

xxx_data04              7.5 MB data only Jul  7 2011 10:28AM                0

xxx_data05           4000.0 MB data only Jul  7 2011 10:31AM                0

xxx_data06           4000.0 MB data only Jun 29 2012  1:26PM                0

xxx_data01            500.0 MB data only Mar 12 2014 10:09AM                0

xxx_data07           4000.0 MB data only May  9 2014 11:57AM                0

xxx_data08           4000.0 MB data only Jan 26 2015 10:31AM                0

xxx_data09           4000.0 MB data only Jun 29 2015  5:12AM          3132118

load database xxx with listonly=create_sql  -->> See care fully   , xxx_data04 = '7M' --> it should be 7.5 MB

=============================

CREATE  DATABASE xxx

  ON xxx_data01 = '500M'

  LOG ON xxx_log01 = '100M'

go

ALTER  DATABASE xxx

  ON xxx_data01 = '3000M'

  LOG ON xxx_log01 = '1000M'

go

ALTER  DATABASE xxx

  ON xxx_data01 = '2000M'

  LOG ON xxx_log01 = '2000M'

go

ALTER  DATABASE xxx

  ON xxx_data02 = '4000M'

  , xxx_data01 = '2000M'

  , xxx_data03 = '4000M'

  , xxx_data04 = '7M'

  , xxx_data05 = '4000M'

  , xxx_data06 = '4000M'

  , xxx_data01 = '500M'

  , xxx_data07 = '4000M'

  , xxx_data08 = '4000M'

  , xxx_data09 = '4000M'

go

==== DDLGEN /Sybase Central

Every thing same except :

ALTER DATABASE bat

     ON bat_data04 = '8M' -- 3840 pages

go

Appreciate your suggestion ...

[dan_thrall · Accepted solution]

Hi Pandey,

Adding notes to the SCN after the Incident was created for benefit of those curious about this report.

Whenever creating or altering for space, we allocate as many Allocation Units (AU) that are available up until the requested amount.

What this means is let's say we have 10 megs free on a device and you try to alter for 20.  The alter will be successful, giving you 10 megs.

Now enter in this problem.  For whatever reason, the size of this device has a non-rounded number of megabytes free.  There are a couple ways this can happen and most commonly seen on raw partitions when the size of the partition isn't quite a whole meg in size.

Some math -

This ASE is 2k page size.  An AU is 256 pages in size. 

This means an AU 256*2048 = 524288 bytes or .5 megabyte.

Now going back to the alter logic.  We will alter as long as we have free AU's.  In the case of 2k page, this also means we can get into (1.0*sysusages.size/512) to be a non-rounded number as seen in the math above.

Why does create_sql not indicate this?

The short answer is create/alter database accepts whole numbers for size specification and not pages.

The technical answer is only integer values are accepted.

Added reason here, is because of the whole number integers for create/alter, the create_sql is not checking for half meg fragments. 

I've logged CR 797291 to have create_sql be half meg aware.

How do you create so you can load this database?

Using the example above:

You can disk init xxx_data04 to be 7.5M, then when create/alter specify "on xxx_data04='8M'" which in turn will give you the 7.5 megs.  To be safe, have this particular alter all by itself, just to avoid confusion.  This works because device sizes can be created for a specified page count.

You can also do a bit of sysusages manipulation during the process which I don't recommend, since updating system tables can be troublesome if not handled carefully.  Essentially it boils down to altering for 8M, subtracting 256 pages (.5M) from size to make 7.5M , run dbcc dbrepair(dbname, remap), then proceed with altering.  Use at your own risk, use a test server first, have master backups, use a transaction, etc. 

HTH

Dan

[Former Member · Accepted solution]

Dan,

Excellent lesson for us all. I particularly like the simplicity and effectiveness of

"You can disk init xxx_data04 to be 7.5M, then when create/alter specify "on xxx_data04='8M'" which in turn will give you the 7.5 megs."

LOL!

Regards,

Jean-Pierre

[Former Member · Accepted solution]

P.S. As far as the manipulation you are talking about... Are you talking about a direct update to the master..sysusages 8MB fragment for the database? Thank you. Regards, Jean-Pierre

[former_member188958 · Accepted solution]

Yes, the final method Dan is talking about is doing direct updates to master..sysusages.

-bret

[Former Member · Accepted solution]

We have same proble when we generate ddl from ddlgen and sybase central ...

create_sql -- takes Floor values , ddlgen/sybase central take ceiling values .

Regards

Ajay Pandey

[Former Member · Accepted solution]

my problem is similar but i do not have the luxury of creating a device just for this database. how can i create the database on an existing device ?

[dan_thrall · Accepted solution]

Hi David,

The safest route is to get to the .5 meg fragment naturally.  What I mean is you would alter on an existing device that just so happens to have a .5 meg AU at the end.  You can fill the device with a dummy database first leaving the desired amount free, then alter as needed.  Once this is done then finally drop the dummy filler database.

Here's a way to quickly check if you have a .5 meg device on 2k page ASE

1> select name,(1.0*high+1-low)/512 from sysdevices where (1.0*high+1-low)/512 %1 !=0 and status !=16

2> go

name

------------------------------------------------------------

         ----------------------------

halfmeg

                       7.500000000000

(1 row affected)

The manual update method can be done.  Again, throwing considerable caution here as modifying sysusages can get you into trouble very quickly if you aren't careful.

Finally the other safe route is a database can load as long as it is the same size or larger than the dump.  The fragments should remap.

Using the example from the first posting

1> select segmap, lstart, size from sysusages where dbid=db_id("test")

2> go

segmap      lstart     size

----------- ---------- ----------

           3          0       5120

           4       5120       5120

           3      10240       3840  <<This is the 7.5M fragment

           4      14080       5120

(4 rows affected)

Let's create and load a copy.

1> create database testload on reprodata=10 log on reprolog=10

2> go

1> alter database testload on reprodata=8  --this will not be 7.5, but a full 8M

2> go

1> alter database testload log on reprolog=10

2> go

--sysusages BEFORE load

1> select segmap, lstart, size from sysusages where dbid=db_id("testload")

2> go

segmap      lstart     size

----------- ---------- ----------

           3          0       5120

           4       5120       5120

           3      10240       4096

           4      14336       5120

Now let's load the dump of the source to it.

1> load database testload from "/tmp/test.dmp"

2> go

[snip]

Backup Server: 3.42.1.1: LOAD is complete (database testload).

All dumped pages have been loaded. ASE is now clearing pages above page 19200,

which were not present in the database just loaded.

ASE has finished clearing database pages.

...

1> online database testload

2> go

1> select segmap, lstart, size from sysusages where dbid=db_id("testload")

2> go

segmap      lstart     size

----------- ---------- ----------

           3          0       5120

           4       5120       5120

           3      10240       3840

           4      14080       5120

           3      19200        256

(5 rows affected)

Now you can see the extra unused .5 meg is tacked on to the end and the 7.5M fragment is regenerated just as it existed in the dump.

You could then do some tricks to swallow this space up into a full fragment.

Options include

"alter database off" to remove it entirely

make your next alter add a half meg using the same logic discussed in this thread.

Since we are only looking at a single AU, the shortest route to a final solution might be to go straight to alter database off to shrink 256 pages from the device fragment.  Future dump/load can reconcile the database hole.  Alter database off accepts page ranges.

Cheers

Dan

[Former Member · Accepted solution]

Thank you for confirming Bret. Your answer though, makes me laugh... So mysterious. To me it looks like a pretty straight forword update to master..sysusages... If I had a server to practice on, I would send you guys the code.

Either way, I really enjoyed, the thread. Good simple work around in the end.

Cheers.

Jean-Pierre