• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 911
  • Last Modified:

Sybase db refresh

I have sybase db which need to be refresh.

In production it has db size of 200GB, 132 data and 68 log

But in test where I will do refresh, it has old size which 89GB, with 80GB data and 9 GB of log
0
motioneye
Asked:
motioneye
  • 7
  • 5
  • 2
6 Solutions
 
Joe WoodhousePrincipal ConsultantCommented:
If you're trying to move Production to Test, it can't be done. The destination must be at least as large as the source.

If you're moving Test into Production, it will work, but unless the segments & fragments are compatible, data & log will probably end up being mixed - which is not what you want in Production. Mind you I suspect you're not copying Test into Production. :)
0
 
motioneyeAuthor Commented:
Hi,
I'm copying production to test for a refresh , however in test we have lesser device compare we have in production, but I have added one big chunk ( few disk with 32GB ), will diff number of device cane become a concern for a refresh to complete ?
One question, if I have bigger db size in test will the refresh failed ? do I have to make test have exactly same size with production ? data and log segment ?
0
 
Joe WoodhousePrincipal ConsultantCommented:
The only absolute rule is that the destination database must be at least as large as the source. Larger is ok.

However unless the fragments are compatible, you will mix data & log in strange ways. It doesn't have to be exactly the same as the source, but that's the easiest way.

Suggest you drop & recreate test to be identical to prod (ie. the same fragments of the same size and segments created in the same order), because that way everything will work out.

Note the devices don't have to be the same - you could use half as many devices of twice the size, for instance. We don't care about the devices, we care about how big each chunk or fragment of the database is, in which order and with which segment(s).

Reverse-engineer your production database and drop & recreate test to match. Bigger is ok.
0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
motioneyeAuthor Commented:
The fragment definitely are not the same, that too much to create same set of fragment again. But if only fragment are not the same but we have same segment, will it still mix data and log ?
0
 
Joe WoodhousePrincipal ConsultantCommented:
Maybe. Depends on the size and mix.

This is the issue. When we load a database, ASE writes out the database page by page according to what's in the dump. (It's a bit more complicated, but that's good enough for what we're talking about.) If the database dump says the next page to write is a data page, but the database we're loading into says that page is a log page, we write a data page but now data & log are mixed.

This is why I said they just have to be compatible... but unless you understand exactly what is and isn't compatible, it's best to just make them identical.

What's the problem with dropping & recreating? Create/alter database doesn't take that long especially when you add "for load" to every statement, and you'll have no end of trouble unless you do...

There's also the issue of what's the point of having a Test system that isn't exactly like Production? If they're not the same, then what you put into Production hasn't been tested...
0
 
motioneyeAuthor Commented:
hmmm,
How could I proceed with drop and recreate ?  I already have db in test  which u suggested me to drop this all then recreate it ? anyway how can I generate a db script which identical with our production db ?
Then I'm not so sure how "for load" works , I could google it to find answer / sybase web for better understands
0
 
Joe WoodhousePrincipal ConsultantCommented:
We don't actually have to create it identical, just compatible. Which version ASE is this? There are any number of free ways to reverse-engineer it, but we can get close enough just from writing a query against the system tables.

You just add "for load" at the end of every create & alter database statement. Take a look at the Sybase manuals. This speeds things up when you're creating a database that you're immediately going to load a dump into.
0
 
motioneyeAuthor Commented:
I'm running on ASE 15
0
 
motioneyeAuthor Commented:
Hi,
One thing I'm not sure  if this db have been created previously using for load or not, is there a way to check this now ? and I'm using ASE 15.0 for both ( prod and test )
0
 
alpmoonCommented:
Actually you should be able to see the data and log segments (with their respective sizes) by using sp_helpdb <db_name>

You can also check sysusages system table in master db to see segments and their size in right sequence.
0
 
Joe WoodhousePrincipal ConsultantCommented:
It doesn't matter if "for load" was used before or not. When you add "for load" to create database, the only commands you can now run on that database are either alter database or load database. Once load database has completed, there's no difference.

It's very important to note that sp_helpdb does not return fragments in the right order. Its output is sorted alphabetically by device name. We do indeed need to look at system tables which is why I asked the ASE version, as the SQL is different depending on version.
0
 
Joe WoodhousePrincipal ConsultantCommented:
The SQL below will generate a "create database" command that can be used to recreate your Test database to be compatible with your Production. You can change the device names, all we care about is that there is room on each device for what you're trying to create there. So fewer & larger devices is fine.

Note this does not recreate the database identically! This is only useful for your specific scenario - when you're going to dump from one dataserver and load into another.

Run the SQL from within the database you want to refresh.

Some explanation/documentation:

char(10) is a CR
"+" is string concatenation, we are building a big string
@@maxpagesize is the page size of your ASE (2K/4K/8K/16K), expressed in bytes (ie 2048/4096/8192/16384)
the "size" column in sysusages is in pages, and we want to convert to Mb. So if you have a fragment of 2048 pages on a 2K page server, that's 2048/(2048/4) = 4Mb, but on an 8K page server, that's 2048/(8192/4) = 1Mb
the first CASE adds "create database [name] on " to the first line of the output
the second CASE adds "for load" as the very last line of the output

Note we are completely ignoring segments (ie data or log). When you run the output of this SQL, it will create a database where all data and log is mixed. BUT when you load your dump from Production, its segment mapping will completely overwrite this, and data & log will all be as they were in Production. This only works because we have fragments of exactly the same size in exactly the same order.

I'm not actually in front of an ASE right now so I can't swear I got the syntax completely correct, but it should be pretty close.

Again, you don't have to use exactly the same devices, you just need enough space on whatever device names you use instead. Of course if your devices were all identical this would work as is.

Good luck!
select   case when lstart = 0 then "create database " + db_name() + char(10) + " on "
              else char(10) + ",   "
         end
+        dev.name + " = "
+        convert(varchar, u.size/(@@maxpagesize/4))
+        case when lstart = (select max(lstart) from sysusages u2 where u2.dbid=db_id())
              then char(10) + "for load"
              else null
         end
from     sysusages u
,        sysdevices dev
where    u.dbid = db_id()
and      dev.vdevno = u.vdevno
order by u.lstart

Open in new window

0
 
alpmoonCommented:
Hi Joe,

As of ASE 15 sp_helpdb lists fragments in the right order. Motioneye is running ASE 15. So sp_helpdb should show devices as needed to recreate.

From ASE 15 reference manual:

sp_helpdb
Description

Reports information about a particular database or about all databases.
Syntax

sp_helpdb [dbname [, order]]

Parameters

dbname

    is the name of the database on which to report information. Without this optional parameter, sp_helpdb reports on all databases. dbname can include wildcard characters to return all databases that match the specified pattern.

order

    The default order of the output is by lstart, which is the order in which the databases were created or altered. Use device_name along with dbname to display the output of sp_helpdb ordered by device_name.
0
 
Joe WoodhousePrincipal ConsultantCommented:
Ah, I hadn't noticed that! Good to know, and yes that makes things much easier.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 7
  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now