Sql-server – File MDF growth SQL Server 2008

sql-server-2008

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?

Best 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.

Related Solutions

Sql-server – Restore bak file to smaller mdf and ldf database files

The backup contains two pieces of information:

the database files layout and metadata, ie. RESTORE FILELISTONLY ...
the actual data

The actual data in the backup is those 1.8 GB. But the metadata describes the layout of files as on the original production server. While it looks like compression, is not actual compression. Is just metadata vs. content. the problem is that during RESTORE the reporting machine creates a layout as described in the metadata, therefore 114.6GB + 24.8GB. Now for the real question: can you restore it w/o recreating the original DB file layout? No. However, there is another solutions to your problem: bring the original DB back in check. Shrinking the Transaction Log covers the steps you need to do, since the log is your major pain point. Next I would deploy log shipping instead of backup/restore.

Sql-server – SQL Server 2008 Database growth issue.

You can shrink an MDF file using:

USE [Databasename]
GO
DBCC SHRINKDATABASE(N'Databasename' )
GO

and you can shrink an LDF file using:

USE  Data base 
GO
DBCC SHRINKFILE(databsename_Log, 1)
GO

Best Answer

Related Solutions

Sql-server – Restore bak file to smaller mdf and ldf database files

Sql-server – SQL Server 2008 Database growth issue.

Related Question