Minimize data loss - SQL Server Forums

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)

Minimize data loss

Author

Topic

CanadaDBA

583 Posts

Posted - 2004-08-17 : 11:28:28

In a Win2000 environment and SQL-Server 2000, I get a backup everyday after work @ 8:45pm. I have set transaction logs backup every 1 hour, starting @ 7am and ending @ 8pm. Recovery model is Full. The files are all on the server but all information are go into tape backup hourly as well.

Assume that my hardware is ok and all backup and transaction logs are on harddisk but something happens on my database at 2:00pm that I have to restore it. I can restore the last night backup and then restore every hour transaction logs up to 1:00pm (because the 2:00pm TLog is not ready). This way I will lose 60 minutes of data.

Questions:
1. If I set the transation log backup to every 30 minutes, how it impacts my server performance?

2. Are there other ways to minimize the data loss?

3. Is it good idea to have a replicated server for this situation? Or Network team should take care of this with a parallel mirror server(I don't know the exact name)?

Thanks,

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-17 : 12:10:34

You should not lose 60 minutes of data. If your hard drive is ok, you might still be able to backup the transaction log so that you restore to the point of failure.

We backup our transaction logs every 15 minutes without any performance degradation.

It is always a good idea to have a disaster recovery server at a remote site. You can use log shipping for this.

Tara

CanadaDBA

583 Posts

Posted - 2004-08-17 : 12:38:10

Thanks Tara; Would you give me more info about this? Any link?

quote:
Originally posted by tduggan

...
It is always a good idea to have a disaster recovery server at a remote site. You can use log shipping for this.

Tara

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-17 : 12:56:37

Books Online has details about log shipping.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-17 : 22:44:55

We backup every 15 minutes here also. I think that's pretty standard in financial industries. Eventually I'm sure we'll get a real hotsite setup with hardware replication like Tara is doing. :) That's extremely expensive though.

You need to monitor your disk queue lengths, processor utilization, paging, etc to make sure the log backups aren't affecting your overall performance enough to affect the business. If they are though, you just need to buy more hardware. It will hurt the business a lot more if they can't recover from a disaster.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

CanadaDBA

583 Posts

Posted - 2004-08-19 : 10:28:26

I noticed my TLogs size are as follow:


07:00    2400.0 MB
08:00      20.0 MB
09:00       4.3 MB
10:00       3.5 MB
11:00       2.8 MB
12:00       6.4 MB
13:00       3.4 MB
14:00       3.0 MB
15:00      17.0 MB
16:00       9.3 MB
17:00       2.5 MB
18:00       0.4 MB
19:00       0.3 MB
20:00       0.2 MB
07:00    2370.0 MB

Question is why the first log size in the morning is about 2 GB while there is no transaction since 8:30pm previous night that the backup is taken?

Canada DBA

CanadaDBA

583 Posts

Posted - 2004-08-19 : 10:30:20

I forgot to write that there are maintenace plans running during the night.

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-19 : 12:23:17

If one of the maintenance plans is optimizations, then that's why the tlog is so large. You should be backing up the tlog during the night too to get the maintenance routines in there.

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-19 : 13:12:05

An optimization or reindex reorders the indexes, which means that all those changes must be written as a transaction. Our first transaction log backups of the day are over 45gb.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-19 : 13:20:18

Just wait til you ship those out to your disaster recovery site. We get about an hour out of sync when these tlogs transfer over. We used to page ourselves when log shipping got out of sync, but after getting paged needlessly in the middle of the night too many times, we now only page when it gets way out of sync.

On some of our systems, we reindex less often. Reindexing probably couple times per month and indexdefrag every few days.

Tara

CanadaDBA

583 Posts

Posted - 2004-08-19 : 14:54:13

Tara,
If I undrestand correctly, Derrick is getting T-Logs only at work time. Just like what I do. And you suggest I set the T-Log all 24 hours. Am I right? In that situation, wouldn't the total size of T-Logs at night greater than the one that I have in the morning?

Totally, what is the recommendation?

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-19 : 14:59:07

I believe Derrick backs up the tlog every 15 minutes like we do. It doesn't stop at night, nor should it. If you backed up the tlog more often, you would have more reasonable sized tlogs instead of a large one when the tlog backups resume during work hours. The total size of them combined though would be equal. Keeping the tlogs a manageable size is important in case you ever need to restore them. You wouldn't want to have to restore a 3GB file if you only need the first 5 minutes of it.

I would recommend hourly regardless of work hours. We do ours more often due to the criticality of the data and not being able to afford to lose more than 15 minutes of data. Our systems are used 24/7, but even if they weren't, we would still backup the tlogs during non-working hours due to the maintenance routines causing large tlogs.

Tara

CanadaDBA

583 Posts

Posted - 2004-08-19 : 19:14:16

Fare enough!
Is it possible to set TLog backups every 15 minutes for work hours, and hourly in non-work hours?

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-19 : 19:15:22

Yes you would have two schedules in the job.

Tara

Kristen
Test

22859 Posts

Posted - 2004-08-20 : 02:52:37

"Is it possible to set TLog backups every 15 minutes for work hours, and hourly in non-work hours?"

I can see the Human Factor sense in this, but I'm struggling to see why you would want to bother with the complexity of two schedules - does it matter if the TLogs backup every 15 minutes during the night as well?

The only thing I can think of is that a RESTORE needs to have every single one of the little blighters specified in order to get all the data back. But I keep a script handy that will generate all that info anyway (and Entprise manager does a good job of that particular exercise too - albeit slowly!)

Just curious ...

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-21 : 15:56:20

We avoided it for this very reason. I have a table that lists when backups happen, when they are restored, etc. I have another one that's the schedule for this so we can do auditing, proactive reports, etc. We just keep the tlogs at every 15 minutes though. It's easier that way.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

CanadaDBA

583 Posts

Posted - 2004-08-25 : 14:19:22

Would you tell me more? What you mean?

quote:
Originally posted by Kristen

"...But I keep a script handy that will generate all that info anyway (and Entprise manager does a good job of that particular exercise too - albeit slowly!)

Just curious ...

Kristen

Canada DBA

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-25 : 16:41:19

Why bother with this complexity though? Why can't you just back them up every 15 minutes? We've been doing this for a long time without any performance problems.

Tara

CanadaDBA

583 Posts

Posted - 2004-08-26 : 15:18:51

Tara,

I have got my answer. I learned a lot from the posted replies and thank you all for them. I just want to learn more about what Kristen has written.

Farhad

Canada DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources