Large Transaction Log Backup after Database Backup

On SQL Server 2000 I have DB maintenance plans to backup databases and
transaction logs. The database is backed up daily at 02:00 hours and
transaction logs backed up every 2 hours (excluding 02:00 hours).The problem
I have is that the first backup of the transaction log after the database
backup is huge. Eg: normal transaction log backups are approx 100mb but the
abnormal one is approx 13gb. The database size is approx 20gb. I can shrink
the transaction log without problem but it expands again after the database
backup.
Does anyone have an explanation for this and is there a resolution?
Thanks
SteveBMy guess is that you either defrag the indexes and/or shrink the database fi
les. Both of these operations are
logged in the transaction log.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SteveB" <SteveB@.discussions.microsoft.com> wrote in message
news:4BA25F24-0289-4808-B351-D26E2CB6C988@.microsoft.com...
> On SQL Server 2000 I have DB maintenance plans to backup databases and
> transaction logs. The database is backed up daily at 02:00 hours and
> transaction logs backed up every 2 hours (excluding 02:00 hours).The probl
em
> I have is that the first backup of the transaction log after the database
> backup is huge. Eg: normal transaction log backups are approx 100mb but th
e
> abnormal one is approx 13gb. The database size is approx 20gb. I can shrin
k
> the transaction log without problem but it expands again after the databas
e
> backup.
> Does anyone have an explanation for this and is there a resolution?
> Thanks
> SteveB|||Your database maintenance plan not only backs up the database, but it can
also reindex all the indexes on the tables or shrink the data file. If you
have either of these last two options ('Reorganize data and index pages' and
'Remove unused space from database files' respectively) checked on the
second page of the maintenance plan wizard, you might want to uncheck them
and create a maintenance plan that runs on a less regular schedule, like
once a week.
Shrinking the database is not useful in a production environment in general,
so you can start with unchecking that if you have it checked and see what
the result is.
Reindexing is something you should do on a regular basis, but as a rough
guide, once a week is enough for most moderately busy systems.
Jacco Schalkwijk
SQL Server MVP
"SteveB" <SteveB@.discussions.microsoft.com> wrote in message
news:4BA25F24-0289-4808-B351-D26E2CB6C988@.microsoft.com...
> On SQL Server 2000 I have DB maintenance plans to backup databases and
> transaction logs. The database is backed up daily at 02:00 hours and
> transaction logs backed up every 2 hours (excluding 02:00 hours).The
> problem
> I have is that the first backup of the transaction log after the database
> backup is huge. Eg: normal transaction log backups are approx 100mb but
> the
> abnormal one is approx 13gb. The database size is approx 20gb. I can
> shrink
> the transaction log without problem but it expands again after the
> database
> backup.
> Does anyone have an explanation for this and is there a resolution?
> Thanks
> SteveB|||In article <OjsO$X7fEHA.3928@.TK2MSFTNGP11.phx.gbl>,
jacco.please.reply@.to.newsgroups.mvps.org.invalid says...
> Your database maintenance plan not only backs up the database, but it can
> also reindex all the indexes on the tables or shrink the data file. If you
> have either of these last two options ('Reorganize data and index pages' a
nd
> 'Remove unused space from database files' respectively) checked on the
> second page of the maintenance plan wizard, you might want to uncheck them
> and create a maintenance plan that runs on a less regular schedule, like
> once a week.
> Shrinking the database is not useful in a production environment in genera
l,
> so you can start with unchecking that if you have it checked and see what
> the result is.
> Reindexing is something you should do on a regular basis, but as a rough
> guide, once a week is enough for most moderately busy systems.
What do you recommend for 24/7 systems? Just INDEXDEFRAG in place of
REINDEX?|||24/7 you have little choice but to use INDEXDEFRAG. DBREINDEX takes out an
exclusive lock on the table it is reindexing and makes it unavailable to
other users. In contrast, INDEXDEFRAG uses short transactions and doesn't
make the table unavailable.
Jacco Schalkwijk
SQL Server MVP
"Brad" <brad@.seesigifthere.com> wrote in message
news:MPG.1b84088229f26a90989695@.news...
> In article <OjsO$X7fEHA.3928@.TK2MSFTNGP11.phx.gbl>,
> jacco.please.reply@.to.newsgroups.mvps.org.invalid says...
> What do you recommend for 24/7 systems? Just INDEXDEFRAG in place of
> REINDEX?|||Hi Guys
Thanks for the responses. I did have similar thoughts. I am doing some
re-indexing, but this takes place at midnight and completes well before the
backup at 02:00 so I would not expect a large log after the database backup.
Will do a trial run on test database with no maintenance activities tonite
and see what results.
"Tibor Karaszi" wrote:

