Large log file even when set to Simple

[Justin Biggs]

Greetings,

I'm having an issue with our data warehouse. Every night it is refreshed
with data from our production environment, and then more SSIS jobs are run
that take data from the data warehouse and put it into specific reporting
tables. It's a lot of data moving around, and since our data warehouse can
be rebuilt easily from our existing production environment (which is being
backed up using full logging), the logging method on our DW is set to Simple.

Here's the crazy part: Every night, the log file will grow to over 80GB
which fills up the rest of the space on our log drive. Truncation is being
run every hour on the DW log file, but there's a 2 1/2 hour period (from
2:00AM to 4:30 AM) where the used space on the log drive steadily goes to
100%. The 4:30 AM time period is when the next truncate will run, so it
empties out at that point, but the 2:30 AM and 3:30 AM truncates seem to have
no effect on the size of the log drive.

How is a SIMPLE logged database filling it's log file so quickly? I have
been unable to explain the issue, and it only started showing up about 2
weeks ago. There isn't anything that has changed to the DW jobs that I'm
aware of in that time, but every night it's an issue.

SQL Server version 9.0.3042
Windows Server 2003 x64 w/ SP2
Intel Xeon quad core 3.2 GHz
11 GB memory

[Russell Fields]

Justin,

This suggests that there is a transaction that is starts around 2:00 AM and
stays open until some time before 4:30 AM. The log cannot be truncated to a
point earlier than the oldest still active transaction.

Use DBCC OPENTRAN (databasename) to look for the oldest open transaction in
each database. Once you find the suspect transaction you can run DBCC
INPUTBUFFER (spid) to look at the input line. (If you are on 2005 or 2008,
there are dynamic management views that can give you more details.)

In any case, once you find the problem transaction, then you may be able to
change its behavior.

RLF

Post by Justin Biggs
Greetings,
I'm having an issue with our data warehouse. Every night it is refreshed
with data from our production environment, and then more SSIS jobs are run
that take data from the data warehouse and put it into specific reporting
tables. It's a lot of data moving around, and since our data warehouse can
be rebuilt easily from our existing production environment (which is being
backed up using full logging), the logging method on our DW is set to Simple.
Here's the crazy part: Every night, the log file will grow to over 80GB
which fills up the rest of the space on our log drive. Truncation is being
run every hour on the DW log file, but there's a 2 1/2 hour period (from
2:00AM to 4:30 AM) where the used space on the log drive steadily goes to
100%. The 4:30 AM time period is when the next truncate will run, so it
empties out at that point, but the 2:30 AM and 3:30 AM truncates seem to have
no effect on the size of the log drive.
How is a SIMPLE logged database filling it's log file so quickly? I have
been unable to explain the issue, and it only started showing up about 2
weeks ago. There isn't anything that has changed to the DW jobs that I'm
aware of in that time, but every night it's an issue.
SQL Server version 9.0.3042
Windows Server 2003 x64 w/ SP2
Intel Xeon quad core 3.2 GHz
11 GB memory
--
Best Regards,
Justin