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

I have the following script:

-- available space in each of the database files

PRINT @@SERVERNAME
--SQLDWDEV01
USE [BodenStage]
GO
SELECT name 
,(CAST(ROUND((size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0),2) AS NUMERIC(18,2))) AS AvailableSpaceInMB  
,(CAST(ROUND((size/128.0 - FILEPROPERTY(name, 'SpaceUsed')/128.0)/1024.00,2) AS NUMERIC(18,2))) AS AvailableSpaceInGB 
FROM sys.database_files;  
GO

and as much as I don't like shrinking neither data nor log files, sometimes it is needed. Not my fault.

There are ways to shrink data files without accruing fragmentation as described here:


TRUNCATEONLY


Releases all free space at the end of the file to the
operating system but does not perform any page movement inside the
file. The data file is shrunk only to the last allocated extent.

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

Please see below it is one way of saving the results of DBCC SQLPERF into a table.

select @@servername as [Server]
       ,db_name() as [database]
go

SET NOCOUNT ON

begin try
  drop table #radhe
end try
begin catch
end catch

create table #radhe (
  DatabaseName varchar(100)
  , LOGSIZE_MB NUMERIC(18, 9)
  , LOGSPACE_USED NUMERIC(18, 9)
  , LOGSTATUS NUMERIC(18, 9)) 

INSERT #Radhe(DatabaseName, LOGSIZE_MB, LOGSPACE_USED, LOGSTATUS) 
EXEC('DBCC SQLPERF(LOGSPACE);')

select  DatabaseName, 
     LOGSIZE_GB=CONVERT(NUMERIC(18,2) ,ISNULL( ROUND(LOGSIZE_MB/1024.00,2),0)),
     PERC_USED =CONVERT(NUMERIC(4,2) ,ISNULL( ROUND(LOGSPACE_USED,2),0))
from #radhe
order by logsize_mb desc

This is now I generate the DBCC SHRINKFILE commands:

```
DECLARE @db VARCHAR(108)
DECLARE @dbid INT
DECLARE @amount INT

SELECT @db = 'MY_DATABASSEStage'
SELECT @dbid = DB_ID(@DB)
SELECT @amount = 0

SELECT SHRINKFILE_SCRIPT = 'USE ' + QUOTENAME( DB_NAME(smf.database_id)) + CHAR(10)
+ 'GO' + CHAR(10)
+ 'DBCC SHRINKFILE(''' + smf.name + '''' + ',' + CA

This can be accomplished somewhat easily by using a "loop back" Linked Server that has the 'remote proc transaction promotion' property set to false, which avoids the Cannot perform a shrinkfile operation inside a user transaction error by side-stepping the implicit transaction started by the INSERT...EXEC operation. The Linked Server will otherwise only require that the 'rpc out' property be set to true. The 'data access' and 'rpc' properties do not need to be enabled (at least not for me).

Create the Linked Server

USE [master];

DECLARE @LinkedServerName sysname;
SET @LinkedServerName = N'TestDBCC';

IF (NOT EXISTS (SELECT srv.name
                FROM   sys.servers srv
                WHERE  srv.server_id <> 0
                AND    srv.name = @LinkedServerName))
BEGIN
  EXEC [master].dbo.sp_addlinkedserver
    @server = @LinkedServerName, @srvproduct=N'', @provider=N'SQLNCLI11', @datasrc=N'DALI';
  EXEC [master].dbo.sp_addlinkedsrvlogin
    @rmtsrvname=@LinkedServerName,@useself=N'True',@locallogin=NULL,
    @rmtuser=NULL,@rmtpassword=NULL;
END;

--EXEC [master].dbo.sp_serveroption @server=@LinkedServerName,
--  @optname=N'data access', @optvalue=N'true';
--EXEC [master].dbo.sp_serveroption @server=@LinkedServerName,
--  @optname=N'rpc', @optvalue=N'true'; -- is_remote_login_enabled
EXEC [master].dbo.sp_serveroption @server=@LinkedServerName,
  @optname=N'rpc out', @optvalue=N'true';
EXEC [master].dbo.sp_serveroption @server=@LinkedServerName,
  @optname=N'remote proc transaction promotion', @optvalue=N'false';
GO

Use the Linked Server via EXEC() AT

CREATE TABLE #ShrinkFileResults
(
    [DBID] SMALLINT,
    [FileID] INT,
    [CurrentSize] INT,
    [MinimumSize] INT,
    [UsedPages] INT,
    [EstimatedPages] INT
);

INSERT INTO #ShrinkFileResults
  ([DBID], [FileID], [CurrentSize], [MinimumSize], [UsedPages], [EstimatedPages])
  EXEC(N'
     USE [tempdb];
     DBCC SHRINKFILE(N''tempdev'', 0, TRUNCATEONLY);
  ') AT [TestDBCC];

SELECT * FROM #ShrinkFileResults;

This worked for me on both SQL Server 2012 SP3 and SQL Server 2016 RTM.

NOTES

-
Using OPENQUERY does not work. Initially you get a "meta-data discovery" error which can be resolved by wrapping the DBCC call in another EXEC that uses the WITH RESULT SETS option. But then you get an error about "OPENQUERY cannot process object as it either does not exist or you don't have permission to it". But if you change the logical name of the file to shrink, then you get an error from DBCC stating that it cannot find a file of that name, along with a secondary error from OPENQUERY stating that the call did not return any result sets, all of which indicates that with the correct file name it is get farther.

-
This operation can also be rather simply accomplished via SQLCLR. One can create a UDF / Scalar function, in fact, using an external connection (not a Context Connection), which would allow for being called in a set-based operation in the SELECT list. That would also only take a few lines of code, and minimize security risk when compared to enabling xp_cmdshell or creating a Linked Server.

StackExchange Database Administrators Q#162084, answer score: 3