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 |
|
Balance
Starting Member
24 Posts |
 Posted - 2005-09-13 : 14:38:46
|
| For some reason, every time my weekly re-indexing job runs (as part of the maint. plan) the t-log file gets shrunk (instead of keeping the space allocated size). I've checked that the "Remove unused space from database files" checkbox isn't checked but it still happening. Can someone shed some light?TIA |
|
|
Thrasymachus
Constraint Violating Yak Guru
483 Posts |
Posted - 2005-09-13 : 14:48:03
|
| does your maint plan back them up?====================================================Regards,Sean Roussy"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?) |
 |
|
|
Balance
Starting Member
24 Posts |
Posted - 2005-09-13 : 14:54:00
|
| Yea, every 2 hours and the t-log doesn't shrink. This only happens when the re-indexing job runs.Thanks |
|
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-09-14 : 05:17:59
|
A lot of the issues posted here re logfiles are to do with them NOT shrinking...maybe you could sell your problem to those poor folk |
|
|
|
Balance
Starting Member
24 Posts |
Posted - 2005-09-14 : 09:38:19
|
| Yea, no kidding.Anyhow, I'm thinking about changing the database recovery mode to Simple since the database gets backed up nightly and my client is OK with rolling back to "yesterday's" database. Using the Simple model would not cause the t-log to grow at all, right? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-09-14 : 12:28:24
|
| It could still grow with SIMPLE recovery model. It will always use the tlog, it's just that once the transaction is completed, it won't be in there anymore. Also if you reindex, then the tlog will get pretty big.Tara |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2005-09-14 : 14:14:39
|
| "my client is OK with rolling back to "yesterday's" database"We've had clients like that. In the event of a major hard disk failure they go "Yup, stuff happens"However, when someone accidentally dropped a table they didn't apply the same easy-going attitude! and I've stuck with TLog backups for pretty much all [non-readonly] databases ever since.OTOH I'd be very happy toggling to SIMPLE just during index rebuilds if the database was not in use, or the usage could afford to be lost.We only rebuild indexes on tables that are fragmented - that typically means that we only rebuild around 10% of the indexes, and I reckon our TLogs are pretty manageable.Just thinking out loud, your case may well be different.Kristen |
|
|
|
|
|
|
|
|
t-log shrinks automagically?