Something I ran into just recently. I have pretty big - 7Gb - table on Server1 that needs to be transfered to Server2 via linked server from Server2 to Server1.
INSERT INTO MyTable
SELECT * FROM Server1.MyDB.dbo.MyTable
I have a simple select that gives me datafile data for an active db:
For SQL Server 2000 (BTW, works in SQL2005 too):
SELECT
filegroup_name(groupid) as Filegroup,
[name],
[filename],
[size]/128.0 as TotalSizeInMB,
FILEPROPERTY([name], 'SpaceUsed')/128.0 as SpaceUsedInMB,
([size] - FILEPROPERTY([name], 'SpaceUsed'))/128.0 as FreeSpaceInMB
FROM sysfiles
For SQL Server 2005:
SELECT
[name],
[physical_name],
[size]/128.0 as TotalSizeInMB,
FILEPROPERTY([name], 'SpaceUsed')/128.0 as SpaceUsedInMB,
([size] - FILEPROPERTY([name], 'SpaceUsed'))/128.0 as FreeSpaceInMB,
CASE
WHEN is_percent_growth = 1 THEN CAST(growth as VARCHAR(8)) + '%'
ELSE CAST(FLOOR(growth/128.0) as VARCHAR(8)) + 'MB'
END as growth,
[is_read_only]
FROM sys.database_files
ORDER BY [Name] ASC
When I execute my query in SQL Server 2000 database, datafile and log in the MyTable immidiately start to grow. When I execute it on SQL Server 2005 db (while Server1 is SQL2000 - I didn't test it on two SQL2005 servers but I would be surprised if that matters), datafiles in my db don't grow. What grows instead is tempdb! I guess it has something to do with version stores though snapshot isolation is turned off.
Update: After some thought seems that I begin to understand the issue. In SQL 2000 deleted and inserted tables (which are used in triggers) aren't actually tables but are derived from transaction log. In SQL 2005 on the other hand deleted and inserted are managed by tempDB via version store mechanism. So TempDB grows as a substitute to transaction log.