Mirroring - Log File Size management
-
Hi
I have two SQL servers in a mirror, and its working great - beats so many shades of crp out of replication I cant believe it. Im not sure how I go about managaing the size of my log files however.
I have a reindex that runs every week that creates a log of about 130 gig. Getting this over to my mirror isnt a problem as whilst its a WAn connection, its on a 15Mbps pipe. The problem is running out of disk space on my primary. Before I started mirroring I could run a
DUMP TRANSACTION [database_name] WITH NO_LOG
to reduce the file back to a couple of Meg - now that the DB is mirrored, SQL does not allow me to do that (or run its equivalent 2005 command)
I can backup the log, and then run a shrink, but that only frees up a couple of gig. Also, I could limit the size of the transaction log, but im not sure if this is a good idea or not, and if im in fact shooting myself in the foot in the event of a re-index / dbase failure where I could restore / recover from a trans log if its werent size limited..
what do other people do to manage the size of their log files?
Cheers All
Alastair
-
also, if my mirror server fails (for whatever reason) and the log size is limited on my primary, whats going to happen?
-
Curently I am fighting a Bug with M$ that only happens under very special conditions and makes the log grow exponentially
For your conditions.
1. You need to make the reindexing job smarter.
Only perform what must be done (don't rebuild everything)
Perform log backups in between large index rebuilds and statistic updates
Use online option if possible
Carefully analize if defragging is enough instead of Rebuild
2. Schedule the rebuilds at low usage times
3. Good Luck
* Noel
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply