1

I have SQL Server 2008, I have 20 GB free on HD when I try to insert row in any table my HD was full before finished.

I try to insert about 200,000 rows each row with around 100 columns.

The operation consumed only space in my mdf file but not in my log file.

My destination table is empty, before today I don't have problems with this process.

Why is this happening?

Improve this question
5
  • 3
    What's the total size of the data? 200,000 * 100 = 20 million data fields. if each is 1kbyte, then there's your 20gigs used up. Don't forget database overhead as well. 1byte of user data seldom takes up only 1 byte in the database. – Marc B Sep 30 '11 at 21:29
  • 2
    At the very least, show us your table structure!! – marc_s Sep 30 '11 at 21:30
  • Thanks Martin...Sorry Marc_s for my last comment I pressed enter and my comment was send. Well first I tried with BULK INSERT with defaults values for the size batch. Now I try this: INSERT INTO MYDESTINY_TABLE SELECT TOP 1000 * FROM MYSOURCE_TABLE and the problem continue. – Arthur HG Sep 30 '11 at 21:55
  • 2
    @ArthurHG - Can you post your CREATE TABLE definition? – Martin Smith Sep 30 '11 at 21:58
  • Is your DB recovery model set to full or simple? – StanleyJohns Oct 3 '11 at 0:36
1

If the database is set to "simple" mode, then the transaction log is truncated after the transaction. Because you are only inserting 1000 records at a time, the transaction never needs to grow the entire amount.

Improve this answer
1
  • 2
    +1 And if the database was in Simple mode, but then you switched it to Full - but haven't yet taken a full backup, it'll still be in simple mode. (catches me out occasionally) – Peter Schofield Oct 3 '11 at 17:46

Your Answer

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