Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Mirroring - Log file size management
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 10/05/2006 :  07:59:06  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Alastair writes "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


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..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?


Alastair Jones"

Aged Yak Warrior

752 Posts

Posted - 10/05/2006 :  12:01:40  Show Profile  Reply with Quote
When do you do a FULL or DIF backup? Before or after the reindexing?

Daniel, MCP, A+
SQL Server DBA
Go to Top of Page


United Kingdom
22859 Posts

Posted - 10/05/2006 :  13:47:21  Show Profile  Reply with Quote
I suggest you consider INDEXDEFRAG instead of REINDEX

Couple 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!)

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000