Transaction log backup size exceeds full backup size

Question

Transaction log backup size exceeds full backup size

Ryan007

SSCrazy Eights

Points: 8275
More actions
November 9, 2010 at 1:06 am

#226062

Hi,
In one of our production server backup of transaction log file exceeds the backup of full backup size. The backups are taken in the below order
Full backup- Every night 12.00AM
Diff- Every day two times between 6.00AM to 8PM
Tranlog vackup- every two hr. starting from 12.30 AM
Full back up size - 11GB
Tranlog backup size - 33 GB (only which is taken at 12.30, others having small size)
Full backup runs till 12.45.
Is it because of the full backup runs during that time?
Ryan
//All our dreams can come true, if we have the courage to pursue them//

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Unlikely to be the result of the full backup. My guess, between 12.30 and the log backup before it you're doing something like an index rebuild over all indexes.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ryan007 SSCrazy Eights Points: 8275 More actions · Answer 2

We have one job for rebuilding indexes which runs at 2.00 AM every sunday.

Ryan
//All our dreams can come true, if we have the courage to pursue them//

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

What jobs run between that 12:30 log backup and the previous log backup?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ryan007 SSCrazy Eights Points: 8275 More actions · Answer 4

What jobs run between that 12:30 log backup and the previous log backup?

Only full backup runs at 12.00 AM between this time.

Ryan
//All our dreams can come true, if we have the courage to pursue them//

Ryan007 SSCrazy Eights Points: 8275 More actions · Answer 5

For the same database log size is 55 GB and data file 15 GB though transaction log backup is taken every 2 hour. Can you tell me why this is happening?

Ryan
//All our dreams can come true, if we have the courage to pursue them//

steveb. SSC-Forever Points: 46733 More actions · Answer 6

Is there any other large amount of activity on the database between those times like a SSIS Data load?

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 7

Sumanta Roy (11/9/2010)
What jobs run between that 12:30 log backup and the previous log backup?
Only full backup runs at 12.00 AM between this time.

Sumanta

Without any modification the t-log backup will not went huge.run a server side trace to find what transaction going on that time.

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 8

Sumanta Roy (11/9/2010)
Hi,
In one of our production server backup of transaction log file exceeds the backup of full backup size. The backups are taken in the below order
Full backup- Every night 12.00AM
Diff- Every day two times between 6.00AM to 8PM
Tranlog vackup- every two hr. starting from 12.30 AM
Full back up size - 11GB
Tranlog backup size - 33 GB (only which is taken at 12.30, others having small size)
Full backup runs till 12.45.
Is it because of the full backup runs during that time?

My guess is the t-log file size is huge thats why you got 33 GB in starting time only.after that there is no huge modification.

After 12:30 am, what is t-log backup size ?

Can you post the dbcc sqlperf (logspace).

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/