TLog Backup Same size as Diff Backup

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

TLog Backup Same size as Diff Backup

Author

Topic

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-09 : 14:12:52

I'm using Full, Diff and TLog Backups.
The full backup runs lets say at 9:00pm,
TLog runs at 9:00, 9:15, 9:30, 9:45,10:00,10:15
The Diff runs at 10:00pm,
and the TLog runs at 10:15pm.

The full backup generates a 40MB file, The Diff a 900KB File,
and the TLog that ran at 10:15 a 40MB file.

It seems to me that the TLog is not truncating when the full or diff runs. There is no way that much that many transactions happened in 15 minutes.

How do I verify and fix this.

Edited by - ValterBorges on 10/09/2002 14:20:00

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-09 : 14:30:46

From Books Online:

quote:
LOG

Specifies a backup of the transaction log only. The log is backed up from the last successfully executed LOG backup to the current end of the log. Once the log is backed up, the space may be truncated when no longer required by replication or active transactions.

Note: If backing up the log does not appear to truncate most of the log, an old open transaction may exist in the log. Log space can be monitored with DBCC SQLPERF (LOGSPACE). For more information, see Transaction Log Backups.

After the first log backup, subsequent log backups should only contain the log entries.

Also check which recovery model you're using (full, simple, or bulk), it affects how much info is kept in the log.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-10-09 : 15:38:48

I'm using Full recovery model.

Here is what I'm running to make the backups happen

FULL:
BACKUP DATABASE [MyDB] TO DISK = @DiskLocation WITH INIT , NAME = N'MyDB FullBackup', SKIP , STATS = 10, NOFORMAT

DIFF:
BACKUP DATABASE [MyDB] TO DISK = @DiskLocation WITH INIT, DIFFERENTIAL, NAME = N'MyDB DiffBackup', SKIP , STATS = 10, NOFORMAT

LOG:
BACKUP LOG [MyDB] TO DISK = @DiskLocation WITH INIT, NAME = N'MyDB Log Backup', SKIP , STATS = 10, NOFORMAT

I ran DBCC SQLPERF (LOGSPACE) and it tells me that its 2560 MB and usage is 2.95 %.

Can this be right? The full backup of the database is 40MB.
How can I fix this?

Edited by - ValterBorges on 10/09/2002 15:52:53

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources