May 21, 2008 at 6:21 am
Is it possible to run code without the transaction log being filled? I can't get my code to run because of the transaction log filling up.
May 21, 2008 at 6:40 am
Hi Shevonne,
You can set the database to the simple recovery model:
ALTER DATABASE MyDB
SET RECOVERY SIMPLE
This will minimally log most operations and truncate the log file on each checkpoint. It does mean that you can't backup or restore the log though, so don't use it if you can't afford to lose any data.
Cath
May 21, 2008 at 7:00 am
You should read and understand what the log is doing. It protects your database and ensures integrity.
If this is production data, you should be running with full recovery mode and perform log backups in addition to database backups. If this is a test database, simple mode might be ok.
May 21, 2008 at 8:19 am
I passed this onto my database administrator and got this response:
It doesn’t always work as when updating elements it needs to be able to rollback the transactions otherwise the data gets corrupt.
what do you think?
May 21, 2008 at 8:28 am
What did you pass along?
What doesn't always work?
May 21, 2008 at 8:29 am
There's NO scenario in SQL server with NO logging. As a matter of course (and as part of the definition of an RDBMS), activity is logged in a way to ensure data consistency.
The same amount of information gets logged whether you're using simple or FULL recovery models. In either case - the transaction log captures the changes being made to records, so that they can be rolled back if the transaction/operation doesn't complete successfully. So you get the same rollback protection either way (since you can't un-commit transactions).
The difference is that in simple mode, once the transaction is committed, the record of the changes in the transaction log is truncated (meaning - the space is marked as free, so the space can be reused). In FULL on the other hand, the record of the changes is maintained until a log backup occurs, allowing for possible "point in time" restores of databases in FULL recovery mode.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 21, 2008 at 8:32 am
the information in cath trimble's response
May 21, 2008 at 8:45 am
Hi Shevonne,
You should refer your DBA to Matt's response in that case, it's a very clear explanation.
Cath
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply