Sql-server – Shrinking large DB file not working

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.

Are you shrinking before backup in the hope that it will reduce your backup time? If so that will not make any difference. But if you are doing it so it allocates less space on your target (when you restore) then it will help.

I did not understand if you are doing this every time before backup. If yes you want to resolve this permanently and not do this.

Now to answering your question.

Is the results of the shrink sitting out in tempdb somewhere ready to go?

It does not have to keep the result. It will check everything again once you restart.

To get this shrink done a little quicker, does anyone think running a larger shrinkfile during the backup that I know won't complete is a good idea?

No, because it does 32 pages at one time and commit. See below.

Reference: https://blogs.msdn.microsoft.com/psssql/2008/03/28/how-it-works-sql-server-2005-dbcc-shrink-may-take-longer-than-sql-server-2000/

Shrink works in transaction batches of ~32 pages. Once 32 pages are moved below the truncation point the transaction is committed and a new transaction started. This avoids an older issue with shrink* that kept a transaction open for a long time and caused the log files to grow because you can’t truncate past an active transaction. It also makes shrink* restart capable. If you cancel/kill a shrink* operation only the current active transaction is rolled back. This means you could schedule a shrink* operation during a maintenance window and limit its runtime to that maintenance window. You can restart it during the next maintenance window and it will pick up from where it left off. The only disadvantage to this is that allocations could occur after the target once the shrink is terminated. This may require shrink to do additional work for newly allocated space but this should be reasonably limited.

On a side note backing up 2TB should not take a full day. There are switches in native backup, can speed up/optimize your backup duration.