Save Results of DBCC ShrinkFile

Question

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

Answer 1

The easiest way I see it is a batch file using sqlcmd:

sqlcmd -S .\SQL2008R2 -E -Q "dbcc loginfo" >> log.txt

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

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

Answer 2

You can output the query results to file by pressing Ctrl+Shift+F if you're looking at one-off query.

If you're looking for something you can automate you can wrap the query in Powershell or another scripting language and have that write the file.

Answer 3

There are two options that are simple enough: either use a Linked Server or SQLCLR.

Linked Server

This option requires three things:

1. that the 'remote proc transaction promotion' property of the Linked Server be set to false, which prevents SQL Server from trying to use MSDTC to tie the local and remote actions together into a single transaction.

2. that the 'rpc out' property of the Linked Server to true so that the result set returned by DBCC can be passed back to the local context.

3. and that you use the Linked Server via EXEC('DBCC...') AT [LinkedServerName];

For a working example of this approach, please see my answer (also here on DBA.SE) to:

How can I save the results of DBCC SHRINKFILE into a table?

SQLCLR

Using a regular / external connection (i.e. not the Context Connection) that has the Enlist connection string keyword set to false, you can create either a SQLCLR Stored Procedure or Scalar Function / UDF. Just like with the Linked Server with 'remote proc transaction promotion' disabled, the new connection with Enlist = false; will not attempt to bind the local and remote transactions together.

While a UDF does lend itself more to being used in set-based operations, the simplest approach (I believe, but have not tested) would be to create a Stored Procedure wherein you pass SqlCommand.ExecuteReader() (where the SqlCommand object is the call to DBCC) into SqlContext.Pipe.Send().