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