Results 1 to 6 of 6
  1. #1
    Join Date
    May 2004
    Posts
    12

    Question Unanswered: Speed up/Shrink SQL Anywhere 7 database (windows)

    Hello,

    I have a 1.8GB Sybase 7 SQL Anywhere database on Windows 2000 and I am trying to find a way to shrink the database as well as speed it up a little. I have a rather big table that contains Audit data and I thought of exporting these data from the database and keep them off-line (they are not needed on-line) and then delete them from the database.

    I didn't see though any difference in db space after deleting them. Any ideas of how to shrink the database ?

    sspan

  2. #2
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    Hiya,

    ASA is slightly different and as part of your regular(maybe once every 3-6 months) maintenance activity needs to be rebuild.

    This so that you can shrink the database. ASA through its lfetime only keeps growing and for a very active database would needs rebuilding using the dbunload -ar. This is one step unload and reload and very user interactive.

  3. #3
    Join Date
    Aug 2004
    Posts
    18
    To shrink an ASA database, you need to unload and reload it, but that alone is unlikely to affect your performance much. There are several things to look at to speed things up:

    Make sure the .db file is not fragmented on the disk (you'll need to stop the database to do this)

    Check for proper indexes to support the queries you are doing. Poorly chosen or non-existent indexes can easily make a response time difference of two orders of magnitude (100x) or more.

    If you post the slow queries, we might be able to help you more with what indexes to use.

  4. #4
    Join Date
    May 2004
    Posts
    12

    Exclamation

    Thanks for the info. I tried to rebuild it using the following:

    dbunload -c "dbf=e:\kpt\kpt.db;uid=DBA;pwd=SQL" -ar E:\KPT\REBUILD

    on the Server where the database was running and I got the following message:

    Adaptive Server Anywhere Unload Utility Version 7.0.2.1402
    ***** SQL error: Database creation failed

    I also noticed the following text in the debug log file of Sybase:

    --------------------------------------
    Mon Aug 30 2004 15:02
    CONN: Application information:
    "HOST=SERVER;OS=Windows 2000 (Service Pack 4);PID=0x378;THREAD=0xf2c;EXE=C:\Program Files\Sybase7\SQL Anywhere 7\win32\dbunload.exe;VERSION=7.0.2.1402;API=DBLIB"
    CONN: Attempting to connect using:
    UID=DBA;PWD=***;DBN=KPT;DBF=e:\kpt\kpt.db;ENG=KPT; CON=Server;ASTOP=YES;DSN=KPT;INT=NO;DBG=YES;LOG=E: \KPT\SybaseProblem.log;DMRF=NO;ASTART=NO
    Trying to start SharedMemory link ...

    SharedMemory link started successfully

    CONN: Connected to the server
    CONN: Connected to database successfully
    Creating system tablesError in file mksadb60.sql at line 256 with sqlcode -645
    --------------------------------------

    The Sybase help file says, regarding error -645:
    Probable cause: Your attempt to initialize a file for a database or for a write file failed.

    Any ideas why this is happening ?

    Regards

  5. #5
    Join Date
    May 2004
    Posts
    12

    Unhappy

    Any help with this one ?

  6. #6
    Join Date
    Aug 2004
    Posts
    18
    Rather than trying to do it all in one step (which I've never gotten to work), do the unload, database re-creation, and reload as 3 separate steps, each triggered manually from Sybase Central, and saving off the .db and .log files after re-creating the database, but before reloading it. Then if it (for example) fails on the reload, you don't have to re-do the unload and the database re-creation.

    As to your specific problem, did you make sure you have your view nesting depth set correctly? If it's too shallow, the reload will probably fail.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •