Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database backup


Database backup

Author
Message
Ryan007
Ryan007
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1616 Visits: 1595
Hi,
I have some issue regarding database backup.
I am taking backup of full backup of my database every 2 hour.
Differential backup every 1 hour.
Transaction log backup every 15 min.
If I dont do anything in my database, then backup file should remain condtant each time. But each time transaction log backup is growing. In the maintenance plan I have mentioned as the transaction log file as overwrite. Can anyone tell me what is the problem?

Ryan
//All our dreams can come true, if we have the courage to pursue them//
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47295 Visits: 44392
Hang on.

You're backing the transaction log up every 15 min, writing to the same file and specifying overwrite? Is that correct?

Why full backups ever 2 hours? That's awfully frequent, especially with a diff every hour.


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


Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36176 Visits: 18751
You transaction log backup should not be growing if you are not changing data. If it is, I can only assume that transactions are being added to the database, inserts, updates, deletes, something.

How are you specifying the backups?

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ryan007
Ryan007
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1616 Visits: 1595
This is testing purpose in testing region. But actually it wiil be one full back up per day, differential back every 4 hour and transaction log every 1 hour. Actual problem is to the growing size of the transaction log though there is no transaction is performed. Two options are available in back up 1. Append and 2. overwrite to the file.
So my question is why the transaction log is growing with any of the option? Full backup file and differential back up file is not grwing for the same option overwrite when no transaction is performed.

Ryan
//All our dreams can come true, if we have the courage to pursue them//
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47295 Visits: 44392
One thing I would like to get clear.

Are you backing up the transaction log to the same file every time?
Do you have the overwrite option specified?


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


Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4463 Visits: 9833
Are you backing up to a device - or a file? How is this being selected?

What Gail was trying to get at is that overwriting an existing file destroys any maintenance plan you setup. What good is a transaction log backup if it only contains the currently backed up transaction log? You won't be able to restore the system because you are now missing the previous transaction log backup.

If you are also overwriting the differential backup - you have the same problem. As well as overwriting the full. To understand better, what is going to happen if you have to restore the system to 10:50am, but your differential backup ran at 11:00am and overwrote the previous differential backup that was run at 10:00am? Add in the fact that you have overwritten the transaction log backups and you don't have a method of restoring to that point in time. The best you can do is restore to the latest differential.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47295 Visits: 44392
Jeffrey Williams (4/24/2009)
What Gail was trying to get at is that overwriting an existing file destroys any maintenance plan you setup.

Note to self. Get to the point..., ;-)

The best you can do is restore to the latest differential.

Providing that the full backup that was the base for that diff is still available.


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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search