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.
| Author |
Topic |
|
franco
Constraint Violating Yak Guru
255 Posts |
 Posted - 2006-10-12 : 04:46:54
|
| Hi all,We have many databases to manage and my question is related to T-Log backup.At the moment we take t-log backup every 1 hour but on different time for each database.For exaample for db1 we take t-log backup at 7.01 AM every hour until 19.01 PM and for db2 we take backup at 7.05 AM until 19.05 PM and so on.Is it ok to perform t-log backup for all the databses at the same time or is it better to perform it on different time like now?Our system is a 2 node MSCS Cluster with SQL Server 2K Enterprise Edition, 8 GB RAM and 4 CPU for each node.SQL Server has a dedicated memory of 2GB.Please advise.Franco |
|
|
monty
Posting Yak Master
130 Posts |
Posted - 2006-10-12 : 05:12:35
|
| The current procedure of urz in take tlogs at diffrent times is gud procedure indeed, provided all the Dbs are on same box..Becoz it would avoid any blockings if any and also the processes wldnt starve for resources, hence there wld be less queing of processes and more cpu utilization.its me monty |
 |
|
|
franco
Constraint Violating Yak Guru
255 Posts |
Posted - 2006-10-12 : 08:10:11
|
| Thank you very much for your suggestion.Cheers.Franco |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-12 : 15:04:02
|
| We backup our databases every 15 minutes. We do not see performance problems when they all kick off at the same time. Our backups kick off at hh:00, hh:15, and hh:30. They only kick off at the same time though when we use log shipping. For our non-log shipped databases, we use one job so they all happen one after the other.Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-13 : 05:51:53
|
| We use a single stored procedure that makes the backups, and does each database in turn [alphabetically] (which I have now just more carefully read is what Tara says she does too!)However, it does raise the question about what happens when it takes more than 15 minutes. My understanding is that another instance of the Sproc will launch, that will start backing up the alphabetically-earlier databases, when it gets to the database being backed up by the first instance it will sit&wait - and presumably the alphabetically-later databases will NOT get backed up until the long-running backup is done. That is something maybe I should investigate and have the second-instance skip the database that is already being backed up so that the alphabetically-later aren't at greater risk.Kristen |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-10-13 : 06:38:19
|
| I setup a separate job ot each DB, and usually stagger the start times.I use separate jobs to allow more control of frequency. For some DBs, I backup at 5 minute intervals, instead of 15, to keep the log file size under control.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Transaction Log Backup