Is it possible to change the initial size of the transaction log?
-
I have a database, and the log file would appear to have been created with some default settings (it is 512Kb size, which I believe is the minimum SQL 2000 can allocate, and its auto-grow factor is 10%).
When I insert some binary data into an image column, I can see a whole series of "log file auto grow" events in Profiler. That's what I'd expect, but each auto-grow event has a pause of just over 1 second before the next auto-grow. So, given that 10% of 512Kb isn't much, and I therefore can see a lot of auto-grow events in response to one insertion, the overall query execution time is very long.
What does SQL Server have an apparently deliberate delay between auto-grow events? Surely it can grow the log file and initialise it in multiple chunks together, or even not have to wait between chunks? If the insertion causes 10 auto-grow events, that's 10 seconds longer it takes to execute than I'd expect?
The obvious solution (I can see you thinking) is to change the auto-grow factor to a more useful fixed value, such as 10Mb (well, the value is arbitrary, but I think 10Mb will be good for my situation). Doing so clearly will reduce the auto-grow events, and slash query execution time. Yes, I can do that, but what I really want to achieve is to eliminate auto-growth as much as possible by setting a larger initial size for my database transaction log.
Is it possible to change the initial size of a database transaction log? I have tried using an ALTER DATABASE, MODIFY FILE to set a new size. This works, but only temporarily. The problem here is that my database is using the simple recovery model, and has AUTO_SHRINK switched on. Therefore, I find that the database is sometimes automatically shrunk, and when that happens, it returns to the 512Kb size it was initially created with. Very annoying - I want it to return to a more useful size! Is there any way to configure auto shrinking to return the database to a specific size (such as you can with DBCC SHRINKFILE)?
I thought about using sp_detach_db and then sp_attach_single_file_db, but BOL says that doing so will create a new transaction log, which I presume will have the same defaults as the last time (and therefore renders this approach rather pointless). Is there any way to ensure the new transaction log has a more useful initial size?
OK, so that's quite a few in-depth questions I guess
Thanks for reading, and thanks in advance for any help you can offer!Rob
-
I think I'd be tempted to turn Auto_Shrink off and then manage file size a different way. You could set the initial size and file growth with an ALTER TABLE statement and then, if you wanted to, create a scheduled job which uses DBCC SHRINKFILE to manage filesize.
Andrew
-
The reason I didn't consider turning auto shrink off was because I was under the impression that the one setting governs both the database and the log files. Thus, as I wanted to take advantage of auto shrink for the database, I had to suffer it shrinking the log file too? Is that actually the case?
That said, turning auto shrink off and extending the maintenance plan to separately (amd differently) shrink the database file and the log file is an interesting idea, and certainly sounds like it will achieve what I'm after. Thanks for the suggestion!
-
Why are you continually shrinking the database file. It is very expensive for it to grow again, and clearly it needs to be that big for some sort of processing. Leave it big with extra space in it most of the time. It's bad to continually grow and shrink the database. (fragmentation at the os file level) When I initially setup a database I'll make it considerably bigger and let the empty space sit out there, for the benefit of a contiguous file on disk. I set auto-grow to 500 mg on most db's so I don't have small file segments cluttering the disk. Don't make the server do so much work growing and shrinking files when the natural size of the file is what it will always grow to.
Tom
-
Hi Rob,
I'd suggest that you set the transaction log to a proper size and back it up regularly. If, for some reason, it grows beyond what you thought it should be, then you thought wrong.
Under normal circumstances, shrinking a log file that will later need to grow is a waste of system resources. It can also cause file-level fragmentation.
jg
-
hi,
turn auto shrink off as in simple recovery model, the transaction log will not continue to grow as committed transactions are truncated from the log.
Then increase the size of the t.log.
another alternative is to use bulk insert as it is a non-logged operation.
Hope this helps!
TJ
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply