Index building transactions

We have had an unusual experience recently. A literally tremendous amount of
transactions occurred on a table. This table had indexes placed on it. We
had uncovered the transactions from the log file through ApexSQL Log.
The symptoms were that each record in the table was being deleted and then
re-inserted with the same exact data. Over ten thousand in only a few
seconds. The log file grew to 15Gb in a day. All on a database only 566Mb
large.
These operations were on a 228,000 record table.
Could the normal operations of an index (clustered) grow a transaction log
to this size? If so, how often should this happen? I am asking this way
because I understand that a clustered index orders the table rows when
creating the index. Although, I thought the file would only grow to 1.2x the
size when creating and is reclaimed after.
Also, according to ApexSQL Log, it listed the user that these operations are
being executed as one of the database owners. Would an index rebuild be
executed under a user?
JulianPerhaps a scheduled maintenace plan (or a DBA in query analyzer) kicked off
a DBCC INDEXDEFRAG ?
"stjulian" <anonymous@.discussions.microsoft.com> wrote in message
news:ejDjuGRnFHA.3300@.TK2MSFTNGP15.phx.gbl...
> We have had an unusual experience recently. A literally tremendous amount
> of transactions occurred on a table. This table had indexes placed on it.
> We had uncovered the transactions from the log file through ApexSQL Log.
> The symptoms were that each record in the table was being deleted and then
> re-inserted with the same exact data. Over ten thousand in only a few
> seconds. The log file grew to 15Gb in a day. All on a database only 566Mb
> large.
> These operations were on a 228,000 record table.
> Could the normal operations of an index (clustered) grow a transaction log
> to this size? If so, how often should this happen? I am asking this way
> because I understand that a clustered index orders the table rows when
> creating the index. Although, I thought the file would only grow to 1.2x
> the size when creating and is reclaimed after.
> Also, according to ApexSQL Log, it listed the user that these operations
> are being executed as one of the database owners. Would an index rebuild
> be executed under a user?
> Julian
>|||Thank you so much for your reply. I am new to this
Yes, in fact there is a maintenence plan calling xp_sqlmaint from server
agent.
It reads:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
41B0BDF9-DBC9-4040-81D9-8C346B15B608 -Rpt "o:\MSSQL\LOG\DB Maintenance
Plan14.txt" -BkUpMedia DISK -BkUpDB "o:\MSSQL\BACKUP" -DelBkUps
4WEEKS -CrBkSubDir -BkExt "BAK"'
And, yes, I do have indexes on a few tables.
Is there a switch for the above (or wherever relating) to override index
rebuilding on the nightly scheduled maintenence? The indexed columns never
change.
And what do I type in Query Analyzer should I need to rebuild indexes
manually?
I think we're on to something!!!!!
Julian
"JT" <someone@.microsoft.com> wrote in message
news:eqBaJKSnFHA.2080@.TK2MSFTNGP14.phx.gbl...
> Perhaps a scheduled maintenace plan (or a DBA in query analyzer) kicked
> off a DBCC INDEXDEFRAG ?
> "stjulian" <anonymous@.discussions.microsoft.com> wrote in message
> news:ejDjuGRnFHA.3300@.TK2MSFTNGP15.phx.gbl...
>