MS SQL Server - Problem with transaction log

Author

Problem with transaction log

Ragnar Midtskogen

2006-03-05, 8:24 pm

Hello,

I have problems explaining the behavior of the transaction log.
We have an SQL Server 2000 database that is used mostly for providing data
for a Web site, but there is also some data entry by Web site users during
normal office hours.
The DB is updated nightly between 10 PM and midnight with data from the
client's Alpha4 DB, by using DTS.
A full backup is done before (9 PM) and after (4 AM) the A4 update, then
transaction log backups are run at 7 AM, 1 PM and 6 PM.
The DB file is about 1.8 GB, the transaction log is about 4.7 GB. Whenever I
check the usage for the log it is about 1.5 to 1.8 %.
Each of the full backups are between 800 and 900 MB.
I know how to shrink the log file, but first I would like to understand what
is going on.
The puzzling thing is that the 7 AM log backup is 2.8 GB, while the 1 PM and
6 PM log backups are maybe a few KB.
As far as we know, there should be no significant activity between 4 AM and
7 AM.
Initially we did not do a full backup after the A4 update, but when I saw
the size of the log backup I added the second full backup, expecting the log
backup to get really small, but it did not change anything.

Is there a simple way I can find out what activity there is between the end
of the A4 update and the 4 AM full backup?

Any ideas of why the first log backup is so big?

Any help would be appreciated.

Ragnar

Dan Guzman

2006-03-05, 8:24 pm

> Initially we did not do a full backup after the A4 update, but when I saw
> the size of the log backup I added the second full backup, expecting the
> log backup to get really small, but it did not change anything.

Be aware that a full backup does not truncate the log Your 7 am log backup
reflects all log activity since the 6 pm log backup. You might try backing
up the log at 4 am instead of performing another full backup.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ragnar Midtskogen" < ragnar_ng@newsgroups
.com> wrote in message
news:eSGPICzPGHA.5152@TK2MSFTNGP10.phx.gbl...
> Hello,
>
> I have problems explaining the behavior of the transaction log.
> We have an SQL Server 2000 database that is used mostly for providing data
> for a Web site, but there is also some data entry by Web site users during
> normal office hours.
> The DB is updated nightly between 10 PM and midnight with data from the
> client's Alpha4 DB, by using DTS.
> A full backup is done before (9 PM) and after (4 AM) the A4 update, then
> transaction log backups are run at 7 AM, 1 PM and 6 PM.
> The DB file is about 1.8 GB, the transaction log is about 4.7 GB. Whenever
> I check the usage for the log it is about 1.5 to 1.8 %.
> Each of the full backups are between 800 and 900 MB.
> I know how to shrink the log file, but first I would like to understand
> what is going on.
> The puzzling thing is that the 7 AM log backup is 2.8 GB, while the 1 PM
> and 6 PM log backups are maybe a few KB.
> As far as we know, there should be no significant activity between 4 AM
> and 7 AM.
> Initially we did not do a full backup after the A4 update, but when I saw
> the size of the log backup I added the second full backup, expecting the
> log backup to get really small, but it did not change anything.
>
> Is there a simple way I can find out what activity there is between the
> end of the A4 update and the 4 AM full backup?
>
> Any ideas of why the first log backup is so big?
>
> Any help would be appreciated.
>
> Ragnar
>
>

Ragnar Midtskogen

2006-03-05, 8:24 pm

¨Thank you Dan,

You are correct. I guess I did not read the BOL carefully enough.
I assumed that a full backup would truncate the log, since to restore you
start with restoring a full backup, then restore all the log backups since
the full.
That, to me, implied that a full backup would truncate the log.

I guess the reason it does not, is to allow for a rollback to a state before
the full backup?

In our case, the content of the transaction log from the duration of the DTS
operation is useless, we cannot recover to the point of failure, then
restart the DTS operation at that point. If something goes wrong during the
DTS operation we would restore the full backup done before the start of the
operation and rerun the DTS operation.

Once the DTS operation is complete there will be no more changes to the
database until the users start logging in, during normal business hours.
In other words, the only time we need backups of the transaction log is
during the day when users make changes.

What if I ran a TSQL script right after the DTS operation completed, to
switch to simple recovery mode and truncate the log, then switch back to
full recovery mode?

Ragnar

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:OoddNXzPGHA.5152@TK2MSFTNGP10.phx.gbl...
>
> Be aware that a full backup does not truncate the log Your 7 am log
> backup reflects all log activity since the 6 pm log backup. You might try
> backing up the log at 4 am instead of performing another full backup.
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>
> "Ragnar Midtskogen" < ragnar_ng@newsgroups
.com> wrote in message
> news:eSGPICzPGHA.5152@TK2MSFTNGP10.phx.gbl...
>
>

Dan Guzman

2006-03-06, 7:15 pm

> What if I ran a TSQL script right after the DTS operation completed, to
> switch to simple recovery mode and truncate the log, then switch back to
> full recovery mode?

You don't need to switch to simple recovery but you will need to perform a
full backup so that you can establish the starting point for full recovery.
You might instead consider the following sequence so that you can perform
point-in-time recovery from the full backup to the next nightly DTS run.

1) log backup
2) switch to simple recovery
3) run DTS
4) switch to full recovery
5) full backup

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ragnar Midtskogen" < ragnar_ng@newsgroups
.com> wrote in message
news:uMkQvxJQGHA.2816@TK2MSFTNGP15.phx.gbl...
> ¨Thank you Dan,
>
> You are correct. I guess I did not read the BOL carefully enough.
> I assumed that a full backup would truncate the log, since to restore you
> start with restoring a full backup, then restore all the log backups since
> the full.
> That, to me, implied that a full backup would truncate the log.
>
> I guess the reason it does not, is to allow for a rollback to a state
> before the full backup?
>
> In our case, the content of the transaction log from the duration of the
> DTS operation is useless, we cannot recover to the point of failure, then
> restart the DTS operation at that point. If something goes wrong during
> the DTS operation we would restore the full backup done before the start
> of the operation and rerun the DTS operation.
>
> Once the DTS operation is complete there will be no more changes to the
> database until the users start logging in, during normal business hours.
> In other words, the only time we need backups of the transaction log is
> during the day when users make changes.
>
> What if I ran a TSQL script right after the DTS operation completed, to
> switch to simple recovery mode and truncate the log, then switch back to
> full recovery mode?
>
> Ragnar
>
> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
> news:OoddNXzPGHA.5152@TK2MSFTNGP10.phx.gbl...
>
>

Ragnar Midtskogen

2006-03-06, 7:16 pm

Thank you Dan,

That sounds like the simplest way to do it.

Ragnar

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:%23ptpgTSQGHA.1728@TK2MSFTNGP11.phx.gbl...
>
> You don't need to switch to simple recovery but you will need to perform a
> full backup so that you can establish the starting point for full
> recovery. You might instead consider the following sequence so that you
> can perform point-in-time recovery from the full backup to the next
> nightly DTS run.
>
> 1) log backup
> 2) switch to simple recovery
> 3) run DTS
> 4) switch to full recovery
> 5) full backup
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
>

Sponsored Links