Is it possible to shrink a log file on a linked server using DBCC SHRINKFILE?

Question

I am doing a large archival of data from one DB to another DB on a linked server. I am copying from MS sql server 2000 to MS sql server 2005. This involves copying millions of records and then deleting them. This all happens via a dynamic script. I would like to incorporate the shrinking of the log file for this process, but I am unable to get the DBCC SHRINKFILE to work for the linked server. Is this even possible?

Answer 1

You should be able to do it by calling sp_executesql through the linked server:

EXEC [LINKED_SERVER].MyDatabase.dbo.sp_executesql
    @statement = N'DBCC SHRINKFILE(''LogFileName'',0)'

You should probably rethink automatically shrinking the log file, though, especially if this script will be run so often that you need to automate it. You'll be better off backing up the log or switching the recovery model to Simple so you can reuse the space. (If it's going to grow back to the same size, did you really accomplish anything by shrinking it? In fact, you can make things worse by inadvertently introducing filesystem fragmentation.)

I'm not saying you never need to shrink a file programmatically, but wanting to do so is usually a sign something else is wrong -- the disk is too small for your expected volume, you have the wrong recovery model, etc.