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 |
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
 Posted - 2006-12-06 : 22:22:14
|
| I know this topic was just done but i didnt want to high jack the post , so thought id start my own, we're currently running a huge database system, with hugeeee transactions performed constantly,the only setup we have now is recovery : SIMPLEBACKUP : FULL BACK UP (every day 6am)but we are thinking of bringing in transaction logs every 30 mins or 1 hour... and moving recovery to FULL.However the problem we have here is we get alotttt of timeout during the back up process , probably 10,000.so i was wondering how we might reduce this?also i was thinking of implementing this system:FULL BACK (saturday 6am)DIFFERENTIAL BACKUP (MONDAY AND WEDNESDAY 6am)TRANSACTION LOGS: 30-1hourRECOVER: FULLwhat you guys think? |
|
|
monty
Posting Yak Master
130 Posts |
Posted - 2006-12-07 : 01:13:11
|
| As you have said that its a huge database and it has huge transactions i wld go 4 this plan:full backup everyday at 1:00AMlog backup every:15-30 min 1:30AM-12:AMconsider taking full abckup daily, if it is very huge then you can go for file/file group backupsi certainly dont prefer diffrentail backups but it is left to your choice if recovey time is crucial for you.its me monty |
 |
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2006-12-07 : 04:24:22
|
| yeah as ive stated i am on full back up everyday 6am, thats when i get the timeouts |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-07 : 05:02:23
|
"the problem we have here is we get alotttt of timeout during the back up process , probably 10,000"Is this during your current FULL backup, or during an experiment with Tlog backup?Is it certain processes that are maybe sub-optimal and maybe getting pushed over the edge?Personally I can say that we see any end-user impact of backups on the performance of our systems"huge database system"How big?"with hugeeee transactions performed constantly"How many? And 24 hours a day, or is there a quieter period?"we are thinking of bringing in transaction logs every 30 mins or 1 hour... "Beats me how often I see this. Why is 1 hour an acceptable loss? Why not do the TLog backups every 10 minutes?One option, assuming that the TLog backups are fast enough not to cause timeouts, would be to log-ship them to another server, and then take your full backups from that server."FULL BACK (saturday 6am)DIFFERENTIAL BACKUP (MONDAY AND WEDNESDAY 6am)"Problem I foresee with this is that a RESTORE to MONDAY 05:59AM will involve restoring a HUGE amount of TLogs. Which will take a HUGE amount of time  What do you do about Defragging the indexes, and updating the statistics if your system is busy round the clock?Kristen |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2006-12-07 : 17:29:18
|
| yep timeouts during the full back up process...huge as in 180GIG datasize...it has transactions 24 hours, however 5-6am is usually the quitest (however small amount of transactions)...we do indexes etc usually between that time frame.I was thinking for the timeouts to increase timeout values in IIS and OBDC but i dont know if that would help |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-12-08 : 01:18:17
|
| "I was thinking for the timeouts to increase timeout values in IIS and OBDC but i dont know if that would help"Yes, I think that is reasonable. But if you timeouts are during full backup then trying to reduce the length of time of a full backup would probably help.Might be worth trying a DIFF just before the full backup would normally run and see a) how long it takes and b) how big it is. Your maintenance routines are likely to cause DIFF to be pretty large after a couple of days ... but that might be a route worth going down.How would you recreate the data if the database was lost (say 10 minutes before the Full backup was due)?If you couldn't (e.g. "general public are placing orders via web", rather than "office staff are typing in paper orders") then you need to move to TLog backups ASAP !!Kristen |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2006-12-08 : 01:29:08
|
| thanx for the tip ill give it a go |
|
|
|
|
|
|
|
|
back up advise needed for this situation