deleting records without writing to transaction log
-
For once, I'm glad I'm wrong too!
-Isaiah -
Believe me I've had that feeling too
* Noel -
-- Create a trigger on YourTable with the following code:
Create Trigger [YourTableInsertTrigger] on [YourTable] Instead of Insert
As
Truncate Table dbo.[YourTable]
Insert dbo.[YourTable] Select * from Inserted
-- Execute this in your script or procedure:
Insert YourTable
Select * from YourTable Where <criteria of rows you want to keep>
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
That's a nice try but:
- Trucate will Log Page deallocation
- Truncate Requires Elevated Priviledges
- Truncate can't be executed if the table has foreign keys
- Truncate will LOCK the users out so if you try this in a multiuser environment you will get a lot of failed transactions
... Too many problems
* Noel -
I'm just not feeling the luv...
PeteK
I have CDO. It's like OCD but all the letters are in alphabetical order... as they should be. -
Elaine wrote: " Part of the problem is that we ran out of disk space, so I don't have enough room to save to another table the data I need to keep"
Elaine, run the following command to reduce the size of the logged transactions, then shrink the database...
BACKUP LOG database_name WITH TRUNCATE_ONLY
... that should free up some space...
I don't normally recommend shrinking the database but this is an exceptional case.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The 'dangerous' part about using EM for changes, is that you have no explicit transaction control. If you click the wrong button - oops - no other choice than to take the system offline and start digging for the latest good backup.. (and perhaps also start thinking of a good explanation to the PHB)
In QA you can do:
BEGIN TRAN
<do some SQL>
..if all went as expected, COMMIT
..if something funky happened, ROLLBACK
..after that, you're back to square on (boss and users hopefully unaware) and you can figure out what went wrong and how to fix it.
/Kenneth
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply