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 |
|
Kristen
Test
22859 Posts |
 Posted - 2004-11-26 : 23:42:31
|
| Would this be a daft plan?BACKUP TLogTurn off loggingDo all housekeeping that has massive impact on logsRe-start loggingBACKUP DBMy LDF files are getting massive just because of the elbow-room needed for housekeeping. At the time of the night we run the Defrag there is almost no-one using the DB - so if we lost the database at that point we wouldn't lose much business. OTOH if I DID have the TLogs AND I had to roll them forwards (after some disaster) I reckon it would take forever to bring the server back up!But I'd like to base my plan on some real world experience pleaseKristen |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-11-29 : 13:22:44
|
| I certainly wouldn't turn off logging during DB maintenance. INDEXDEFRAG is minimally logged as compared to DBREINDEX. It's a real pain for us those tlog sizes as we log ship them to another state (about 300 miles away). So log shipping gets quite a bit out of sync during this time, but it's something that we just deal with because of the criticality of our data. We're putting in a bigger pipe to deal with this slow speed.Tara |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-11-29 : 13:33:57
|
I've been having a right bear! with my LOG table. Sizewise it's about 60% of the database, and an INDEXDEFRAG is causing the LDF to grow bigger than the MDF - so I wouldn't call it "minimal logging" in my case :-(. Elsewhere Nigel suggested that I shouldn't need to defrag it at all 'coz its using a Clustered PK on an IDENTITY column. I've currently got some updates to the rows after creation (a "Finished" time is added, and a varchar(1000) of any error messages) - his suggestion was to store these in a separate table so that I have NO updates.The database is going to need log shipping at some point, so might as well not do anything [to the maintenance routines] to muck that up in the interim  Kristen |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-11-29 : 15:33:25
|
| if indexdefrag is resulting in poor performance for one of your tables, try DBREINDEX on that table. Sometimes, as in the case where you are moving all your data pages, this will result in a quicker and more efficient cleaning of your index.Of course, your issue is that you don't think indexdefrag should be causing so much activity, so maybe this isn't the right step, but it's worth an explore, dontcha think? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Turn TLogs off during INDEXDEFRAG?