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