3

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
1
  • Side note: I don't wanna point the finger, but why in the world would you wanna script the log file shrinking? It's not an action that should be automated, as I think of its purpose.
    – Marian
    Commented Oct 10, 2012 at 9:49

3 Answers 3

2

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.

1

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.

2
  • I am trying to script this, as I don't want to login to the Database Server every time I want to execute this. I'm hoping the scripting can be done in SSMS. Commented Oct 9, 2012 at 18:49
  • Okay. Wrap it in Powershell. You can use invoke-sqlcmd and then use Powershell to create the file. If you're more comfortable with VBScript or another language (or even SSIS) that can be used too. Commented Oct 9, 2012 at 18:51
0

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().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.