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