1

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?

  • 1
    I would suggest carefully examining the script. Likely there is mistake in the script, though perhaps subtle. It is not hard to accidentally to do a join or miss a constraint such that it multiplies the amount of data being copied. Can you show the code? – RLF Oct 25 '14 at 1:59
  • 1
    Are you sure it was the mdf file and not the log file? If it was the log file, see this post - it is about deletes but applies to any DML really. Also is your T-SQL script adding 1.8 million rows, one insert at a time? – Aaron Bertrand Oct 25 '14 at 2:32
  • I would also see the autogrowth settings they should not be in Percentage. There are options like bulk insert/select * into which would be minimally logged in BL recovery model and BL would not provide Point in time recovery. Uploading in batches followed by transaction log backup would help if DB is in full recovery mode. – Shanky Oct 25 '14 at 10:28
  • Thanks for the replies! Yes, it was the MDF file. I've posted my code. It adds the 1.8 million records all at once. Seems to be returning the correct # of records, so I'm truly stumped! – user1541301 Oct 25 '14 at 20:25
  • Only other clue I can offer is Database A was originally restored from SQL Server 2005 Enterprise to a local copy of SQL Server 2012 Express where I ran some MERGE commands. I then used "Generate Scripts" to create new DDL which I used to create a new database in SQL Server 2005 Enterprise. I then used the import/export wizard to copy data from SQL Server 2012 Express to SQL Server 2005 Enterprise, and it's copying from that resulting database that's giving me problems. – user1541301 Oct 26 '14 at 18:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.