October 26, 2010 at 8:29 am
SQL Server 2000 SP4
Windows Server 2000 SP4
Recovery Model: BULK (set by the vendor)
Full backups: 10:30PM daily
Transactional backups: 3AM to 10:00PM every hour
Most days the transactional backups are less than 5MB, but occassionally the transactional backup is larger than the database.
I'm not sure what is causing the entire database to backup to a transaction log. There are no other maintenance plans outside of the normal integrity check, reorg fragmented indexes, daily backup, hourly transactional backup.
This database server accepts badge swipes from card readers throughout our various buildings. It is also used as a repository for employee schedules and is a target database for an Oracle ETL. However none of these operations occur during this time..
The server statistics right before the transaction log backup were:
99% buffer cache hit ratio
36% CPU (note: this is higher than usual)
-3,249 KB/s on network (this is unusual)
5,982 pg faults/sec (must be mostly soft since 99% of the pages were found in RAM)
0 processor queue
0 disk queue
65 batches/s
40 sessions
5 ms response time
4 active connections
.68 compiles/s
1.6GB Memory Use for SQL Server out of 2.25GB available
30% free disk space on data disk
62% free disk space on log disk
RAID 5
Any ideas would be greatly appreciated for where else to look.
Thanks!
Hawkeye DBA
October 26, 2010 at 8:33 am
A database in Bulk Logged mode probably has some bulk ETL processes in it. The next log backup after the bulk load has to include the data from the bulk load in the backup.
Bulk Logged makes it so the log file doesn't grow as much, but it does nothing to stop the log backup from growing. Otherwise, the log backup couldn't be used for restoring, which would kind of defeat the purpose of having it in the first place.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 26, 2010 at 8:41 am
Thanks for your quick reply!
So, do you think it's "normal" for that backup to be bigger than the database size? I understand the bulk logged model, but there shouldn't be any ETL process that large coming into the database, if that were so it would do it every night when the badge devices download 🙁
The database is 6.5GB and the log backup was 6.6GB. The database is 6.5GB because it has 10years worth of data in it..(that's #2 on my list of items to correct)
I don't understand why the transactional backup would be so large? Am I missing something?
Thoughts?? Thanks again for your speedy response!
October 26, 2010 at 11:01 am
Does that large log backup correspond to the index rebuild?
index rebuilds are minimally logged and, if run for every single index in the DB, changes just about every extent in the DB. All changed extents will be part of the next log backup.
If not, you need to identify what is happening during that log backup interval. That will be the key to the large backups.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2010 at 12:11 pm
You should set your transaction log backups to run more often: every 15 minutes 24x7 is a good standard.
That will let you find out exactly when it is happening, and it will also help to prevent such large log file backups.
As Gail mentioned, it is probably due to index rebuild operations.
October 26, 2010 at 3:16 pm
There is an index rebuild that happens between the last backup and that transaction backup! You called it Gail and Michael! Thank you! Don't know how oh how I missed that!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply