| Author |
Topic |
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
 Posted - 2007-05-02 : 12:58:33
|
| One of my customers has hinted that just at the time when we backup the transaction logs, the system response time slows down like hell.Although the backups last only about 1 minute.Anybody face similar situation or concerns before?I have requested to re-create the problem by manually running the same transactions when the backups are actually happening so I can run both the profiler and perfmon. Any other ideas, let me know.RegardsParesh MotiwalaBoston, USA |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-02 : 13:56:34
|
| How often are you backing up the transaction log? It should not be taking a minute to do it if you are backing it up frequently. How big is the transaction log backup?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-02 : 14:00:09
|
| Where to put backup files? If on same disk with db file, slowness may caused by heavy disk i/o. |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-05-02 : 14:09:26
|
| The entire process takes about 1 minute to finish.The logs are backed up 9 am, noon and 3PM. to the same disk as the DB, unfortunately.We don't have an option there.RegardsParesh MotiwalaBoston, USA |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-02 : 14:12:42
|
| Try backup log hourly then. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-02 : 14:21:09
|
| We backup our transaction logs every 15 minutes. This keeps the backup time down to only a few seconds and the file size down for each individual file.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-05-02 : 15:02:03
|
| The normal log backups are ok. they are about 150K or so.Only when I do re-indexing is when the log size goes up like crazy.This is the message I send to the developers after the job is complete.Last ABC Log Size 220 KB is well within average of 254572 KB.Last DEF Log Size 140 KB is well within average of 398 KB.Last CMR Log Size 389 KB is well within average of 6012 KB. Last SRC Log Size 151 KB is well within average of 3919 KB. RegardsParesh MotiwalaBoston, USA |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-02 : 15:07:59
|
| That's normal for reindexing to cause large transaction logs. You should increase the frequency of your transaction log backups especially around the time that the reindex is running.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-02 : 15:09:36
|
| Why do developers care about the log size? Only a DBA should be aware of that information.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-05-02 : 15:21:19
|
| During peak hours and during peak days, in the government office where I work, there are a lot of transactions. Sometimes it is good to let the developers to know that their app is being used well. This actually came about when an external user had a completed data reload usin excel spreadsheets. So if we know the peaks, we can plan for a better Developer Coverage.Besides, they like to receive automated messages, in fact they love me for this. I even provide them with standard deviation...But point well taken, I will take the logs more frequently after the re-indexing.These databases are small 100MB to 4.5GB. The one we are about to migrate are 10-40GB. I am worried about the log sizes chewing up disk space.RegardsParesh MotiwalaBoston, USA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-02 : 16:38:11
|
| "The logs are backed up 9 am, noon and 3PM"Sorry to be outspoken, but this approach never fails to amaze me. if you are backing up your Logs why is 3 hours an acceptable "window", and therefore potential loss? Why wouldn't you backup you logs every 10~15 minutes to minimise the potential for loss? Not to mention reducing your overall LDF file size in the process, which in turn improves recovery time on SQL 2000 [at least]Kristen |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-05-03 : 11:19:55
|
| Thank you Kristen, Outspokenness is what keeps this forum going.You are right, I have had no control over that, I have inherited a non-dba-owned environment.Everything that has to change in the Government has to go through unbelievable red tape.The business is ok with the 3 hour loss of information.(seriously). Strangely, where I come from, the logs were taken only once every 12 hours even for a 120GB databases. But I will endeavor to put an hourly log in place.RegardsParesh MotiwalaBoston, USA |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-03 : 17:13:33
|
"I have inherited a non-dba-owned environment"Better to do consultancy on those, rather than "inherit" them  Cutting the time down to 10-15 minutes will reduce the strain on the LDF file when, e.g., Index Rebuild is running.I figure that more often than 10-15 minutes is likely to put some strain on the server, less often than that gains nothing that I can think of - except there will be fewer files in the event of a restore! but the total size of a day's worth of TLog backups will be the same (give or take a few bytes overhead for each file), and it might just save the day some-day!Kristen |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-05-04 : 10:11:20
|
| Thank you folks. I have put in an hourly log backups schedule. Somehow when I get the results, it has the entire script in it. I tried to figure out where it is getting it from...but no luck.and after saving when I go back to check the databases selected, I get no databases selected. But... the logs are indeed backedup and I don't even get any notification despite the fact that I am an operator.RegardsParesh MotiwalaBoston, USA |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-05-04 : 11:54:54
|
| Did you set job notification? |
|
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2007-05-04 : 12:19:22
|
| yes, but after install of sp2, when I opened up the packages to modify them, the notification part got lost, not sure how? But I did fix that.ThanksRegardsParesh MotiwalaBoston, USA |
|
|
|
|
Log Backups and Performance issues