SQL server table logging
-
In Oracle one can do NOLOGGING on table so that you do not save the changes into rollback and redo logs, this way these tables work faster, especially some user defined temporary tables. IS there any way I can do this in SQL server, if so how ?
Thanks
Sonali
-
I am not aware of it in SQL Server.
-
If you set your recovery model to Simple or Bulk-Logged in SQL Server 2000 (or set the "select into/bulkcopy" option to True in previous versions), then any inserts done with SELECT...INTO...FROM or via one of the bulk processes (bcp, BULK INSERT, etc.) will not be logged and its performance will benefit. The downsides are that point-in-time recovery is disabled and your tran log for the entire database is rendered useless for restoring after you perform one of these non-logged operations.
--Jonathan
--Jonathan -
quote:
The downsides are that point-in-time recovery is disabled and your tran log for the entire database is rendered useless for restoring after you perform one of these non-logged operations.I just think the logs aren't useless because when you do transaction log backup on db with Bulk-Logged recovery model, sql server inserts extends modified by bulk logged operations right into that backup to avoid breaking the log chain....
And of course, you can't do trans log backup in Simple recovery model!
-
quote:
I just think the logs aren't useless because when you do transaction log backup on db with Bulk-Logged recovery model, sql server inserts extends modified by bulk logged operations right into that backup to avoid breaking the log chain....Okay, not useless, but you'll be missing any data that was inserted by one of the bulk operations. I don't want to deal with a database with inconsistent data, but this might meet the needs of the OP, as he references temporary tables; so thanks for correcting me!
quote:
And of course, you can't do trans log backup in Simple recovery model!
Yes, that was so obvious to me that I neglected to mention it. Thanks again!--Jonathan
--Jonathan -
quote:
Okay, not useless, but you'll be missing any data that was inserted by one of the bulk operations.Mayby it's my bad english or i'm still sleeping but I mus desagree with you
When you do transaction log backul on db with Bulk-Logged recovery model, the backup contanins:
1) Content of the transaction log which covers all non bulk-logged operations on db
2) Data pages (from db data files) added/changed by bulk-logged operations
So when you apply this backup when recovering db, you definitely and up with consistent data....
PLEASE, correct me if I'm wrong. Thanx!
On the other side:
First thing you should do when you are trying to recover demaged database is to take last transaction log backup to prevent loosing last changes to db. In case of Bulk-Logged recovery model and when the data file is demaged (disk failure etc.) this might by a problem....
-
quote:
When you do transaction log backul on db with Bulk-Logged recovery model, the backup contanins:
1) Content of the transaction log which covers all non bulk-logged operations on db
2) Data pages (from db data files) added/changed by bulk-logged operations
So when you apply this backup when recovering db, you definitely and up with consistent data....
PLEASE, correct me if I'm wrong. Thanx!
On the other side:
First thing you should do when you are trying to recover demaged database is to take last transaction log backup to prevent loosing last changes to db. In case of Bulk-Logged recovery model and when the data file is demaged (disk failure etc.) this might by a problem....
Liwoj--
You made me check.
This is indeed the case now with SQL Server 2000, so that's a major change from SQL Server 7 that I missed. SQL Server 2000 BOL also needs to updated, as it has:quote:
If the log is damaged, or bulk operations occurred since the most recent log backup, changes since that last backup must be redone.--Jonathan
--Jonathan
Yes, that was so obvious to me that I neglected to mention it. Thanks again!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply