Problem with Logsegment

If I have two machines, one production machine and other backup machine and I want to load the production database to backup machine. I installed Sybase with the same configuration. Create the required database with the same amount to log as on production machine. Then I load the database dump of production machine to this backup machine. It loaded the whole database, creates the segments automatically but create problem with LogSegment. I define the for example two seperate database devices for log e.g. Log01 & Log02, while I have loaded the database it I dont know why will use any one log device for data or data & log both and other database device that was defined for data only will be used for log automatically. Why it happen? It also gives me the message that your data & log are placed on same device which can create problem in case of physical device crash i.e. recovery is impossible. How can I manage to get rid of this problem? Should I batter create the segments as well or after loading database I adjust the segments for log?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Do you have a tool such as DBArtisan? You should extract the DDL for the devices and the database definition and compare the differences you find between the production and backup machine. Not sure if you can do it in Sybase Central but possibly.

Even if your databases sizes in production machine and backup machine are same , you are getting problem because of the way the database is created. You can check whether the database is created in order ( on devices in order) by selecting the information of master..sysusages.
In your case there must be mismatch of sysusages entries in both servers.

Anyway,You need to recreate the database in backup machine. Follow the following steps :

1. select information from master..sysusages table of  Production machine :
   select * from master..sysusages where dbid = (dbid of the database)
or if you do not have many databases you can use command like :
  select * from master..sysusages. You can look at the segmap and size columns. Divide  size by 512 to get the size in MB. Check your segmap orders like 3,4 or 7.  3 is the data segment, 4 is the log segment 7 is both data and log.Values higher than 7 indicates user defined segment.
 Now you can find in which order you need to create that means data, log order for example :
     segmap        size in (mb)
      3                100
      4                200
      4                100
      3                200
      3                100

You need to create the database in the following order :

create database database_name
on  data_device=100 log on log_device=200  ( for load)
alter database database_name on log_device=100 (for load)
alter database database_name on data_device=200 (for load)
alter database database_name on data_device=100 (for load)

After cretaing the database in backup machine, you can compare the values of sysusages tables in both servers to make sure that you have created correctly. After that you can load the dump as usually.

If you have segmaps like 7 you need to create both data and log in one device. For values higher than 7 you need to create user segment before loading the dump.

**** you may use the following query to get the information from sysusages :

   select dbid,segmap,(size/512) from master..sysusages

This will work for you. If you have more question ,please let me know.

Again, the tools like DBArtisian & Sybase Central also can extract the schema for create database, but sometimes these tools mislead too...

Thanks ...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TahirKhalilAuthor Commented:
David! I dont have DB-Artisan with me, is it available for Sun Solaris O/S?

dk99! Your answer seems good.But is it necessary to load the dump of database to a different machine that have the same database size as well as same settings of segment. Here I am not talking about LogSegment. I have the same size of logsegment both on backup & production servers. The size of logsegment is same on both the servers. Why it amalgumate the logsegments which are of the same size e.g. 1024MB with 2 devices. We have 1024 MB Log on two different devices 512MB & 512MB. It still create the problem while you load the data. It uses the other 512MB data segment insted of Log for logsegment. I m creating the database as same you told me.

Create Database DBName
On DataDevice01 = 1024,
     DataDevice02 = 1024,
     DataDevice03 = 512,
Log On LogDevice01 = 512,
           LogDevice02 = 512

After creating this database I checked the status of logsegment through

sp_helpsegment LogSegment

its shows me the correct results that I have same in Production system. But when I trying to load dump of production, It create problem and change the device for LogSegment ie instead of LogDevice01 it uses DataDevice03

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

The space storage in both the server databases are different.
You need to check the sysusages information. I am pretty sure the sysusages information are different. Check the order of  segmap & size in both cases.

If you are sure,you created as per sysusages information :

the sysusages information in your production server should be as follows :

dbid    segmap  size
             3         1024
             3         1024
             3         512
             4         512
             4         512

You should find this type of entry in your sysusage of production machine. I only extracted this information from your database creation script.

Since you are telling it uses datadevice03 instead of logdevice01, i am sure your the order in sysusages in both the servers are changed while creating.
Please check once again the sysusages information in production and create accordingly. If still you are not able to do, please next time post the information from sysusages table of production here, so that I can look at before give you next solution.

Thanks & Good Luck.
TahirKhalilAuthor Commented:
Hi DK !

Look the problem is the same. I have created the data devices and logdevices as I required them. Is it necessary that their names should match eachothers? If not! than I have created the database of same size (u know if I create a smaller database I cannot load my production database). The names of devices are different. One more thing I am not creating any segment except LogSegment thru create database. When I load the database It creates the segments automatically.

Look I am not concerned with the data devices that are okay, whatever scheme SQL Server uses its no problem for me, but the problem is still with Log. I have defined the same size of Log on each database while creating databases. Why it uses the data device instead of logdevice that I already have specified for it. I have checked with one more expert who says me its the problem with Sql Server 11.2.3, what I am using here.
Hi Tahirkhalil,

It is not necessary to match name of the database devices in both the server. Usually this type of problem occurs when the order of segmaps are not same in sysusages table of source and target database.

I do not have any more comments if you are sure that entries in sysusages table of both servers( production and backup ) are same.

I can only give my comments once you post the entries of sysusages from your production server. This I had mentioned in my previous comments. Did you check the entries of the sysusages table in production? Did the entries match as per my guessing entry from your database schema ?

 If possible please post the output of the following query from your production server :

select dbid,segmap,size from master..sysusages where dbid = dbi_id("database_name")

I do not think it is a bug with sybase sql server. Infact I have been working with all versions of SQL Server in all platforms except the version what you mentioned(11.2.3). I am not sure whether Sybase has this version. Usually versions between 11.0 & 11.5 are like 11.0.1,11.0.2,,11.0.3,, .

TahirKhalilAuthor Commented:
hi dk!
its sql server 11.0.3 its my mistake
TahirKhalilAuthor Commented:
hi dk !
should i send you the output of the sysusages so that you can guide me how to create devices & segments?
Yes please.
And also send me  the output of sp_helpdb database_name from your production server.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.