Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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 Marc_s!!! –  Arthur HG Sep 30 '11 at 21:39
    
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
show 1 more comment

migrated from stackoverflow.com Oct 2 '11 at 14:40

This question came from our site for professional and enthusiast programmers.

1 Answer

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.

share|improve this answer
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.