how to reuse log file - 3 questions inside.
-
Monday, May 07, 2012 12:33 AM
I am using SQL Server 2008 R2
At present the database log file autogrowth is set to None and the database recovery model set to Simple. The transactions are still working on the database.
Although, the application that access this database has seperate log file, that says the "xxx.log" database log file is full... I knew, this is becasue log file won't allow grow.
I have below 3 questions if someone could help me out please?
1) If I enable the autogrowth on log, lets say upto 1GB, does still allow transactions once it reaches to 1GB limit?
2) Also, I want to reuse the log file once it reaches its max. limit, I believe this may be related to log_reuse_wait_desc column in sys.databases, but how?
3) If I have setup above two options on the database, is there any issue on the database transactions?
The log file is no use to me, because the database is only for test.
All Replies
-
Monday, May 07, 2012 3:11 AM
Hi,
First of all, in SQL Server, you cannot ignore the transaction file - no matter of your database is dev, test or production. Since your test database is in simple recovery mode, i agree that you will not use the transaction log for recovery, but the log file is important for integrity of your transaction. - http://msdn.microsoft.com/en-us/library/ms190925.aspx
That being said, the transaction log in sql server in reused in a circular manner. Log truncation frees space in the log file for reuse by the transaction log. Log truncation is essential to keep the log from filling. Log truncation deletes inactive virtual log files from the logical transaction log of a SQL Server database, freeing space in the logical log for reuse by the Physical transaction log. If a transaction log were never truncated, it would eventually fill all the disk space that is allocated to its physical log files. Under the simple recovery model,log truncation happens after a checkpoint.
There are a number of options to manage the size of the transaction log - http://msdn.microsoft.com/en-us/library/ms365418.aspx
After familiarizing yourself with some of the basics about transaction log from the links above, You will find the 3 answers below more helpful;
1. Depends - If you have 1 very long running transaction that uses up all of the 1 GB space, then your transaction will hang up ! You have to come up with the Max Size based on the transaction load on that database and the expected size of transactions. You can add multiple transaction log files as well. Here are some options to control the growth of the transaction log file - http://msdn.microsoft.com/en-us/library/ms365418.aspx#ControlGrowth , but you have to monitor it!
2. Yes - Its happens automatically ! Follow this link for an MSDN article of transaction log architecture to understand why - http://msdn.microsoft.com/en-us/library/ms179355.aspx
3. What "issue" do you mean ? - If you are asking that after doing above 2 configurations, you will never have to look at the transaction log and everything will run smoothly for ever, then you are mistaken. Please following this link for an article on what maintenance tasks you have to do routinely to manage transaction long - http://www.simple-talk.com/sql/learn-sql-server/managing-transaction-logs-in-sql-server/
Hope this helps ! Good Luck. Remember, you have to care about the transaction log , no matter what recovery model you use and what environment your database is in.
Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com
- Proposed As Answer by Uri DimantMVP, Editor Monday, May 07, 2012 4:56 AM
- Marked As Answer by shahmr Thursday, May 10, 2012 11:46 PM
-
Monday, May 07, 2012 4:59 AMAnswerer
>>>1) If I enable the autogrowth on log, lets say upto 1GB, does still allow transactions once it reaches to 1GB limit?
That means that every time SQL Server needs to space it allocates (creates new virtual logs files within a transaction log) 1GB to the log file. I am nit sure what you meant by " it reaches to 1GB limit"?
>>>2) Also, I want to reuse the log file once it reaches its max. limit, I believe this may be related to log_reuse_wait_desc column in sys.databases, but >>how?
Having set your db to FULL recovery model and perform regular log backups will ensure to reuse VLF ...
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
-
Friday, May 11, 2012 12:10 AM
Thanks Sanil,
So, the solution for me is to increase the initial size to 1GB to the log file, this is max. size on database transaction.
As per information given on those links, I understand that the log files are reused once it reaches to the end of the file.
If I keep autogrowth disabled, does the log file keep reused?
Or, does it decline the transaction once it reach the end of the file size?(transactions will be definitely smaller than 1GB).
-
Friday, May 11, 2012 2:17 AM
Hi,
Even if you keep autogrowth disabled, the transaction log file will get reused. However, the transaction log still has the possibility of getting full because it all depends on the number of simultaneous transactions and their duration. If the transaction log gets full and reuse is not possible due to all transactions still being open, then the calling application will get errors.
For this reason , it is advisable to set autogrowth ON ( Please set this in terms of bytes, not %) and set a Max file size. So assume that your transaction logs are on a disk of 10 GB size, then initial size of 1 GB, autogrowth 100 MB and possibly max size of 5 GB is a good a good rule of thumb. You should also keep monitoring it
Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com
.png)
