Maintenance Schedule

[DBAWinnipeg]

Good Afternoon everyone

I'm gonna throw this diaper against the wall let me know if you think it will stick or not.

I'm in a 24/7 shop with at most a 1/2 hour window for maintenance. With all the maintenance that needs to happen and the size of the database, etc this window is not even close to the amount of time required to get things purring.

So here is my revolutionary (yet probably already thought of, whitepapered, and other there somewhere idea)

2 servers and we start with the same database, 1 server processes daily transactions for 23.5 hours of the day, while this is all going on the 2nd server is performing maintenance offline with no user activity so things like reindexes, DBCCs, blah blah blah, basically getting everything tickideeboo.

Now at 23.5 hours during the "outage window" the transaction log from the server that has been processing daily activity is applied against server 2 which has finished all it's maintenance that needs to happen and when the 1/2 hour is up activity for the day now goes to server 2 while server 1 does all the maintenance it needs to and we go back and forth from server to server everyday.

Thoughts? comments? Better Solutions?

Anyone else have to deal with VLDBs and short maintenance windows?





Thanks in advance!!!

Colin in da 'Peg

 

[hmckillop]

That might be ok if you are talking about minimal data changes during a day otherwise as soon as the transaction log is applied all the data writes/deletes etc will happen leaving indexes fragmented again.
We run a 24/7 process, but realistically the system isnt used 24/7 so we run a job which tries to do as much as it can but ensuring that users have priority -e.g. if system being used we use indexdefrag as opposed to dbreindex etc.
We do all our maintenance when the DB is effectively online but low usage.
Our DB holds about 80GB of data and takes about 1-2 hours to process the maintenance, and generally it finishes fine but on nights when it doesnt we just leave it for the next night to sort it out.

It will be interesting to see what other solutions there are around as i know our way isnt optimal.


"I'm living so far beyond my income that we may almost be said to be living apart