Here's my scenario:
- SQL Server 2005 Enterprise
- Records from Database A being added to Database B
- Size of Database A MDF file: 3.15 GB
- I have separate drives for Data, Logs, and System.
I ran a T-SQL script, adding 1.8 million records from database A to a table with 7.3 million records in database B.
When I started the script I had about 200 GB free on my Data drive. 12 hours later the job wasn't finished and completely filled up my Data drive!
I could maybe understand if I had lots of indexes on this destination table that would need to be updated when adding new records, but the indexes only take up 687.969 MB. How could a database with a 3.15 GB
.MDF fill up 200 GB of space?
I shrank the
.MDF and tried again, this time Inserting to a temp table. Before running this script I had 168 GB free on my system drive. After running for 2 hours the script wasn't finished and
TempDB completely filled up my system drive!
What could be causing this? I have confirmed the script isn't adding more records than it should.
Here's my code - I've used similar code to this in the past without this runaway
.MDF file growth:
INSERT INTO mydbB.dbo.TableA (FieldA, FieldB, FieldC, FieldD, FieldE, FieldF, FieldG, FieldH, FieldI, FieldJ, FieldK, FieldL, FieldM) SELECT a.FieldA, b.FieldB, 129 as FieldC, a.FieldD, c.FieldE, b.FieldF, a.FieldG, b.FieldH, b.FieldI, b.FieldJ b.FieldK, b.FieldL b.FieldM FROM mydbA.dbo.TableA a LEFT OUTER JOIN mydbA.TableB b ON a.FieldB = b.FieldB LEFT OUTER JOIN mydbA.TableC c ON a.FieldG = c.FieldG WHERE a.FieldA NOT IN (SELECT FieldA FROM mydbB.dbo.TableA WHERE FieldB = 129)
It seems to be copying the right number of records. If I do a
select count(*) on my select statement, I get 1,839,793, which makes sense given I'm doing left outer joins:
select count(*) from mydbA.dbo.tableA --1,839,793 select count(*) from mydbA.dbo.tableB --716,840 select count(*) from mydbA.dbo.tableC --32,289
So what could cause runaway
.MDF growth when the query returns the correct number of records and the source database is only 3.15GB?