Sql-server – SQL Server 2008 R2 : LDF file size not increasing

sql serversql-server-2008-r2

I am using SQL Server 2008 R2. I have a table statDB of 100 GB in primary filegroup.

I have created a secondary filegroup in same database (Lab1) and created a table copyStatDB.

Now I start copy table data from primary file group to secondary file group.

I have noticed that none of my TempDB size change and not my .LDF file size change.

I am surprised to see that because as per my understanding when we execute a Insert statement it should increase .LDF file size increase first then copy data to my .NDF file.

Best Answer

I wouldn't expect a copy operation to increase file sizes unless the file size was too small to begin with. SQL Server will first use unallocated space within data files before growing the file. Similarly, if the log file is large enough for the operation, that file shouldn't grow either.

Also regarding log space usage, some operations can be minimally logged to reduce logging requirements. Whether or not your INSERT...SELECT is fully logged depends on the database recovery model and indexes on the target table.

Related Question