SQL Server Performance

turn off transaction log

Discussion in 'General DBA Questions' started by manu_dba, Jun 27, 2007.

  1. manu_dba New Member

    I have a quick question plz.

    can we turn off the transaction log in sql 2000 for some
    query or for some time so that it should not log that particular operation.
    .

    because till now what i have learned is no. It is an integral part of the SQL system and cannot be turned off.

    Certain operations are "non-logged" like fast-BCP and select into. This means they do not log record updates. However they DO log extent allocations. They need to do this so that if the process is terminated unexpectedly (maybe the power goes out), SQL can recover the space. This greatly reduced logging can result in better performance - but as it makes the database non-recoverable from transaction-logs should be used with caution



    what do you suggest
  2. madhuottapalam New Member

    there is no transaction in sql server which is not logged. Yes you can say there are minimally logged transactions. yes u r rightly said that we can not turn off any trasaction in sql server.

    Madhu
  3. satya Moderator

    Why do you need to turn off logging, in this case if the disk space is a problem you can take help of recovery models or keep the transactions in smaller batches to execute. THere is a risk of losing auto-recovery if used SIMPLE recovey model.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  4. manu_dba New Member

    thanks a lot..satya..you are right turning the DB into simple is
    one of the best option if we dont want to transactions to be logged...
    but please let me know more on this if I am missing any thing
    regarding logg of transactions.
  5. satya Moderator

    Read more about SIMPLE recovery model in books online, When SIMPLE is specified, the database can be recovered only to the last full database backup or last differential backup.
    quote:With the Simple Recovery model, the database can be recovered to the point of the last backup. However, you cannot restore the database to the point of failure or to a specific point in time. To do that, choose either the Full Recovery or Bulk-Logged Recovery model.

    The backup strategy for simple recovery consists of:

    Database backups.


    Differential backups (optional).
    So test it before and have a thorough backup routine to recover from loss.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

Share This Page