|
|
|
SSC-Enthusiastic
 
Group: General Forum Members
Last Login: Thursday, February 02, 2012 3:25 PM
Points: 158,
Visits: 147
|
|
| I have a SQL 2005 database with a transaction log that is 233GB in size. There is a large transaction running which inserts 181 million rows via linked server from a DB on another instance on the same box. (Single Node Cluster with two cluster groups.) The transaction log usage for this database was close to 0 percent when the transaction started 6 hours ago (This is a dev box with slow disk and poor hardware.) The rowcount for the dest table is at the final size as of about 3 hours ago. There is nothing else going on except for this transaction. The total size of the table with indexes is about 63GB, but the log usage is now at 80% and still growing. Why might I be seeing such excessive use of the log for this single transaction?
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, February 02, 2012 3:25 PM
Points: 158,
Visits: 147
|
|
| Folks, please accept my apologies for the misleading title. There is no "log growth," and the title should read "log usage."
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, February 02, 2012 3:25 PM
Points: 158,
Visits: 147
|
|
Also of interest, while the destination table (which was empty before insert) is estimated at 50+ GB just for data, the source table is only 18GB. What am I missing folks?
I have a destination table using almost 3x the data of the source table (not counting index space) and then transaction log usage over 3x the total of index space + data space on the destination table.
|
|
|
|
|
SSC-Enthusiastic
Group: General Forum Members
Last Login: Thursday, February 02, 2012 3:25 PM
Points: 158,
Visits: 147
|
|
| Given my index space is still growing, the problem seems to lie there. Still seems ridiculous. It has now used 240GB of log space to import an 18GB table.
|
|
|
|
