March 21, 2014 at 10:33 am
Hello,
As I am monitoring my sql servers, I noticed something in one server. In the error log, I see that one databases has more than 1000 virtual log files which is excessive.
The database is in simple recovery model. Why would this error be in the errorlog if the recovery model is simple?
Thank you.
Tony
Things will work out. Get back up, change some parameters and recode.
March 21, 2014 at 11:26 am
It is my understanding that your VLFS have gotten so high because of potential autogrowth in your transaction log(s) over time. Depending on the size of your database, 1000 VLFs may not be a huge issue (but if you've recently started noticing things taking a longer time to run than normal, you may want to look at reducing them (as high VLFS may impact insert/update/and delete operations).
Question: do you have autogrowth/autoshrink turned on?
Check out this article, and feel free to post back for clarification if any is needed
http://adventuresinsql.com/2009/12/a-busyaccidental-dbas-guide-to-managing-vlfs/
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 24, 2014 at 12:43 am
What is the size of Data & Log file?
---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
March 25, 2014 at 6:46 am
take a look at Ezequiel's blog
http://blogs.msdn.com/b/blogdoezequiel/archive/2011/05/24/sql-swiss-army-knife-9-vlfs-revisited.aspx
He's created an excellent script for dealing with large volumes of VLF's...
March 25, 2014 at 10:08 am
Hello,
Autoshrink is set to false.
However, the files autogrowth is turned on.
Thanks.
Things will work out. Get back up, change some parameters and recode.
March 25, 2014 at 2:12 pm
WebTechie38 (3/25/2014)
Hello,Autoshrink is set to false.
However, the files autogrowth is turned on.
Thanks.
What are the autogrowth settings? Like, 10 MB, 10%, etc?
March 25, 2014 at 2:16 pm
WebTechie38 (3/21/2014)
Hello,As I am monitoring my sql servers, I noticed something in one server. In the error log, I see that one databases has more than 1000 virtual log files which is excessive.
The database is in simple recovery model. Why would this error be in the errorlog if the recovery model is simple?
Thank you.
Tony
What are the settings on your log files regarding auto-growth? If you have a 1MB growth it may be causing this. High VLF counts are not healthy for databases where replication is involved.
Please consider raising your growth to 1000MB (not an even multiple of 1MB such as 1024 - known SQL 2008 bug).
Please run DBCC SHRINKFILE on your TLOG files to bring them down to a reasonable size.
DBCC LOGINFO will return a result set with 1 row per VLF.
Thanks
John.
March 26, 2014 at 7:33 am
I found this myself on a couple of sqlservers.....
to clean up the issue I backed up the trans log, shrunk the transaction log down to bare minimum then resized it to what it was. This got rid of 90% of the fragmentation.
This happens from time to time when an application creates a new database and sets the growth to 1% or 1mb and grows a lot before I notice it happened and fix the auto growth.
March 26, 2014 at 7:56 am
The reason I proposed my question is why am I getting this in the errorlog when the database is in recovery mode is simple.
This may be a stupid question to some, but, is the log file still being used even with the recovery model being simple?
Even if the log file is set to autogrowth, if the database is simple, then I thought the database doesn't use the log file.
Thanks.
Things will work out. Get back up, change some parameters and recode.
March 26, 2014 at 8:02 am
Yes, the logfile is still being used, it just truncates when a database event occurs. What are your autogrowth settings? If they are 1 MB, then by the time an event occurs and truncates the log, it could have a large amount of VLF's.
March 26, 2014 at 8:07 am
Wrong. The transaction log is critical to the database.
When you are in SIMPLE mode as transactions enter the database they are in the trans log FIRST... once they are committed to the database they are flushed out of the transaction log. So, if you have a MASSIVE amount of inserts going on within the database... say 1 million inserts before you have a commit they will be in the transaction log until the commit happens. The reason for this is for database consistency purposes... say, half way into this 'transaction' you hit the cancel query button.... SQL Server will use the transaction log to undo the insert statements it has already processed. Same thing if SQL Server is shutdown or the server crashes during when activity was going on... it uses the Transaction logs to undo what was not committed to the database.
March 26, 2014 at 8:10 am
JoshDBGuy (3/26/2014)
Yes, the logfile is still being used, it just truncates when a database event occurs. What are your autogrowth settings? If they are 1 MB, then by the time an event occurs and truncates the log, it could have a large amount of VLF's.
I will NEVER understand why Microsoft ships SQL Server with such tiny sized TEMPDB and auto growth sizes. They recommend NOT doing these type settings but the product comes setup that way from the install... A lot of vendors are no better with 1% growth or 1MB growth and auto shrink on as well though.
March 26, 2014 at 8:13 am
WebTechie38 (3/26/2014)
The reason I proposed my question is why am I getting this in the errorlog when the database is in recovery mode is simple.
The message has nothing to do with the recovery model, you'll get it any time the log has lots of VLFs regardless of what the recovery model is. That will occur if the log has grown in small chunks from small to large.
This may be a stupid question to some, but, is the log file still being used even with the recovery model being simple?
Sure. If it wasn't used, you'd never be able to roll back any command and any failure of a data modification would send the database suspect, requiring a restore from backup.
The only difference between the recovery models is that simple and bulk-logged allow minimally logged operations (bulk insert, index rebuilds and a few more) and that in simple recovery VLFs which contain older log records are automatically marked as reusable when a checkpoint occurs whereas in full and bulk-logged recovery models only a log backup marks portions of the log file as reusable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 26, 2014 at 8:44 am
WebTechie38 (3/26/2014)
The reason I proposed my question is why am I getting this in the errorlog when the database is in recovery mode is simple.This may be a stupid question to some, but, is the log file still being used even with the recovery model being simple?
Even if the log file is set to autogrowth, if the database is simple, then I thought the database doesn't use the log file.
Thanks.
No - even in simple mode, the TLOG is used. For example, if you have a transaction (explicit or implicit), the changes are logged until a commit or rollback, and which point they are no longer kept. Therefore, you need to, as I said earlier, allocate some space to the TLOG and set the auto-growth to something like 1000MB. Then a 4GB log would be ~4 VLF and not 900+ VLF.
Recovery full means that the log entries are removable only after a TLOG backup, and is used to enable point-in-time restores using the TLOG backups in conjunction with the last full backup.
This is why the transaction log is required for both modes.
Thanks
John.
March 26, 2014 at 1:18 pm
This is an incredible day!!
I want to thank all of you for your responses. I am in constant learn mode with SQL Server. From asking one question, I have learnt so much.
You guys explained so much...Wow. Thank you.
1) I now understand more of the transaction logs and their value regardless of the recovery model.
2) This particular database is a highly transactional database. The vendor has said that it needs to have a simple recovery model.
3) Since, it was in simple, I didn't understand why I was getting errors on the transaction log.
4) The Database file is 300 GB with with autogrowth of 100 MB.
5) The log file is 9 GB with autogrowth of 10 MB.
Based on your answers, I have a much better understanding of what is going on.
Thanks again for sharing your knowledge.
Tony
Things will work out. Get back up, change some parameters and recode.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply