Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
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
add comment

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

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.