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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-10-05 : 07:59:06
|
| Alastair writes "HiI 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_LOGto 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..also, if my mirror server fails (for whatever reason) and the log size is limited on my primary, whats going to happen? what do other people do to manage the size of their log files?CheersAlastair Jones" |
|
|
SQLServerDBA_Dan
Aged Yak Warrior
752 Posts |
Posted - 2006-10-05 : 12:01:40
|
| When do you do a FULL or DIF backup? Before or after the reindexing?Daniel, MCP, A+SQL Server DBAwww.dallasteam.com |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-05 : 13:47:21
|
| I suggest you consider INDEXDEFRAG instead of REINDEXCouple this with checking the fragmentation, so that only the files that need it get defragged.You could also interject TLog Backups in the process - so that logs are backed up more frequently than normal.And you could alternate which files get done day by day. We run this every night, and typically only a handful of files are sufficiently fragmented to need any treatment at all.Tara's Blog has some routines that will help, the concepts have been discussed on SQL Team, but I don't have any links to hand (clearly need to find so and add to the FAQ though - so please post any you find here!)Kristen |
|
|
|
|
|
|
|
|
Mirroring - Log file size management