Differential Backup Size and Transaction Log Backup Size !

Hello All,
Sometime back we had scheduled Differential database backups on our OLTP
system. The backup is scheduled to run once at 6AM, once at 12Noon and
7PM. This backup is apart from the half hourly transaction log backup
and the daily full database backup.
Normally, for a day, the Transaction log backup does not exceed 300MB and
the 6AM Diff backup does not exceed 400MB, the 12Noon Diff backup does not
exceed 500MB and the 7PM Diff backup does not exceed 600MB.
However, sometimes, I notice that the 6AM Diff backup is around 1100MB,
the 12Noon Diff backup around 1300MB and the 7PM Diff backup around 1400MB
but the Transaction log backup still 300MB.
I would expect the Transaction Log backup also to show around 1500MB.
Does anyone know why the differential database backup is so large when
compared to the Transaction Log.
Thanks,
RgnOne explanation would be that you move to bulk logged recovery mode, and
bcp'd in a lot of data ( which is minimally logged).
Another explanation would be that you dropped/re-created a bunch of indexes,
only the drop/create statement would be in the log, but the entire index
would be copied in the differential backup.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"rgn" <anonymous@.discussions.microsoft.com> wrote in message
news:9B20C782-042B-4D0C-857C-F68787C5C45C@.microsoft.com...
> Hello All,
> Sometime back we had scheduled Differential database backups on our OLTP
> system. The backup is scheduled to run once at 6AM, once at 12Noon and
> 7PM. This backup is apart from the half hourly transaction log backup
> and the daily full database backup.
> Normally, for a day, the Transaction log backup does not exceed 300MB and
> the 6AM Diff backup does not exceed 400MB, the 12Noon Diff backup does not
> exceed 500MB and the 7PM Diff backup does not exceed 600MB.
> However, sometimes, I notice that the 6AM Diff backup is around 1100MB,
> the 12Noon Diff backup around 1300MB and the 7PM Diff backup around 1400MB
> but the Transaction log backup still 300MB.
> I would expect the Transaction Log backup also to show around 1500MB.
> Does anyone know why the differential database backup is so large when
> compared to the Transaction Log.
> Thanks,
> Rgn|||Wayne
You are very true in saying that the bulk-logged operation generates a large transaction log backup and/or differential DB backup. However, my question is that, if the differential Db backup is 1.5GB then even the transaction log backup shoul
be of simillar size, right
In my case, the transaction log seems to be only 300MB where as the differential db backup is 1.4GB
rgn