Optimization Job blows up T-Log

Is it normal for an opti job to blow the T-log on a 80GB db to 50 GB from
2GB?It is not unlikely, depending on how full the database is, if your tables ha
s clustered indexes etc.
In BOL, under DBCC SHOWCONTIG, you have code that will defrag only indexes t
hat are fragmented. But
first read http://www.microsoft.com/technet/pr...bp
.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:%23OT0$mLBGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Is it normal for an opti job to blow the T-log on a 80GB db to 50 GB from
> 2GB?
>|||In addition to Tibor's comments you have to understand that in Full recovery
mode even Reindexing is fully logged. I assume you are using the
Maintenance Plan which uses DBCC DBREINDEX. This will recreate all the
indexes so you have essentially at least as much logged as the size of the
indexes. If you don't issue a log backup during the operation the log file
will have to hold all of those transactions.
Andrew J. Kelly SQL MVP
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:%23OT0$mLBGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Is it normal for an opti job to blow the T-log on a 80GB db to 50 GB from
> 2GB?
>|||I thought page allocation/deallocation did not take up that much space in
t-log
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O4g6ArMBGHA.272@.TK2MSFTNGP11.phx.gbl...
> In addition to Tibor's comments you have to understand that in Full
> recovery mode even Reindexing is fully logged. I assume you are using the
> Maintenance Plan which uses DBCC DBREINDEX. This will recreate all the
> indexes so you have essentially at least as much logged as the size of the
> indexes. If you don't issue a log backup during the operation the log
> file will have to hold all of those transactions.
> --
> Andrew J. Kelly SQL MVP
>
> "David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
> news:%23OT0$mLBGHA.1312@.TK2MSFTNGP09.phx.gbl...
>|||You are copying the entire page from the original page to the new one so the
entire page has to be logged, not just the allocation of a new page.
Andrew J. Kelly SQL MVP
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:OXNz60MBGHA.3396@.tk2msftngp13.phx.gbl...
>I thought page allocation/deallocation did not take up that much space in
>t-log
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O4g6ArMBGHA.272@.TK2MSFTNGP11.phx.gbl...
>|||would switching to bulk-logged b4 opti only record alloc/dealloc ?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ehrnVENBGHA.2512@.TK2MSFTNGP09.phx.gbl...
> You are copying the entire page from the original page to the new one so
> the entire page has to be logged, not just the allocation of a new page.
> --
> Andrew J. Kelly SQL MVP
>
> "David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
> news:OXNz60MBGHA.3396@.tk2msftngp13.phx.gbl...
>|||Yes, index creation and drop including rebuild will result in less logging.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David J. Cartwright" <davidcartwright@.hotmail.com> wrote in message
news:%23ubegMNBGHA.2356@.tk2msftngp13.phx.gbl...
> would switching to bulk-logged b4 opti only record alloc/dealloc ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ehrnVENBGHA.2512@.TK2MSFTNGP09.phx.gbl...
>