SQL ANYWHERE 16

I am reaching the DBSpace limit on one of our databases. There is a 4K page size so our DBSpace limit is 1 TB. My question is can I move existing tables into a new database space or must I unload and reload.

asked 11 Jan, 15:25

J%20Diaz's gravatar image

J Diaz
1.1k293455
accept rate: 11%

Sorry this is SQL Anywhere 17.0.10.6160

(11 Jan, 15:34) J Diaz

Here's what I am thinking I need to do. Following the logic associated wit unloading and reloading a database involved in replication. This is a consolidated database involved in SQL Remote replication. I want to move certain tables and their indexes to a new DBSpace I'm thinking I will duplicate the table structures with a new table name and insert into select. Then truncate the tables in the original DBSpace and rename the tables in the new DBSpace.

Does this sound ok? Of course I'll test first

(11 Jan, 16:12) J Diaz
Replies hidden
2

1 TB

Awesome... your indexes may benefit greatly as well, with 8K page size.

(11 Jan, 16:13) Breck Carter

Hm, I would do an unload/reload with a modified script with the according table created in a different dbspace, see here. Your suggestion to create a new table and then copying data might be more tricky w.r.t. log offsets and other effects on publications...

(11 Jan, 17:10) Volker Barth

> This is a consolidated database involved in SQL Remote replication.

LOL! You got me! (to suggest a solution that breaks replication:)

(12 Jan, 11:20) Breck Carter
Comment Text Removed

> I want to move certain tables and their indexes to a new DBSpace

Are you proposing avoiding the 1TB file size limit by creating an additional dbspace file, rather than increasing the limit to 2TB by changing the page size to 8K? (it's probably a dumb question, but I gotta be sure :)

Have you considered "sharding" the table(s), possibly along subscription lines?

(12 Jan, 11:39) Breck Carter
showing 3 of 6 show all flat view

Update: This is a VERY BAD ANSWER because the database uses SQL Remote replication.


> can I move existing tables into a new database space or must I unload and reload

AFAIK UNLOAD and LOAD is the fastest way to move, er, copy tables.

Based on a recent conversation I believe dbunload -ac uses named pipes, or you can go bare metal for individual tables like Foxhound does when upgrading to a new version, using the technique described here

http://sqlanywhere.blogspot.com/2010/12/unload-and-load-via-named-pipes.html

and here

http://sqlanywhere.blogspot.com/2011/08/unload-to-named-pipe-beats-unload.html


permanent link

answered 11 Jan, 16:10

Breck%20Carter's gravatar image

Breck Carter
31.2k5036861006
accept rate: 20%

edited 12 Jan, 11:52

I have decided to follow the help and do a manual unload and reload of a database involved in replication. I will then modify the reload script to locate certain tables in a new dbspace. My current plan is to create three or four new spaces as well as use an 8K page size giving me a maximum database size of 6 or 8 TB. Of course all DBSpaces will not expand equally so this is only a very rough max db size.

I do have several related questions 1) Can I turn off the automatic dbspace expansion? 2) If auto expansion is enabled how will these be expanded each separately as required I assume? 3) When extracting new databases will these default to use the same dbspace configuration?

permanent link

answered 2 days ago

J%20Diaz's gravatar image

J Diaz
1.1k293455
accept rate: 11%

As to your first two questions: What do you mean by "automatic dbspace expansion"? AFAIK, like the default system dbspace addional dbspaces do automatically grow when more database pages are needed - if they would not (say, because of a disk full condition), the according transaction would fail... - you can monitor file growth via an GrowDB event...

I don't know how dbspaces affect the default extract process, however, you could certainly customize that.

(yesterday) Volker Barth

I ran a test on a large 1TB database and was able to crash the database by inserting data until the system auto expanded the "System" dbspace and reached the maximum. Once this occurs the db will not restart using normal dbsrv parameters. I was able to force a restart by renaming the log file and using the -f parameter. It's interesting because the log file created with -f starts at an offset less than the ending offset of the log file in place when the maximum space was exceeded. I believe a checkpoint might have caused the expansion not sure though.

I'll be looking deeper

(yesterday) J Diaz
Replies hidden

Well, as stated, the according GrowDB system events might be of help here - I don't know whether the dbsrvX -fc option does also handle file limits or only disk full conditions.

(yesterday) Volker Barth

The V17 dbxtract page does not contain the string "dbspace"

That would fill me with dread if I had your responsibilities, and would prompt me to mark the rest of January and all of February for "testing" :) ...the good news is, it is clear that SQL Remote is actively maintained if the vast increase in dbxtract options is any indication.

(yesterday) Breck Carter

> follow the help and do a manual unload and reload of a database involved in replication

...bet that lifted a great weight off your mind :)

(yesterday) Breck Carter
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×22

question asked: 11 Jan, 15:25

question was seen: 103 times

last updated: yesterday