Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2003
    Posts
    64

    Question Unanswered: Suppressing the use of transaction log?..

    We had to remove a column from a very large table last night (alter table MY_TABLE drop MY_COLUMN).

    This single query filled up the transaction log and we could not continue.

    Since we backed the entire table up first (via bcp), we'd like to be able to bypass the transaction log even if it was big enough (to make things faster).

    Is there a way to do that? MS-SQL server, for example, seems to allow to: "ALTER DATABASE xyz SET RECOVERY SIMPLE". There are also example like "ALTER DATABASE xyz MODIFY LOG OFF" -- but none of that works with Sybase

    Any suggestions? Thanks a lot!

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    As you've bcp-ed the data out - I would suggest dropping and recreating the table without the additional column, and then bcp-ing the data back in.

    Michael

  3. #3
    Join Date
    Apr 2003
    Posts
    64

    bcp-ing is ugly

    Yes, this is the method we are currently using. However, it -- obviously -- requires all data in the table to travel over the network twice. Just to drop a column? Seems useless and inelegant...

    Besides, this is not the only case, when I'd want to temporarily disable the transaction logging.

    It must be possible to start a car without putting the seatbelt on, so to speak.

    Does Sybase allow it?

  4. #4
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Actually an ALTER TABLE DROP ... does a SELECT INTO under the covers. That operation should also be minimally logged (i.e. same level of logging as the fast bcp). I guess the difference is that the SELECT INTO can't be split into batches to reduce the amount of log space needed for the page allocations.

    There's a tracefile that allows disabling of the transaction log, but I've never used it, and it is undocummented and it comes with dire warnings about completely breaking your server. A google search should turn it up (either in the Sybase FAQ or on Rob Vershoor's site.

    Michael

  5. #5
    Join Date
    Apr 2003
    Posts
    64

    Found the traceflag value

    Thanks for the hint, mpeppler! I found the table and the value 699 will turn off transaction logging for the entire SQL dataserver. This may be sufficient for our needs, although I'd expect to be able to do this per database, rather than for the whole server.

  6. #6
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Be very very careful with this, and don't expect much help from Sybase if you break your server...

    Michael

Posting Permissions

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