Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Backing up the transaction logs

Author  Topic 

pras2007
Posting Yak Master

216 Posts

Posted - 2008-03-10 : 12:07:31
Hello All,

I run a full nightly back up every day of the database. But lately the transactions log backups are as big as the full backups now I’m running out of space. Can anyone tell me which backup plan would be better suited for my situation? Thanks.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-10 : 12:21:34
Full backup: Every day
Differential: Every 5 hours
Transaction: Every 15 min
Go to Top of Page

pras2007
Posting Yak Master

216 Posts

Posted - 2008-03-10 : 14:34:46
Thanks sodeep, for your response. In my recovery plan I did not have auto shrink selected does this matter? Should I have it selected? Please advice. Thanks.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-10 : 14:51:09
You should not have autoshrink selected in a production environment, nor should you have a scheduled shrink job.

My backup plan is the same as sodeep's but without the differential backups. I don't see the need for the differentials when you perform daily full backups.

But you need to understand that the sizes will be the same if you ran one tlog backup per day or every 15 minutes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-10 : 15:04:42
'But you need to understand that the sizes will be the same if you ran one tlog backup per day or every 15 minutes.'

Why is that? I think it depends on transaction rate and we take deifferential because It will be headache to apply transaction log for whole day while restoring

Go to Top of Page

pras2007
Posting Yak Master

216 Posts

Posted - 2008-03-10 : 15:07:06
tkizer thanks for your response... I'm just thinking of other ways to solve this problem. Should I purge all the old data and shrink the databases or I should request for more space to be allocated to the server? Please advice. Thanks.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-10 : 15:22:27
Add more space.

sodeep, it doesn't matter how many files you have. Add them all up and they'll equal about the same size if you only did one tlog backup in a day.

And as for the restore, you just script what needs to be restored so you don't have to worry if you have 1000 files to restore or just 2 files.

Differentials are typically used so you don't have to perform full backups daily. Some people will perform differentials 6 days a week and a full backup on the other day.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-10 : 15:25:34
I understand that Size will be same but it will be faster to restore from one differential rather than restoring each tran log file.

Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-03-10 : 15:29:13
Not really if you know how to script it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-03-10 : 15:32:57
Thats right Tara. I like doing by GUI .
Go to Top of Page
   

- Advertisement -