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 |
|
frank.svs
Constraint Violating Yak Guru
368 Posts |
 Posted - 2010-01-23 : 22:58:34
|
| I have set the database to SIMPLE recovery model and i have restricted to my logfile 6MB.my table has 1 gb data. i purposefully restricted the logfile to6MB to checkbehavior of SIMPLE recoery model.msdn saysAutomatically reclaims log space to keep space requirements small, essentially eliminating the need to manage the transaction log spaceI thought, whenever i say SIMPLE recovery model, when log file reaches 70-80% , then automatically overwrittes the LOG file but it is not happended and result in LOG FULL error.I have run the below statement DELETE FROM <TNAME> -- Conatins 1 GB data and is not able to store the before image in .LDF file which is of 6MB restricted growth.I feel, irrrespective of recovery model , until the txn is completed, the LDF file keeps increasing and once completed only then it can be re-used by other transaction. Am i right????????? I asked this question bcz i felt when 70% of logfile reaches , then .LDF will be overwritten. but it never happened. The ldf is growing and growing till the txn completes.Also, i understand why the sql server is trying to expand the log file. Meaning, the user my rollback the txn at any time and that could be the reason why logfile is increasing and does'nt get overwritten.Correct me if am wrong.My question is if am running a huge transaction which kills my entire drive space and eventually end up in logfull ERROR i thought SIMPLE recovery model will solve my problem.How to deal with such scenario's??? How can we avoid such kind of errrors in future..(i.e running a ttxn for 6 - 8 hrs and ending upin logfull errors).I feel one solution is keeping the transactions short. Other solution is shrinking the logfile/database. What can be other ideal solution for this so that whenever i can calculate this txn may take this much amount space and move the .LDF where amount of disk space is available...Can i use this sp_spaceused 'tablename', get the data size and estimate the logsize ????? Is that a solution to fix the log full error problems?????????? Thanks in Advance. |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-24 : 05:40:15
|
| LDF / TLog holds the transaction - so that it can be rolled back if necessary. When the transaction is "finished" that LDF space is available for reuse.Yes a monster query will extend the LDF (and you may run out of disk space). However, in the real world there are usually lots of users accessing the database and these things even out - someone else's transaction completes, and that releases space from the TLog.Monster transactions require careful design whether you have SIMPLE or FULL recovery model.If I do a DELETE FROM MyBigTable it will create X bytes in the TLog. If that extends my TLog (and this is a one-off event, rather than a daily-delete) then I have created an unnecessary housekeeping job. Increasing the frequency of TLog backups (in FULL Recovery model) won't help - the space used by the transcation won't be marked as "available" until it has completed (AND been backed up).So, for such "monster deletes", we use a loop, delete in small-ish batches, force a TLog backup (because we are using FULL Recovery model), use a WAITFOR to, briefly, allow other tasks to use / lock the DB tables, etc. |
 |
|
|
|
|
|
|
|
SIMPLE RECOVERY MODEL BEHAVIOUR