I have a database that is 400gb in size and is in full recovery mode. We
perform a full backup nightly and apply this backup to the reporting server.
Transaction log backups are performed hourly. I have noticed at times that
it appears that after the full backup the next hourly transaction log is
huge, in 60gb size.

Last night we performed a full backup that finished at 3:04 am. There is
very little to no activity at night. However the transaction log backup at
4:00am on has been failing because of not enough space on disk. The disk has
68gb free, should be more than enought to backup what was changed during the
full backup.

This has happened on other 2005 servers also, has something changed in the
backup process for SQL. SQL2000 didn't have this issue, that I ever found.

Do you have any maintenance plans that kick in after the backups complete?
Rebuliding indexes, for example, will result in v. large transaction log
usage.

"Thom" <Thom (AT) discussions (DOT) microsoft.com> wrote



__________ Information from ESET NOD32 Antivirus, version of virus signature database 4658 (20091203) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

Thom,

Firstly the full backup in SQL2005 does not clear out the transaction log.
So if you are writing a lot of data to the log all day and didn't run the
log backup because of the full backup running it will get pretty full. Now
with a log that large you may well be suffering from many VLF's in the log.
This would cause some problems.

Run DBCC LOGINFO on the database and it will report how many VLF's that you
have.

HTH

Chris
"Thom" <Thom (AT) discussions (DOT) microsoft.com> wrote

Log backups are not from the last full backup but from the last Log backup.
This is assuming you have already once run a full backup before. Subsequent
full backups do not break the log chain and each log backup must get all
changes from the last log backup, not the full. But my guess is you have a
maintenance operation occurring such as rebuilding an index that is eating
up the log files.

--

Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Thom" <Thom (AT) discussions (DOT) microsoft.com> wrote

I'll check other maintenance activities. but I hav a question as to the way
log backups work in 2000 vs 2005. In 2000 a log backup was from the last
full, differential or log backup correct? So this has changed in 2005.

"Andrew J. Kelly" wrote:

Thom,

No, this has always been the case that a log backup is from the last log
backup. Of course, for a database that has just been switched to FULL
recovery model, the full backup is needed first in order to initiate the
starting point for the first log backup.

Also, if a log is 'truncated' or you switch between FULL and SIMPLE modes,
another full backup is needed to reestablish a starting point for the logs
that will follow.

This is actually a very smart design, since it allows log backups to be used
to restore past a damaged full backup. For example, I have full backups on
Monday and Wednesday nights, with hourly log backups. I have a Thursday
disaster and try to restore the Wednesday backup, but it is unusable. I can
still restore the Monday backup and all the log backups from then until
Thursday to get back to my restore point.

FWIW,
RLF


"Thom" <Thom (AT) discussions (DOT) microsoft.com> wrote