USE MASTER
GO
IF OBJECT_ID('dbo.sp_ShrinkSimpleLog') IS NULL
EXEC ('CREATE PROCEDURE dbo.sp_ShrinkSimpleLog AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_ShrinkSimpleLog
@MinFreePercent NUMERIC(6, 2) = 40,
@MaxFreeGb NUMERIC(12, 2) = 2,
@PrintInfo BIT = 1,
@Exec BIT = 1,
@SQL VARCHAR(MAX) = NULL OUTPUT
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF OBJECT_ID('tempdb..#Result') IS NOT NULL
DROP TABLE #Result
CREATE TABLE #Result ([database] SYSNAME NOT NULL,
[recovery_model] VARCHAR(10) NOT NULL,
[filegroup] SYSNAME NULL,
[file] SYSNAME NOT NULL,
[physicalname] VARCHAR(260) NOT NULL,
[path] VARCHAR(260) NULL,
[sizegb] NUMERIC(12, 4) NULL,
[usedgb] NUMERIC(12, 4) NULL,
[freegb] NUMERIC(12, 4) NULL,
[freegb%] NUMERIC(12, 4) NULL)
SELECT @SQL = Util.dbo.StringConcat('
USE ' + name + '
INSERT #Result
SELECT
''' + name + ''' AS [database],
''' + recovery_model_desc + ''' AS recovery_model,
fg.name AS filegroup,
mf.name AS [file],
mf.physical_name as physicalname,
SUBSTRING(mf.physical_name, 1, LEN(mf.physical_name) - CHARINDEX(''\'', REVERSE(mf.physical_name))) AS path,
CAST(mf.size *8.0/1024/1024 AS NUMERIC(12,4)) AS sizegb,
CAST(FILEPROPERTY(mf.name, ''SpaceUsed'')/128.000 / 1024 AS NUMERIC(12,4)) AS usedgb,
CAST((mf.size - FILEPROPERTY(mf.name, ''SpaceUsed''))/128.000 / 1024 AS NUMERIC(12,4)) AS freegb,
CAST((mf.size - FILEPROPERTY(mf.name, ''SpaceUsed'')) * 100.00 / mf.size AS NUMERIC(12,4)) AS [freegb%]
FROM [' + name + '].sys.database_files mf (NOLOCK)
LEFT OUTER JOIN [' + name + '].sys.data_spaces ds (NOLOCK) ON mf.data_space_id = ds.data_space_id
LEFT OUTER JOIN [' + name + '].sys.filegroups fg (NOLOCK) ON ds.data_space_id = fg.data_space_id
WHERE fg.name IS NULL', '
')
FROM master.sys.databases d (NOLOCK)
WHERE is_read_only = 0
AND recovery_model_desc = 'SIMPLE'
AND STATE = 0
AND NAME NOT IN ('master', 'model', 'tempdb', 'msdb')
AND NOT EXISTS ( SELECT *
FROM sys.dm_tran_locks t1 (NOLOCK)
WHERE resource_type = 'database'
AND request_status = 'grant'
AND request_mode = 'u'
AND t1.resource_database_id = d.database_id )
EXEC(@SQL)
DELETE FROM #Result
WHERE NOT ([freegb%] >= @MinFreePercent
AND freegb > @MaxFreeGb)
IF EXISTS ( SELECT *
FROM #Result )
BEGIN
IF @PrintInfo = 1
SELECT *
FROM #Result
SELECT @SQL = Util.dbo.StringConcat('USE [' + [database] + ']
DBCC SHRINKFILE (N''' + [file] + ''' , 0, TRUNCATEONLY)', '
')
FROM #Result
IF @PrintInfo = 1
PRINT @SQL
IF @Exec = 1
AND @SQL <> ''
EXEC(@SQL)
END
GO
EXEC sys.sp_ms_marksystemobject
sp_ShrinkSimpleLog
GO