| Author |
Topic |
|
Dinky
Starting Member
37 Posts |
 Posted - 2007-10-08 : 17:01:14
|
MS SQL Server 2005I have a table in our system that hold temporary data for doing calculations. It will process several million records in it. each time they forecast our products.....Is there any way to have the SQL server NOT add these transactions to the transaction log, since I'm going to wipe the data anyway? I'd like to be able to pick and choose the tables that are 'backed up' into the transaction log...Please advice. Thanks |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-08 : 17:04:08
|
| That is not possible.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-08 : 20:25:56
|
| If your main database is in FULL Recovery model you could put your "working" table in another database, which was set to SIMPLE Recovery Model.That might have less impact on your Main databases, at least.It increases the complexity of Database Restore, but I presume you can recreate this calculated data, so that may not matter too muchKristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-08 : 21:44:05
|
| Or try set recovery mode to bulk-logged, that generats least logs. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-10-08 : 21:47:31
|
quote:
Originally posted by rmiao
Or try set recovery mode to bulk-logged, that generats least logs.
That will only help for bulk operatoins which the poster didn't given an indication that they were doing.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-08 : 22:00:33
|
| Just point out one more thing to look at. |
|
|
|
evilDBA
Posting Yak Master
155 Posts |
Posted - 2007-10-09 : 07:13:16
|
| You can do your calculations in table variables (declare @tab table ....), their contents in not logged |
|
|
|
evjo
Starting Member
20 Posts |
Posted - 2007-10-15 : 07:13:44
|
| My best sughestion to you is to set your recovery mode to bulk logged or simple, then use a SELECT INTO, this is a bulk logged operation, so only 1 entry is put into the transaction log for the whole operation, rather then per record.Whenever I see an old lady slip and fall on a wet sidewalk, my first instinct is to laugh. But then I think, what if I was an ant, and she fell on me. Then it wouldn't seem quite so funny. - Jack Handey |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-10-15 : 08:31:59
|
even in SIMPLE mode, bulk inserts can blow your tlog if you don't set BATCHSIZE.
elsasoft.org |
|
|
|
|
Avoiding entries to transaction log