SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

AskSQLTeam
Ask SQLTeam Question

USA
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

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

Cheers

Alastair Jones"

SQLServerDBA_Dan
Aged Yak Warrior

USA
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
www.dallasteam.com
Go to Top of Page

Kristen
Test

United Kingdom
22415 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!)

Kristen
Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.06 seconds. Powered By: Snitz Forums 2000