Save Results of DBCC ShrinkFile When we execute the following command:

DBCC SHRINKFILE('MyDB_log', 1)


We get the following results in SSMS:

DBID | Field | CurrentSize | MinimumSize | UsedPages | Estimated Pages
-----|-------|-------------|-------------|-----------|----------------
 11  |   2   |    128      |   128       |  128      |     128


The Question:

How can we create a query that outputs these results to a text file, without using SSMS output window.

I have tried this:

CREATE TABLE #x
(
    [DBID] int,
    FileID int,
    CurrentSize int,
    MinimumSize int,
    UsedPages int,
    EstimatedPages int
)
INSERT #x 
  EXEC('DBCC SHRINKFILE(''MyDB_log'', 1)')

SELECT * 
  FROM #x

DROP TABLE #x


But I get the following error:

Msg 8920, Level 16, State 2, Line 1
Cannot perform a shrinkfile operation inside a user transaction. Terminate the transaction and reissue the statement.


I have also tried the following:

DECLARE @Statement AS VARCHAR(2000); 
SET @Statement = 'bcp "DBCC SHRINKFILE(''MyDB_log'', 1)" queryout C:\Test.txt -c -UDBAdmin -P1234 -S192.168.123.123';

exec xp_cmdshell @Statement


I get:

Error = [Microsoft][SQL Native Client]BCP host-files must contain at least one column
The easiest way I see it is a batch file using sqlcmd:

sqlcmd -S .\SQL2008R2 -E -Q "dbcc loginfo" &gt;&gt; log.txt


The redirect operator appends data at the end of the file.

-Q parameter was used for closing the sqlcmd session immediately.