Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ThreePea
Yak Posting Veteran
83 Posts |
 Posted - 2002-02-13 : 15:22:43
|
| Is it possible to programmitcallly disable transaction logging via T-SQL?We have a small laptop that we use to demo new modules to existing customers. When we restore our production databases to the demo laptop we need to delete all the data in each table that does not belong to whichever particular customer we are going to.I've written a program that does all this automatically, and it works great. But sometimes it crashes because the transaction log grows bigger than the laptop's hard drive. Since this is a demo, non-production machine, I'd love to turn off logging while doing the delete operation.I know about TRUNCATE TABLE, but I need a WHERE clause on the delete because we keep the data for the customer in question.Thanks.Jerry======================================================="The world has achieved brilliance without conscience.Ours is a world of nuclear giants and ethical infants." -- General Omar N. Bradley (1893-1981) |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-13 : 15:30:39
|
| How about this: bcp out the data you want to keep (a SELECT with the opposite WHERE clause as DELETE), then TRUNCATE TABLE, then bcp or BULK INSERT in the data. This is about as minimally logged as you can get...bcp in/BULK INSERT are not logged by default. |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-13 : 15:31:41
|
| You can't disable logging. That would violate the ACID properties that make SQL Server a legitamate enterprise DBMS.However, you could select the values you want to keep into a temp table (Or table variable), truncate the table, then re-insert the left overs.-Chad |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-13 : 15:34:07
|
D**n you Rob! That is the second time today you have nipped me by a minute!oh well  -Chad |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-13 : 16:04:11
|
 But your answers are better than mine are, Chad!Keep an eye out for Ilya and Mark, those guys are sniping champions, they'll clip you by 5 seconds!Have no fear though: there will come a day, O Lord yes there WILL come a day, when Chad snipes me...big time! And I will curse his name too! And we shall call it...even!For anyone new to this site, please don't worry, this is the friendly environment we like to foster here on SQL Team: Gladly helping to answer your most difficult SQL Server questions while ruthlessly competing with one other!Now you got me thinking: We're world-class snipers, why not a SQL Team Clan! Quake III, Unreal Tournament, Tribes 2! Anyone else think this is a good idea? (god, I hope not!) |
|
|
|
ThreePea
Yak Posting Veteran
83 Posts |
Posted - 2002-02-13 : 16:29:08
|
| Rob & Chad,Those are both good ideas, I'll try them out. Thanks!======================================================="The world has achieved brilliance without conscience.Ours is a world of nuclear giants and ethical infants." -- General Omar N. Bradley (1893-1981) |
|
|
|
|
|
|
|
|
Disable transaction logging in code?