July 10, 2010 at 9:13 pm
Dear all,
We are in the process of doing data conversion.
Hence, we prepare a lot of update queries in a single .sql script file.
When we run this script, the log file increase dramatically until
it consumed all of the disk space.
During this process, we make our DB recovery model to "simple",
my question is:
1.Why is the log file not reused although the recovery model is set to simple?
2.What should we do for these update statements in order to avoid the same issue encountered again?
Please advice.
Thousand thanks.
July 10, 2010 at 11:17 pm
can you try to divide the transaction into many small transactions
Thanks
John
July 11, 2010 at 8:53 am
Log space cannot be reused until a transaction is committed and a checkpoint run. If you have one large transaction, it prevents reuse of the log file.
Setting to simple, do you take a full backup immediately afterward? Or before? If not, you risk losing a lot of potential data changes.
July 11, 2010 at 9:10 am
Isn't auto commit is a nature of sql server? (correct me if i am wrong)
in my case, my script look like below:
update.....
update....
update....
...
..
...
After the first statement update completed,it is not considered committed transaction?
If it is not, what can do I to avoid the log file from growing?
Please kindly advice.
Thanks a lot.
July 11, 2010 at 9:18 am
yes and no.
If you had
Begin Transaction
......
update x
update y
update x
....
This is one transaction. You can also have Implicit transactions turned on (http://msdn.microsoft.com/en-us/library/ms188317.aspx). If that's the case, then each statement starts a transaction and you need to issue a commit. I'd check on this.
Have you traced this to be sure that there isn't one statement that is generating an inordinate or unexpected volume of changes?
July 11, 2010 at 9:19 am
Or perhaps your log is just not sized correctly?
July 11, 2010 at 7:06 pm
hi,
does it mean that if i rewrite the query as below,the log file will be reused?
BEGIN TRANSACTION
update a...
GO
update b...
Go
COMMIT TRANSACTION
BEGIN TRANSACTION
update c...
GO
update d...
Go
COMMIT TRANSACTION
BEGIN TRANSACTION
update e...
GO
update f...
Go
COMMIT TRANSACTION
My log file is set to 50MB increment (unrestriced growth).
Please advice.
July 11, 2010 at 8:42 pm
Yes, the two updates would be one transaction, and the log could not clear between them, simple mode or not.
You could also set a t-log backup in the middle of the set to clear the log if you don't want to go with simple mode.
begin tran
update a
update b
commit tran
backup log mydb to disk = ...
begin tran
update a
update b
commit tran
I would track log size as you run this, maybe running it manually and checking the sizes so you know roughly what size the log needs to be. I like keeping full mode for this reason. That way if I see log backups (or fulls) way out of whack, I can go check on what happened.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply