|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Thursday, May 17, 2012 11:29 AM
Points: 189,
Visits: 378
|
|
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
|
|
|
|
|
SSChampion
Group: General Forum Members
Last Login: Today @ 12:10 PM
Points: 14,134,
Visits: 8,527
|
|
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.
- 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
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, May 17, 2012 11:29 AM
Points: 189,
Visits: 378
|
|
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!
|
|
|
|
|
SSC-Dedicated
Group: General Forum Members
Last Login: Today @ 5:58 AM
Points: 32,257,
Visits: 24,742
|
|
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
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass
|
|
|
|
|
SSCrazy
Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 2,529,
Visits: 8,844
|
|
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.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, May 17, 2012 11:29 AM
Points: 189,
Visits: 378
|
|
| 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!
|
|
|
|
