Temporarily avoiding writing into trans log
-
Guys,
Is there a way to temporarily disable logging into the transaction log.
In our system, we perform purging of our database every night, where the purging consists of 2 steps:
1. For each table, insert the data, to be deleted, into a corresponding "purged" table, to remain there for one day only.
2. For each table, delete the unnecessary data (i.e. same data stored in purged tables in step 1)
During these 2 steps, the transaction log grows, and since we perform the transactional log back up, the back up at that time is huge. We are running a bit low on the hard disk space and I'd like to disable logging into the transaction log when these operations are performed.
I really don't care about being able to recover this data.
I thought that one option is to set the database to simple recovery, then perform the purging of the database, and then change back to full.
However, I think that trans log can grow even if recovery model is simple [although you won't be able to retrieve any changes].
So, is there a way to delete a portion of a table [or insert into it] so that no data is written to a transaction log (I know that we can use TRUNCATE if we need to remove whole table without logging)?
Thanks a lot
-
I would say that your only two real options here would be to use Simple recovery mode or to truncate your log file in between each major data manipulation step. You are correct when you say that the Simple recovery mode will still grow the log, but it will only grow it enough to perform the current operation. Once the commit takes place, that portion of the log will be overwritten or reused instead of appended to as it would in Full recovery mode. If you don't care about recovery and this is all happening over night when no users are on the system changing other data, I would say go with changing your recovery mode. If you are so limited on disk space that your logs cannot support a Simple recovery mode size transaction log, I would suggest adding more disk space.
-
Thanks for the suggestions!
-
Even if the db is in simple recovery model, if the transaction is big you may run out of space sometimes if don't have enough space...
In the above mentioned scenarios reduce the transaction size using WHILE loop or CURSOR....
If you are deleting millions of rows... delete 100/1000/10000 at a time, it will more time to delete but with less blocking and without tlog issues...
MohammedU
Microsoft SQL Server MVP -
IIRC it is also worth noting that when changing from simple to full recovery the actual change only occurs after taking a full backup so you should schedule one as soon after the switch as possible so transactions after your major changes are logged to the transaction log properly.
- James
--
James Moore
Red Gate Software Ltd -
Another thought if you can do this safely:
Why not do the following:
- drop the previous days purged table
- alter the main table and rename it to be the purged table
- create a new main table
This should have the same affect and should not use anywhere near as much transaction log space - ofcourse this only works if you empty the main table each day and start afresh.
- James
--
James Moore
Red Gate Software Ltd -
We do something similar when we do large updates or purges. We store them in another DB that is simple recovery, rather than clutter up production.
-
The tables we are purging are replicated. As such I don't think I can just drop them - replication will fail.
Great advices though...
-
OK now what type of replication?
-
Transactional.
-
" ... 1. For each table, insert the data, to be deleted, into a corresponding "purged" table, to remain there for one day only. ..."
In this step, your "purged" table could be in another DB so it doesn't affect the log size, since it sounds like it just there temporarily. ... unless I'm misunderstanding something
-
What ShekharNaidu said. Also, the notion of a avoiding writing to the transaction log, even if it were possible, is out. Transactional replication uses the transaction log to create commands to run on the subscriber(s) to keep them in sync.
You are probably going to need more disk space. Are you using 3 physical servers for your replication, as in separate publisher, distributor, and subscriber?
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply