Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

When we execute the following command:


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:

    [DBID] int,
    FileID int,
    CurrentSize int,
    MinimumSize int,
    UsedPages int,
    EstimatedPages int
  EXEC('DBCC SHRINKFILE(''MyDB_log'', 1)')

  FROM #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
share|improve this question
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 Oct 10 '12 at 9:49
up vote 2 down vote accepted

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.

share|improve this answer

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.

share|improve this answer
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. – Onion-Knight Oct 9 '12 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. – cfradenburg Oct 9 '12 at 18:51

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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