August 18, 2008 at 11:49 am
The .ldf file is set to grow @ 10% with unrestricted file growth.
It has not grown since 12/28/2007 when an upgrade from SQL 2000 to SQL 2005 was performed. The current size is approximately 45.6 gig.
The developer states that there is no need to worry that the file is not growing.
There is a transaction log backup plan.
Is he right?
August 18, 2008 at 11:51 am
Yes the developer is right.
A 45.6GB log file shouldn't need to grow if you are doing regular log file backups. I'd even venture to say that you could reduce the size of the log file, depending on the activity between log backups.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 18, 2008 at 11:57 am
Thanks Jack.
Would you perform a DBCC SHRINKFILE to reduce the size of the .ldf?
August 18, 2008 at 12:03 pm
You can use DBCC SHRINKFILE or make the changes from the database properties using SSMS. SSMS will then use DBCC SHRINKFILE to make the changes.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 18, 2008 at 12:19 pm
You first should attempt to determine what size your TX Log should be. The typically recommended starting point is 20% of the database size. It may be you need a 46GB TX Log, but then again it may not. You do not want to shrink it too far because then there will be too much autogrowth occurring.
I'd also limit the size so that you always have some space left on your drive for maintenance operations.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 21, 2008 at 12:06 pm
The T-Log is a strange and somewhat intractable thing. The log file location and property settings can have a signifcant impact on system performance. If your initial size is small (the default is 1mg) and your growth is small then you can end up with a very large, very fragmented file. The result is that every database operation is slowed down while the the operation is first written to the log, then to the database and then updated in the log. (This happens even in Simple Recovery Model.)
You may find that after running a T-log backup and then a shrink file that the log file size is almost unaffected. I don't understand the algorithm that causes this but you can use the undocumented "DBCC LogInfo(' ') to get a view of the actual log file structure and use. The command will return one row per Virtual Log File (VLF). The Status column is the key. If the value is 0 the VLF is not in use, if it is 2 then the VLF is in use. Shrink can only free up unused VLFs at the end of the file. So you may see dozens (hundreds) of free VLFs but if the last one in the chain has status = 2 you won't get any of the space back.
You can mess around with a bunch of inocuous transactions to get the log to wrap, then take a Log Backup. After a couple of cycles you should find the file can shrink, and perhaps a lot.
This is where the thinking comes in. You need to reset the Log file initial size and growth to 'reasonable' values and there is no good standard for 'reasonable'. If the database often has very large logged transactions then you may need a very large log file. If you only backup the log file once or twice a day you will need a large file. If you backup the log hourly or more often then you may be able to use a smaller file.
Given disk sizes, I tend to start with an initial log size of at least 1GB and growth of 100MG. Then monitor the usage for a week or so and see if the Log file does auto grow. If so make the intial size still larger (say 2GB). A t-log growth is an expensive operation that you don't want to happen very often.
There is a lot more to say about T-logs. There have been many articles about them and you should probably lookin to some of them.
August 21, 2008 at 4:41 pm
Ray Herring (8/21/2008)
and your growth is small then you can end up with a very large, very fragmented file.
shrinking and growing files can cause fragmentation at the file level too
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply