Question Re: DBSpace size limit in Technology Q&A

DBSpace size limit

JimDiaz — Mon, 11 Jan 2021 15:25:56 GMT

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.

Re: DBSpace size limit

JimDiaz — Mon, 11 Jan 2021 15:34:14 GMT

Sorry this is SQL Anywhere 17.0.10.6160

Re: DBSpace size limit

Breck_Carter — Mon, 11 Jan 2021 16:10:48 GMT

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

Re: DBSpace size limit

JimDiaz — Mon, 11 Jan 2021 16:12:07 GMT

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

Re: DBSpace size limit

Breck_Carter — Mon, 11 Jan 2021 16:13:23 GMT

1 TB

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

Re: DBSpace size limit

VolkerBarth — Mon, 11 Jan 2021 17:10:21 GMT

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...

Re: DBSpace size limit

Breck_Carter — Tue, 12 Jan 2021 11:20:12 GMT

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

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

Re: DBSpace size limit

Breck_Carter — Tue, 12 Jan 2021 11:39:47 GMT

> 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?

Re: DBSpace size limit

JimDiaz — Thu, 14 Jan 2021 16:12:14 GMT

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?

Re: DBSpace size limit

VolkerBarth — Fri, 15 Jan 2021 02:58:15 GMT

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.

Re: DBSpace size limit

JimDiaz — Fri, 15 Jan 2021 12:16:22 GMT

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

Re: DBSpace size limit

VolkerBarth — Fri, 15 Jan 2021 12:26:31 GMT

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.

Re: DBSpace size limit

Breck_Carter — Fri, 15 Jan 2021 16:03:54 GMT

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.

Re: DBSpace size limit

Breck_Carter — Fri, 15 Jan 2021 16:07:56 GMT

> 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 🙂