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 + '''' + ',' + CAST(@amount AS VARCHAR) + ', TRUNCATEONLY) WITH NO_INFOMSGS' + CHAR(10)
+ 'GO' + CHAR(10)
, *
FROM sys.master_files smf
WHERE smf.database_id = @dbid
The script above produced the following results:
USE [MY_DATABASSEStage]
GO
DBCC SHRINKFILE('MY_DATABASSEStageStockLedger',0, TRUNCATEONLY) WITH NO_INFOMSGS
GO
USE [MY_DATABASSEStage]
GO
DBCC SHRINKFILE('MY_DATABASSEStageStockLedgerArchive',0, TRUNCATEONLY) WITH NO_INFOMSGS
GO
USE [MY_DATABASSEStage]
GO
DBCC SHRINKFILE('MY_DATABASSEStageProduct',0, TRUNCATEONLY) WITH NO_INFOMSGS
GO
here is the result of one of the scripts - after removing the with no_infomsgs
there are similar questions but they don't give an answer that apply to my case:
Shrink logs in between user transaction in SQL Server
Save Results of DBCC ShrinkFile
Conclusion
My answer works fine, but the method used by srutzky is much simpler and does the job, just require a linked server.
for my test server I am using the following script to create the linked server:
USE [master]
GO
IF NOT EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'TESTDBCC')
BEGIN
EXEC master.dbo.sp_addlinkedserver
@server = N'TESTDBCC',
@srvproduct=N'',
@provider=N'SQLNCLI11',
@datasrc=N'JPB01275\SQL2014',
@catalog=N'radhe'
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname=N'TESTDBCC',
@useself=N'True',
@locallogin=NULL,
@rmtuser=NULL,
@rmtpassword=NULL
END
GO
EXEC master.dbo.sp_serveroption
@server=N'TESTDBCC',
@optname=N'collation compatible',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTDBCC',
@optname=N'data access',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTDBCC',
@optname=N'dist',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTDBCC',
@optname=N'pub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTDBCC',
@optname=N'rpc',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTDBCC',
@optname=N'rpc out',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTDBCC',
@optname=N'sub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTDBCC',
@optname=N'connect timeout',
@optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTDBCC',
@optname=N'collation name',
@optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'TESTDBCC',
@optname=N'lazy schema validation',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTDBCC',
@optname=N'query timeout',
@optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTDBCC',
@optname=N'use remote collation',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'TESTDBCC',
@optname=N'remote proc transaction promotion',
@optvalue=N'false'
GO
and using the linked server above I can collect the data as follows:
--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];
INSERT INTO #ShrinkFileResults
([DBID], [FileID], [CurrentSize], [MinimumSize], [UsedPages], [EstimatedPages])
EXEC(N'
USE [radhe];
DBCC SHRINKFILE(N''radhe_log'', 0, TRUNCATEONLY);
') AT [TestDBCC];
INSERT INTO #ShrinkFileResults
([DBID], [FileID], [CurrentSize], [MinimumSize], [UsedPages], [EstimatedPages])
EXEC(N'
USE [US16HSMMProduct_AFTER_CHANGES];
DBCC SHRINKFILE(N''US16HSMMProduct_AFTER_CHANGES_log'', 0, TRUNCATEONLY);
') AT [TestDBCC];
INSERT INTO #ShrinkFileResults
([DBID], [FileID], [CurrentSize], [MinimumSize], [UsedPages], [EstimatedPages])
EXEC(N'
USE [US16HSMMProduct_AFTER_CHANGES];
DBCC SHRINKFILE(N''US16HSMMProduct_AFTER_CHANGES_log'', 0, TRUNCATEONLY);
') AT [TestDBCC];
SELECT * FROM #ShrinkFileResults;