> My guess is that you either defrag the indexes and/or shrink the database
files. Both of these operations are
> logged in the transaction log.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SteveB" <SteveB@.discussions.microsoft.com> wrote in message
> news:4BA25F24-0289-4808-B351-D26E2CB6C988@.microsoft.com...
>
>|||The database and log backups are independent of each other. Backing up the
database doesn't have any effect the transaction log, so the extra
transaction log records that are created at midnight (after the 00:00
transaction log backup, I assume) are still there when you next backup the
transaction log at 04:00.
Jacco Schalkwijk
SQL Server MVP
"SteveB" <SteveB@.discussions.microsoft.com> wrote in message
news:413EC866-0D72-4FA1-8A83-A21DDB4656F3@.microsoft.com...[vbcol=seagreen]
> Hi Guys
> Thanks for the responses. I did have similar thoughts. I am doing some
> re-indexing, but this takes place at midnight and completes well before
> the
> backup at 02:00 so I would not expect a large log after the database
> backup.
> Will do a trial run on test database with no maintenance activities tonite
> and see what results.
> "Tibor Karaszi" wrote:
>|||Jacco
Thanks for response. But my understanding was that the transaction log was
truncated after a full database backup. So I would expect the transaction
log to be truncated at 02:00 hours. Whilst I don't expect the physical size
of the transaction log to change I do expect the size of the transaction log
backup to be minimal for the 04:00 backup.
"Jacco Schalkwijk" wrote:

> The database and log backups are independent of each other. Backing up the
> database doesn't have any effect the transaction log, so the extra
> transaction log records that are created at midnight (after the 00:00
> transaction log backup, I assume) are still there when you next backup the
> transaction log at 04:00.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "SteveB" <SteveB@.discussions.microsoft.com> wrote in message
> news:413EC866-0D72-4FA1-8A83-A21DDB4656F3@.microsoft.com...
>
>|||Steve,
I'm afraid your understanding of the matter was wrong. Try the following
script and see for yourself that the database backup doesn't truncate the
transaction log:
CREATE DATABASE steve
GO
ALTER DATABASE steve SET RECOVERY FULL
GO
USE steve
GO
BACKUP DATABASE steve TO DISK = 'steve.bak'
-- Need an initial backup to 'switch on' FULL RECOVERY
GO
DBCC SQLPERF ( LOGSPACE )
GO
SELECT s0.* INTO test_table
FROM sysobjects s0, sysobjects s1, sysobjects s2--, sysobjects s3
GO
DBCC SQLPERF ( LOGSPACE )
GO
BACKUP DATABASE steve TO DISK = 'steve.bak'
GO
DBCC SQLPERF ( LOGSPACE )
GO
USE master
GO
DROP DATABASE steve
Jacco Schalkwijk
SQL Server MVP
"SteveB" <SteveB@.discussions.microsoft.com> wrote in message
news:BC1602B4-DC3B-4E61-9477-7D61FE8055E7@.microsoft.com...[vbcol=seagreen]
> Jacco
> Thanks for response. But my understanding was that the transaction log was
> truncated after a full database backup. So I would expect the transaction
> log to be truncated at 02:00 hours. Whilst I don't expect the physical
> size
> of the transaction log to change I do expect the size of the transaction
> log
> backup to be minimal for the 04:00 backup.
> "Jacco Schalkwijk" wrote:
>