Automatic Log expansion

SQL Server 2000 SP4
On an underpowered machine (mostly an old RAID 5 controller/drives), hosting
Visnetic MailFlow we had a .LDF file that was 10 GB (already dealt with).
When the .LDF file filled, SQL Server did a 10% expansion and the entire
server locked up for several minutes.
Is it normal for the system to refuse connections when expanding the .LDF
file? My guess is yes, because it has nowhere to put log entries (though I
would have hoped it would do the expansion before it filled, thus allowing
continued use).
How about when the .MDF/.NDF is expanded?
Thanks,
JayA 10 gig TLog? Wow :)
Is not it too big mate? You have an issue which is more important than
"expanding" thing... You better take a look at the following documentation:
Optimizing Transaction Log Performance
http://msdn2.microsoft.com/en-us/library/aa178420(SQL.80).aspx
Transaction Logs
http://msdn2.microsoft.com/en-us/library/aa933065(SQL.80).aspx
Managing the Transaction Log (Applies to SQL Server 2005, however, it's
gonna give you precious ideas I believe)
http://msdn2.microsoft.com/en-us/library/ms345382.aspx
--
Ekrem Önsoy
"Jay" <nospan@.nospam.org> wrote in message
news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000 SP4
> On an underpowered machine (mostly an old RAID 5 controller/drives),
> hosting Visnetic MailFlow we had a .LDF file that was 10 GB (already dealt
> with). When the .LDF file filled, SQL Server did a 10% expansion and the
> entire server locked up for several minutes.
> Is it normal for the system to refuse connections when expanding the .LDF
> file? My guess is yes, because it has nowhere to put log entries (though I
> would have hoped it would do the expansion before it filled, thus allowing
> continued use).
> How about when the .MDF/.NDF is expanded?
> Thanks,
> Jay
>|||Sounds like you have Full recovery mode enabled and you are not backing up
your tlog. Common mistake.
You should size tlog appropriately for your workload, data size and backup
plan. It should NEVER grow automatically - you should control this if
needed during periods of minimal activity. Autogrowth should only fire if
something unexpected happens.
"Jay" <nospan@.nospam.org> wrote in message
news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000 SP4
> On an underpowered machine (mostly an old RAID 5 controller/drives),
> hosting Visnetic MailFlow we had a .LDF file that was 10 GB (already dealt
> with). When the .LDF file filled, SQL Server did a 10% expansion and the
> entire server locked up for several minutes.
> Is it normal for the system to refuse connections when expanding the .LDF
> file? My guess is yes, because it has nowhere to put log entries (though I
> would have hoped it would do the expansion before it filled, thus allowing
> continued use).
> How about when the .MDF/.NDF is expanded?
> Thanks,
> Jay
>|||Jay,
I wouldn't expect SQL Server to to refuse connections. But perhaps this was perceived so, because
the app tries to do some modification when it connects. Even the smallest modifications would
require a log records and if the log is full, well...
No, database files are not expended in advance, that is your job. ;-) This is one of the reasons why
we only use autogrow as a sort of backup plan (don't page me while I'm sleeping).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Jay" <nospan@.nospam.org> wrote in message news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
> SQL Server 2000 SP4
> On an underpowered machine (mostly an old RAID 5 controller/drives), hosting Visnetic MailFlow we
> had a .LDF file that was 10 GB (already dealt with). When the .LDF file filled, SQL Server did a
> 10% expansion and the entire server locked up for several minutes.
> Is it normal for the system to refuse connections when expanding the .LDF file? My guess is yes,
> because it has nowhere to put log entries (though I would have hoped it would do the expansion
> before it filled, thus allowing continued use).
> How about when the .MDF/.NDF is expanded?
> Thanks,
> Jay
>|||That makes sense Tibor, thanks.
To everyone else, thanks for your replies, but I already dealt with the
super-sized file before the post, hence the statement "(already dealt
with)". The question was to verify why the server locked up during the
expansion. As to how it happened, I'm being handed production database
servers one-by-one and would have dealt with something as basic as a huge
logfie withing a day, if not hours, of getting a server.
Thanks,
Jay
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:0E2F49FB-37F3-4497-9CB3-F8886A294B98@.microsoft.com...
> Jay,
> I wouldn't expect SQL Server to to refuse connections. But perhaps this
> was perceived so, because the app tries to do some modification when it
> connects. Even the smallest modifications would require a log records and
> if the log is full, well...
> No, database files are not expended in advance, that is your job. ;-) This
> is one of the reasons why we only use autogrow as a sort of backup plan
> (don't page me while I'm sleeping).
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Jay" <nospan@.nospam.org> wrote in message
> news:e7Y3Bb3BIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> SQL Server 2000 SP4
>> On an underpowered machine (mostly an old RAID 5 controller/drives),
>> hosting Visnetic MailFlow we had a .LDF file that was 10 GB (already
>> dealt with). When the .LDF file filled, SQL Server did a 10% expansion
>> and the entire server locked up for several minutes.
>> Is it normal for the system to refuse connections when expanding the .LDF
>> file? My guess is yes, because it has nowhere to put log entries (though
>> I would have hoped it would do the expansion before it filled, thus
>> allowing continued use).
>> How about when the .MDF/.NDF is expanded?
>> Thanks,
>> Jay
>>
>