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
>>
>

Auto Generating Phantom drives

I'm running SQL on a clustering configuration. I recently started noticing
these phantom drives appearing under my computer. They are all relatively
close in size and it appears to only create one phantom drive each Sunday. I
currently have the SQL Server backing up it's files to the real Local drive
and from there it goes to tape. I do not only back up on Sunday I backup the
same way each day. The phantom drives do not show up under Disk Manager only
under My Computer.
Anyone every experience these phantom drives appearing.
Peter
Disks or drives? They are different.
Disks could be from your SAN solution, managing snapshot copies and other
head-in components.
Drives are one of two possible scenarios:
Have you been presenting replacement LUNs to your cluster? If so, you could
be seeing remnants of the old definitions. You might want to rescan the
disks and then reboot the servers to remove the old ones.
The more likely scenario is the use of the OTM (Open Transaction Manager) in
conjunction with the Veritas NetBackup or BackupExec. OTM can create
temporary shares that look like drives that it uses for snapshot copies
while doing backup acceleration.
They go away once the tape archive has completed successfully. If they
remain, then you probably had a backup failure and Veritas failed to remove
the share.
Sincerely,
Anthony Thomas

"Peter" <peter@.discussions.microsoft.com> wrote in message
news:373FDF72-009C-44A6-AF0D-E09B4D0710BE@.microsoft.com...
> I'm running SQL on a clustering configuration. I recently started noticing
> these phantom drives appearing under my computer. They are all relatively
> close in size and it appears to only create one phantom drive each Sunday.
I
> currently have the SQL Server backing up it's files to the real Local
drive
> and from there it goes to tape. I do not only back up on Sunday I backup
the
> same way each day. The phantom drives do not show up under Disk Manager
only
> under My Computer.
> Anyone every experience these phantom drives appearing.
> --
> Peter