Log Expansion Question

Question

Log Expansion Question

Ariadne

SSCrazy

Points: 2044
More actions
January 12, 2005 at 8:50 am

#87779

Got a debate going on here. Need a mediator...
Here's the situation:
-Log File is set to auto grow at 10%, unrestricted size
-There is plenty of harddrive space for the log to expand in (i.e. 100 mb log file on a 12 GB partition).
-One very long transaction is running and fills up the 100 mb log..
DBA 1 says that the transaction log will not expand because:
-Log File becomes so full it can't expand because there's no room for another transaction to allow it to expand
DBA 2 says this is not true. The Log file will expand ad nauseum until it runs out of Harddrive space.
Which one is correct? and why? Thanks bunches...

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

DBA 2 because of the setting for unrestricted growth. Only once all HD space is exausted will the Log file fail becuase the is nothing it can do at that point. (Hello rollback)

Ariadne SSCrazy Points: 2044 More actions · Answer 2

Thanks Antares,

I'm DBA2, and I used that argument. DBA1 still didn't believe me. He says that once the log file is full, it's full, and you have to expand the file manually. Anyone have handy a nice test insert that'll continuously insert into a table as one transaction and cause a log file to fill up so he can run this on his test server?

sa24 SSCrazy Eights Points: 8027 More actions · Answer 3

you can have something as simple as below.

create table test (column1 char(100))

declare @i int

set @i = 0

while @i < 1000000

begin

insert into test values('a')

set @i = @i + 1

end

drop table test

Farrell Keough SSCoach Points: 17414 More actions · Answer 4

Change 'WHILE @Counter <= 10 ' + CHAR(13) +

to read as

'WHILE @Counter >= 1 ' + CHAR(13) +

and it should run forever.... until you "Kill" it.

CREATE TABLE #Fill( BogusText varchar(100))

DECLARE @SQL nvarchar(2000)

SELECT @SQL =

'DECLARE @Counter int ' + CHAR(13) +

'SELECT @Counter = 1 ' + CHAR(13) +

'WHILE @Counter <= 10 ' + CHAR(13) +

'BEGIN ' + CHAR(13) +

' BEGIN TRANSACTION "Bogus" ' + CHAR(13) +

' INSERT INTO #Fill SELECT ' + CHAR(39) + 'What a bogus thing to have to do...' + CHAR(39) + CHAR(13) +

' SELECT @Counter = @Counter + 1 ' + CHAR(13) +

' COMMIT TRANSACTION "Bogus" ' + CHAR(13) +

'END '

EXEC( @SQL)

SELECT * FROM #Fill

DROP TABLE #Fill

I wasn't born stupid - I had to study.

Mike Metcalf SSCrazy Eights Points: 8763 More actions · Answer 5

in a former company we occasionally encountered problems where the transaction log didn't expand automatically even though it was set to. This is the only time that i've ever had to grown them manually.

SQLBill SSC Guru Points: 51440 More actions · Answer 6

I would be in SERIOUS trouble if the autogrow didn't work and I had to expand my logs manually. I have my logs set to grow by 2000MB. And they GROW. My transaction log grows once a week. I NEVER manually expand it. There have been days my log has grown twice in a day.

Yes, it is possible to have a transaction or series of transactions happen so fast that the log has trouble growing. But that is due to it filling up faster than it can expand. The solution to that is to allow it to grow larger, but 10% will take care of that.

-SQLBill