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 |
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
 Posted - 2006-07-11 : 06:44:32
|
| Hi,I have a DB who's .ldf file sits happily at 250MB, but whenever I rebuild indexes it balloons up to 950!And then!... I can't shrink it back, irrelevent of how many times or how often I back it up, until I first put the model into Simple Model, back it up, then shrink it.What's all that about, aye?Drew aka The Drewster |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-11 : 07:29:01
|
| "What's all that about, aye?"Use DEFRAG instead.REINDEX copies the whole nine yard to a fresh part of the MDF file, and presumably logs the whole operation too.DEFRAG runs in-situ, and can be aborted mid-stream (i.e. retaining the work done to date), and is much less intrusive on concurrent operations.We still do REINDEX on very small tables (< 1,000 rows I think)And don't rebuild anything unless it is fragmented - otherwise a) you are doing the work for no good reason and b) that wasted disk space didn't need to be wasted either!Paul Randall has a white-paper on it, but I don't have the URL to hand, but trawling around here should locate it.Not sure its terribly helpful, but this may help:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Rebuilding+IndexesKristen |
 |
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2006-07-11 : 09:27:20
|
| Right.But what about the second point. You don't normally need to put something into Simple Model to shrink it efficiently, do you?Drew aka The Drewster |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2006-07-11 : 10:25:14
|
| Are you backing up and shrinking the LOG file rather than just the database? This is where (some of)the increase in space is going to be.-------Moo. :) |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2006-07-11 : 14:22:01
|
| Yeh, I've tried both shrinking the database and the individual files, but it will only shrink if in Simple model. It's not a big deal, I just thought there may be a clear reason for it.Drew aka The Drewster |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-22 : 08:53:00
|
| "You don't normally need to put something into Simple Model to shrink it efficiently, do you?"No, but if you have an uncommitted transaction in your log file then that will prevent shrinking - which presumably setting the DB to SIMPLE will cure!There are other ways to force the log to be truncated & shrunk, but they are a bit brutal!Kristen |
|
|
|
drewsalem
Constraint Violating Yak Guru
304 Posts |
Posted - 2006-07-22 : 16:37:49
|
quote:
Originally posted by Kristen
"You don't normally need to put something into Simple Model to shrink it efficiently, do you?"No, but if you have an uncommitted transaction in your log file then that will prevent shrinking - which presumably setting the DB to SIMPLE will cure!
That's interesting. This must reflect the way this particular application works, as it can only be shrunk in that model.Drew aka The Drewster |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-07-22 : 16:58:27
|
"This must reflect the way this particular application works"This scenario is relatively rare, IME, but you can get a transaction that is orphaned at the end of the LOG file, and prevents the [physical file containing the] log from being shrunk. Its not related to the transaction itself being committed - I expect it will have been - but to do with SQL Server actually having "posted" that transaction to the database - perhaps by issuing a Checkpoint. I've seen various techniques for sorting this, including (usually) posting additional dummy transactions to force that final block to be "full" so that it can be physically "posted" to the database.I did all that buggery-bollocks for years when sorting out clients' run-away databases, but now I reckon that a simple:USE MyDatabaseGOBACKUP LOG MyDatabase WITH TRUNCATE_ONLYGODBCC SHRINKFILE (MyDatabase_Log, 1)GO does the trick.Kristen |
|
|
|
|
|
|
|
|
Rebuilding indexes hogs space