Re: Large Transaction Log Backup after Database Backup
From: Jacco Schalkwijk (jacco.please.reply_at_to.newsgroups.mvps.org.invalid)
Date: 08/13/04
- Next message: Stuart: "Monitoring DTC from PerfMon"
- Previous message: Giacomo: "Re: vacation backup"
- In reply to: SteveB: "Re: Large Transaction Log Backup after Database Backup"
- Next in thread: Jacco Schalkwijk: "Re: Large Transaction Log Backup after Database Backup"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 13 Aug 2004 10:48:00 +0100
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... > 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... >> > 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... >> >> > 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 >> >> >> >> >> >> >> >> >>
- Next message: Stuart: "Monitoring DTC from PerfMon"
- Previous message: Giacomo: "Re: vacation backup"
- In reply to: SteveB: "Re: Large Transaction Log Backup after Database Backup"
- Next in thread: Jacco Schalkwijk: "Re: Large Transaction Log Backup after Database Backup"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
