Tag Archives: How To

How to set every database in a SQL Server instance to read only

The following TSQL when executed will create the TSQL needed to set all databases in a SQL Server instance to read only mode.

Typical use cases for this might include creating a copy of databases for reporting purposes or when migrating databases from one server to another.

SELECT 'ALTER DATABASE [' + NAME + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [' + NAME + '] SET READ_ONLY WITH NO_WAIT
ALTER DATABASE [' + NAME + '] SET MULTI_USER
GO'
FROM sys.databases
/*Setting database id > 4 excludes the system databases*/
WHERE database_id > 4

 

Before you run the outputted script you may need to kill open connections to the databases or certain running activities. Obviously you would want to make sure it is safe to do so before killing any activity.

To kill activities on mass you can use the script in this post.

 

 

How to create a job that tests whether SQL Server database mail is working

The following script will create a job that will run every minute to test if database mail can be sent from a job scheduled to run by the Sql Server Agent.

Simply find and replace the email address below with the email address you want to target:

testoperator@mail.com

Then run the script.

The operator ‘Test Operator’ and job ‘MailTest’ will be created.

The job is disabled by default, enable it to begin testing.

When you are finished run the commented out section at the bottom of the script to remove the test operator and job.

If you have just setup database mail for the first time the SQL Server Agent will need to be restarted.

/*
FIND AND REPLACE

testoperator@mail.com

*/
USE msdb;
GO

EXEC dbo.sp_add_operator @name = N'Test Operator'
	,@enabled = 1
	,@email_address = N'testoperator@mail.com'
GO

USE [msdb]
GO

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 31/07/2019 11:35:43 ******/
IF NOT EXISTS (
		SELECT NAME
		FROM msdb.dbo.syscategories
		WHERE NAME = N'[Uncategorized (Local)]'
			AND category_class = 1
		)
BEGIN
	EXEC @ReturnCode = msdb.dbo.sp_add_category @class = N'JOB'
		,@type = N'LOCAL'
		,@name = N'[Uncategorized (Local)]'

	IF (
			@@ERROR <> 0
			OR @ReturnCode <> 0
			)
		GOTO QuitWithRollback
END

DECLARE @jobId BINARY (16)

EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name = N'MailTest'
	,@enabled = 0
	,@notify_level_eventlog = 0
	,@notify_level_email = 3
	,@notify_level_netsend = 0
	,@notify_level_page = 0
	,@delete_level = 0
	,@description = N'No description available.'
	,@category_name = N'[Uncategorized (Local)]'
	,@owner_login_name = N'sa'
	,@notify_email_operator_name = N'Test Operator'
	,@job_id = @jobId OUTPUT

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

/****** Object:  Step [Step 1]    Script Date: 31/07/2019 11:35:44 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @jobId
	,@step_name = N'Step 1'
	,@step_id = 1
	,@cmdexec_success_code = 0
	,@on_success_action = 1
	,@on_success_step_id = 0
	,@on_fail_action = 2
	,@on_fail_step_id = 0
	,@retry_attempts = 0
	,@retry_interval = 0
	,@os_run_priority = 0
	,@subsystem = N'TSQL'
	,@command = N'SELECT 1'
	,@database_name = N'master'
	,@flags = 0

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId
	,@start_step_id = 1

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId
	,@name = N'Job Schedule'
	,@enabled = 1
	,@freq_type = 4
	,@freq_interval = 1
	,@freq_subday_type = 4
	,@freq_subday_interval = 1
	,@freq_relative_interval = 0
	,@freq_recurrence_factor = 0
	,@active_start_date = 20190731
	,@active_end_date = 99991231
	,@active_start_time = 0
	,@active_end_time = 235959
	,@schedule_uid = N'f0741db6-488e-44da-8f5e-a3f0ed13835e'

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId
	,@server_name = N'(local)'

IF (
		@@ERROR <> 0
		OR @ReturnCode <> 0
		)
	GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

IF (@@TRANCOUNT > 0)
	ROLLBACK TRANSACTION

EndSave:
GO

/*
REMOVE OPERATOR AND JOB
*/
/*
USE msdb;
GO

EXEC sp_delete_operator @name = 'Test Operator';

EXEC sp_delete_job @job_name = N'MailTest';

GO
*/

 

How to fix the SQL Server Job Error “Failed to decrypt an encrypted XML node because the password was not specified or not correct” encountered with SSIS packages

If you have a password protected SSIS package you may encounter this error.

First off this seems like a very buggy problem and the solution may not work for everyone. Also, as typical, you may find that you are trying to solve more than one problem at once, for instances going from one domain to another seems to confuse matters. So if this solution doesn’t work it may be just part of the solution to your particular puzzle.

If you go to the job step properties you actually see that there are two spaces between /DECRYPT and /CHECKPOINTING.

CommandLine

If you reduce the space to one and run the job again you might get this error:

“Enter decryption password: Missing argument for option “decrypt””

This is because you’ve removed the password from the job associated with the package.

I’ve found that when you edit the command manually by first removing the spaces and then retyping the command with the package password this solves the error.

EditCommand

For example:

/DECRYPT YourPackagePassword /CHECKPOINTING

If this doesn’t work try scripting out the job, deleting it, and then running the script again.

Good Luck.

 

 

How to get SQL Server Network Information using SSMS

The following code will work for a remote client request to SQL 2008 and newer.

Note: The local machine address (local_net_address) is that of the SQL Server while client_net_address is the address of the remote computer you have used to make the request. 

SELECT @@SERVERNAME AS ServerName
	,CONNECTIONPROPERTY('net_transport') AS net_transport
	,CONNECTIONPROPERTY('protocol_type') AS protocol_type
	,CONNECTIONPROPERTY('auth_scheme') AS auth_scheme
	,CONNECTIONPROPERTY('local_net_address') AS local_net_address
	,CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port
	,CONNECTIONPROPERTY('client_net_address') AS client_net_address
 

 

How to set a database to single user and back to multiuser

Here’s a quick script to set a database to single user and then back to multi user.

You’ll need to do this when altering the database in situations like database name changes, file name changes or even restores 

/*
Set Database to Single User
*/
USE [master];
GO

ALTER DATABASE [my_db]

SET SINGLE_USER
WITH

ROLLBACK IMMEDIATE;
GO

/*
Set Database to back to Multi User
*/
USE MASTER;

ALTER DATABASE [my_db]

SET MULTI_USER
WITH

ROLLBACK IMMEDIATE;
GO

 

How to get the date a web page was published

This posting is particularly useful for anyone looking to use the date a web page was published as part of academic referencing.

Step 1. In your browser open https://www.google.com/.

Step 2. Copy the url of the web page you want to get a publication date for.

Step 3. Paste this url into the search bar of the google.com page and search. The search should return a series of results.

Step 4. In the address bar of the results page go to the very end of the url text and paste &as_qdr=y15 and hit return.

This should update the results page with each link including a publication date.

How to monitor file stats using a batch script

The use case for this batch script is to monitor SQL Server files using Windows task manager scheduled to run the file every 5 minutes. The batch scripts gathers the stats of  every file in a referenced directory. The script can obviously be adapted however to something that suits your specific needs.

Note: Running this script against the C drive on a laptop took 15 minutes and produced a text file that was around 50 Mb. The script is best utilized in scenarios such as monitoring file sizes on drives used by applications that do not generate thousands of files, e.g. backup directories etc.

There is two scripts presented below. The first is the simpler of the two scripts, it just records the file stats of a single directory.

Directions for use:

Take either script below and save it as a batch file called FileStatsLogger.

Two variables need to be updated to use the script:

“LogPath=C:\Log\” Change C:\Log\ to another directory if you want the output file written somewhere else.

“ScanDir=C:\FolderToScan\” Change C:\FolderToScan\ to the directory you want to gather file stats from.

The file that will be created, FileStats, will record the following information:

  • A timestamp of when the batch script was run
  • A reference to each parent directory
  • Every file creation date and time
  • File size in Kbs
  • Every file owner
  • The name of the file
  • How many files are in the directory and their collective size

The output file will look like this when opened.

FileStats

Each time the script is run it will update the text file FileStats.

@ECHO OFF

REM SET TIMESTAMP VARIABLE
SET Timestamp=%date:~6,8%%date:~3,2%%date:~0,2%%time:~0,2%%time:~3,2%%time:~6,2%

REM SET FILE LOGGING VARIABLES
SET "LogPath=C:\Log\"
SET "LogFileName=FileStats.txt"
SET "Log=%LogPath%%LogFileName%"
SET "ScanDir=C:\FolderToScan\"

REM CREATE FILE IF DOES NOT EXIST
IF NOT EXIST "%Log%" ECHO File Created Timestamp: %Timestamp% > "%Log%"

REM TIMESTAMP ENTRY
ECHO/ >> "%Log%"
ECHO Entry Timestamp: %Timestamp% >> "%Log%"
ECHO/ >> "%Log%"

REM SCAN DIRECTORY
DIR /a %ScanDir% >> %Log%

To scan multiple directories create a text file called DirectoryList and populate the the file with the directories you want to scan. Each directory on a new line, for example:

L:\
D:\
T:\

It is assumed the directory list text file will be kept in the same directory as the batch file FileStatsLogger but you should add the full file path to were DirectoryList.txt is referenced in the script to avoid any problems.

Note: L, D & T are the naming convention for the Log, Data and tempdb drive names for SQL Server. These are just drive letters examples but you can be more specific like C:\Users\UserName\Desktop\ just be sure to include the backslash at the end of the reference.

This script will create a new log file for each day as the files can be large. The filename will following the format FileStats_YYYYMMDD.

As before you will have to:

“LogPath=C:\FileStats\” Change C:\Log\ to another directory if you want the output file written somewhere else.

“ScanDir=C:\FileStats\DirectoryList.txt” Change to the directory you will store DirectoryList.txt.

Though it is recommended you keep everything in a folder like this (potentially writing the FileStats log to a directory off of the host server):

FileStatsDirectory

@ECHO OFF

REM SET TIMESTAMP VARIABLES
for /f "tokens=2 delims==" %%G in ('wmic os get localdatetime /value') do set datetime=%%G
SET "dd=%datetime:~6,2%"
SET "mth=%datetime:~4,2%"
SET "yyyy=%datetime:~0,4%"
SET "hh=%time:~0,2%"
SET "mm=%time:~3,2%"
SET "ss=%time:~6,2%"

SET "FileNameDate=%yyyy%%mth%%dd%"
SET "Timestamp=%yyyy%%mth%%dd%%hh%%mm%%ss%"

REM SET FILE LOGGING VARIABLES
SET "LogPath=c:\FileStats\"
SET "LogFileName=FileStats_%FileNameDate%.txt"
SET "Log=%LogPath%%LogFileName%"


REM CREATE FILE IF DOES NOT EXIST
IF NOT EXIST "%Log%" ECHO File Created Timestamp: %Timestamp% > "%Log%"

REM TIMESTAMP ENTRY
ECHO/ >> "%Log%"
ECHO Entry Timestamp: %Timestamp% >> "%Log%"
ECHO/ >> "%Log%"

REM READ DIRECTORY LIST AND SCAN EACH DIRECTORY REFERENCED 
FOR /f "tokens=*" %%x IN (c:\FileStats\DirectoryList.txt) DO DIR /a /s /q "%%x" >> %Log%

Finally you can create a Windows task scheduler task to run the script at an interval that suits your needs.

The description of the task could be something along the lines of:

This task runs the batch script “FileStatsLogger” every 5 minutes which
logs the stats for each file of each directory referenced in the file
Directory List. The stats are recorded in the FileStats txt file.
All resources related to this tasks are contained in the FileStats directory.

How to find Missing Indexes for all databases in a SQL Server instance

This script is for SQL Server 2005 and up. The script will return all the missing indexes for a SQL Server instance, rating their impact and provide the T-SQL to create the missing indexes.

SQL Server 2005 was the first version of SQL Server to add DMV (Database Management View) and DMO (Database Management Objects) which this script requires to function.
DMV & DMO provide useful information about SQL Server like expensive queries, wait types, missing indexes etc.

WARNING!
Before you create the missing indexes on the referenced tables you must consider the following essential points:
• Find and assess all the queries that are using the table referenced. If the table has a heavy amount of Data Manipulation Language (DML) operations against it (SELECT, INSERT, UPDATE, or DELETE) then you must analyse what impact adding the missing index will have before you create it on the table. INSERTs on tables are slowed down by nonclustered indexes for example.
• You need to make sure that by creating the missing indexes you are not going to end up with duplicate indexes on tables. The duplicate or unwanted indexes can kill your database performance. Search for the blog “over-indexing can hurt your SQL Server performance” for more information.
• If you find there is already an existing index that has most of the columns of the missing index highlighted you should consider adding the missing columns to the current index rather than creating another index on the table. FYI making an index wider does not mean adding all columns from a table into the current index.

/*Script to find Missing Indexes for all databases in SQL Server*/
/*
This script is for SQL Server 2005 and up. 
The script will return all the missing indexes for a SQL Server instance, rating their impact 
and provide the T-SQL to create the missing indexes.

SQL Server 2005 was the first version of SQL Server to add DMV (Database Management View) 
and DMO (Database Management Objects) which this script requires to function. 
DMV & DMO provide useful information about SQL Server like expensive queries, wait types, missing indexes etc.

WARNING!
Before you create the missing indexes on the referenced tables you must consider the following essential points:
• Find and assess all the queries that are using the table referenced. If the table has a heavy amount of Data Manipulation Language (DML) 
operations against it (SELECT, INSERT, UPDATE, or DELETE) then you must analyse what impact adding the missing index will have before you create it on the table. 
INSERTs on tables are slowed down by nonclustered indexes for example.
• You need to make sure that by creating the missing indexes you are not going to end up with duplicate indexes on tables. 
The duplicate or unwanted indexes can kill your database performance. Search for the blog “over-indexing can hurt your SQL Server performance” for more information.
• If you find there is already an existing index that has most of the columns of the missing index highlighted you should consider adding the missing columns to 
the current index rather than creating another index on the table. FYI making an index wider does not mean adding all columns from a table into the current index.
*/
SELECT [EstIndexUses]
	,[EstIndexImpact%]
	,[EstAvgQueryCost]
	,[DbName]
	,[SchemaName]
	,[TableName]
	,[CreateIndex]
	,[EqualityColumns]
	,[InequalityColumns]
	,[IncludedColumns]
	,[UniqueCompiles]
	,[LastUserSeek]
FROM (
	SELECT migs.user_seeks AS [EstIndexUses]
		,migs.avg_user_impact AS [EstIndexImpact%]
		,migs.avg_total_user_cost AS [EstAvgQueryCost]
		,db_name(mid.database_id) AS [DbName]
		,OBJECT_SCHEMA_NAME(mid.OBJECT_ID, mid.database_id) AS [SchemaName]
		,OBJECT_NAME(mid.OBJECT_ID, mid.database_id) AS [TableName]
		,'CREATE INDEX [IX_' + OBJECT_NAME(mid.OBJECT_ID, mid.database_id) + '_' + REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, ''), ', ', '_'), '[', ''), ']', '') + CASE 
			WHEN mid.equality_columns IS NOT NULL
				AND mid.inequality_columns IS NOT NULL
				THEN '_'
			ELSE ''
			END + REPLACE(REPLACE(REPLACE(ISNULL(mid.inequality_columns, ''), ', ', '_'), '[', ''), ']', '') + ']' + ' ON ' + mid.statement + ' (' + ISNULL(mid.equality_columns, '') + CASE 
			WHEN mid.equality_columns IS NOT NULL
				AND mid.inequality_columns IS NOT NULL
				THEN ','
			ELSE ''
			END + ISNULL(mid.inequality_columns, '') + ')' + ISNULL(' INCLUDE (' + mid.included_columns + ') WITH (MAXDOP =?, FILLFACTOR=?, ONLINE=?, SORT_IN_TEMPDB=?);', '') AS [CreateIndex]
		,mid.equality_columns AS EqualityColumns
		,mid.inequality_columns AS InequalityColumns
		,mid.included_columns AS IncludedColumns
		,migs.unique_compiles AS UniqueCompiles
		,migs.last_user_seek AS LastUserSeek
	FROM sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
	INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK) ON migs.group_handle = mig.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK) ON mig.index_handle = mid.index_handle
	) AS a
WHERE 1 = 1
--AND [EstIndexUses] > 1000
--AND [EstIndexImpact%] > 10
--AND [EstAvgQueryCost] > 1
--AND DbName IN ('DatabaseName')
ORDER BY [EstIndexUses] DESC
	,[EstAvgQueryCost] DESC
	,[EstIndexImpact%] DESC
OPTION (RECOMPILE);

 

How to get the size of every index in a SQL Server Instance

The code below will run against every online database (excluding the system databases bar the master database) and return the size of each index in each database. Knowing these values is especially important as it is recommended that the free space available on disk should be at least 1.5 times the size of the largest index. This is only one guideline regarding disk space however and use cases vary from database to database.

The output will include:

  • Database Name
  • Schema Name
  • Table Name
  • Index Id
  • Index Name
  • Index Size Mb

You can uncomment the last three lines to focus on nonclustered indexes.

SET NOCOUNT ON;

DECLARE @Database TABLE (DbName SYSNAME);
DECLARE @IndexStats TABLE (
	ServerName SYSNAME
	,DbName SYSNAME
	,SchemaName SYSNAME
	,TableName SYSNAME
	,IndexId INT
	,IndexType VARCHAR(12)
	,IndexName SYSNAME
	,IndexSizeMb INT
	);
DECLARE @DbName AS SYSNAME;
DECLARE @Sql AS VARCHAR(MAX);

SET @DbName = '';

INSERT INTO @Database (DbName)
SELECT NAME
FROM sys.databases
WHERE NAME NOT IN (
		'tempdb'
		,'msdb'
		,'model'
		)
	AND state_desc = 'ONLINE'
ORDER BY NAME ASC;

WHILE @DbName IS NOT NULL
BEGIN
	SET @DbName = (
			SELECT MIN(DbName)
			FROM @Database
			WHERE DbName > @DbName
			);
	SET @Sql = 'USE ' + QUOTENAME(@DbName) + ';	
	SELECT @@ServerName AS ServerName
	,''' + @DbName + ''' AS ''DbName'' 
	,OBJECT_SCHEMA_NAME(i.OBJECT_ID) AS SchemaName
	,OBJECT_NAME(i.OBJECT_ID) AS TableName
	,i.index_id AS IndexId
	,CASE WHEN i.index_id > 1 THEN ''Nonclustered'' WHEN i.index_id = 1 THEN ''Clustered'' ELSE ''Heap'' END AS IndexType
	,CASE WHEN i.NAME IS NULL THEN ''No Name'' ELSE i.Name END AS IndexName
	,(8 * SUM(a.used_pages)/1024) AS ''IndexSizeMb''
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID
	AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE a.used_pages > 0
GROUP BY i.OBJECT_ID
	,i.index_id
	,i.NAME
ORDER BY IndexSizeMb DESC; 
'

	INSERT INTO @IndexStats
	EXEC (@Sql);
END

SELECT ServerName
	,DbName
	,SchemaName
	,TableName
	,IndexId
	,IndexType
	,IndexName
	,IndexSizeMb
FROM @IndexStats
--WHERE IndexType = 'Nonclustered'
--AND IndexSizeMb > 0
--ORDER BY IndexSizeMb DESC;

 

How to perform incremental database file shrinking on SQL Server

First off you don’t ever shrink a database file unless you known it won’t need the space back again. (There’s some caveats to that statement but it mostly holds true) If that’s the space the file has consumed generally that’s the space the file needs. Shrinking just means the file will grow again and SQL Server will take a performance hit while it does that. You’re better off just adding the additional disk space.

If you need to perform a shrink and you’re worried about how long it will take and the performance impact it will have you can perform shrinks in small chunks. This is good practice particularly in a production transactional (OLTP) system as small increments  can make continuous, incremental progress, instead of trying to shrink by a large amount in one command. This makes it easier to shrink a database that must be shrunk by a large amount, and allows the shrink process to interrupted without losing all progress.

The script below can be used to shrink a database file in small increments until it reaches a target free space size based on the current free space percentage.

Simply provide as variables the database name, whether the file to be shrunk is the rows or log file, how much space to be left in terms of a percentage and how many shrinks you wish to perform. If you only want to truncate the file set the flag to 1 and you can ignore providing values for the number of shrinks or the percentage of space to keep.

(TruncateOnly means releasing all free space at the end of the file to the operating system without performing any page movement inside the file)

The script will determine given the number of shrinks and the target size what the shrink increment should be. It will then loop and execute the DBCC SHRINKFILE command to shrink the database file by the calculated increment until it reaches the target free space.

Progress updates are written to the global temp table ##DbStats so you can query this table during the shrink to see how far along the process is. The query to perform this check is commented out at the bottom of the script.

SET NOCOUNT ON;

DECLARE @DbName SYSNAME;
DECLARE @DbFileName SYSNAME;
DECLARE @RowsOrLog VARCHAR(4);
DECLARE @SpaceToLeavePercentage VARCHAR(2);
DECLARE @Sql VARCHAR(MAX);
DECLARE @ShrinkSql VARCHAR(MAX);
DECLARE @NumberOfShrinks INT;
DECLARE @FreeMbRounded INT;
DECLARE @TargetFreeMbRounded INT;
DECLARE @ShrinkIncrementInMb INT;
DECLARE @ShrinkToInMb INT;
DECLARE @TruncateOnly BIT;

/*SET USER INPUT VARIABLES*/
SET @DbName = '_DatabaseName_';
SET @RowsOrLog = 'Rows';
SET @SpaceToLeavePercentage = '10';
SET @NumberOfShrinks = 1;

--SET @TruncateOnly = 1;
IF OBJECT_ID('tempdb..##DbStats') IS NOT NULL
BEGIN
	DROP TABLE ##DbStats
END;

IF OBJECT_ID('tempdb..#Check') IS NOT NULL
BEGIN
	DROP TABLE #Check
END;

CREATE TABLE ##DbStats (
	Id_DbStats INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,LogicalName SYSNAME
	,FreeMbRounded INT
	,TargetFreeMbRounded INT
	,DiffMb INT
	,ShrinkIncrementInMb INT
	,NumberOfShrinksLeft INT
	);

CREATE TABLE #Check (
	Id_DbStats INT NOT NULL IDENTITY(1, 1) PRIMARY KEY
	,LogicalName SYSNAME
	,FreeMbRounded INT
	,TargetFreeMbRounded INT
	,DiffMb INT
	,ShrinkIncrementInMb INT
	,NumberOfShrinksLeft INT
	);

IF @SpaceToLeavePercentage IS NULL
BEGIN
	SET @SpaceToLeavePercentage = '10'
END

SET @Sql = '
USE ' + QUOTENAME(@DbName) + ';
SELECT LogicalName
,FreeMbRounded
,TargetFreeMbRounded
,FLOOR(FreeMbRounded) - FLOOR(TargetFreeMbRounded) AS DiffMb
FROM (
SELECT NAME AS LogicalName
	,CAST(ROUND(CONVERT(DECIMAL(12, 1), (size - FILEPROPERTY(NAME, ''SpaceUsed'')) / 128.0), 0) AS INT) AS FreeMbRounded
	,CAST(ROUND(CONVERT(DECIMAL(12, 1),((((size - FILEPROPERTY(NAME, ''SpaceUsed'')) / 128.0) / 100.0) * ' + @SpaceToLeavePercentage + ')), 0) AS INT) AS TargetFreeMbRounded
FROM ' + QUOTENAME(@DbName) + '.sys.database_files 
WHERE type_desc = ''' + @RowsOrLog + '''
) AS a
';

INSERT INTO ##DbStats (
	LogicalName
	,FreeMbRounded
	,TargetFreeMbRounded
	,DiffMb
	)
EXEC (@Sql);

SET @TargetFreeMbRounded = (
		SELECT TargetFreeMbRounded
		FROM ##DbStats
		)
SET @DbFileName = (
		SELECT LogicalName
		FROM ##DbStats
		);

IF @TruncateOnly = 1
BEGIN
	SET @ShrinkSql = '
USE ' + QUOTENAME(@DbName) + ';

DBCC SHRINKFILE (
		N' + '''' + @DbFileName + '''' + '
		, 0
		, TRUNCATEONLY
		) WITH NO_INFOMSGS;
';

	EXEC (@ShrinkSql);
END
ELSE
BEGIN
	SET @ShrinkIncrementInMb = (
			(
				SELECT (FLOOR(MAX(FreeMbRounded)) - @TargetFreeMbRounded)
				FROM ##DbStats
				) / @NumberOfShrinks
			);

	UPDATE ##DbStats
	SET ShrinkIncrementInMb = @ShrinkIncrementInMb
		,NumberOfShrinksLeft = @NumberOfShrinks
	WHERE NumberOfShrinksLeft IS NULL;

	SELECT TOP 1 *
	FROM ##DbStats
	ORDER BY Id_DbStats DESC;

	IF @ShrinkIncrementInMb > 0
	BEGIN
		WHILE @NumberOfShrinks > 0
		BEGIN
			SET @FreeMbRounded = (
					SELECT FreeMbRounded
					FROM ##DbStats
					WHERE Id_DbStats = (
							SELECT MAX(Id_DbStats)
							FROM ##DbStats
							)
					);

			IF @TargetFreeMbRounded > (@FreeMbRounded - @ShrinkIncrementInMb)
			BEGIN
				SET @ShrinkToInMb = @TargetFreeMbRounded;
			END
			ELSE
			BEGIN
				SET @ShrinkToInMb = (@FreeMbRounded - @ShrinkIncrementInMb);
			END

			SET @ShrinkSql = '
USE ' + QUOTENAME(@DbName) + ';

DBCC SHRINKFILE (
		N' + '''' + @DbFileName + '''' + '
		,' + CONVERT(VARCHAR(12), @ShrinkToInMb) + '
		) WITH NO_INFOMSGS;
';

			EXEC (@ShrinkSql);

			SET @NumberOfShrinks = @NumberOfShrinks - 1;
			SET @Sql = '
USE ' + QUOTENAME(@DbName) + ';
SELECT LogicalName
,FreeMbRounded
,' + CONVERT(VARCHAR(255), @TargetFreeMbRounded) + '
,FLOOR(FreeMbRounded) - ' + CONVERT(VARCHAR(255), @TargetFreeMbRounded) + ' AS DiffMb
,' + CONVERT(VARCHAR(255), @ShrinkIncrementInMb) + '
,' + CONVERT(VARCHAR(255), @NumberOfShrinks) + '
FROM (
SELECT NAME AS LogicalName
	,CAST(ROUND(CONVERT(DECIMAL(12, 1), (size - FILEPROPERTY(NAME, ''SpaceUsed'')) / 128.0), 0) AS INT) AS FreeMbRounded
	,CAST(ROUND(CONVERT(DECIMAL(12, 1),((((size - FILEPROPERTY(NAME, ''SpaceUsed'')) / 128.0) / 100.0) * ' + @SpaceToLeavePercentage + ')), 0) AS INT) AS TargetFreeMbRounded
FROM ' + QUOTENAME(@DbName) + '.sys.database_files 
WHERE type_desc = ''' + @RowsOrLog + '''
) AS a
';

			INSERT INTO ##DbStats (
				LogicalName
				,FreeMbRounded
				,TargetFreeMbRounded
				,DiffMb
				,ShrinkIncrementInMb
				,NumberOfShrinksLeft
				)
			EXEC (@Sql);

			SELECT TOP 1 *
			FROM ##DbStats
			ORDER BY Id_DbStats DESC;
		END
	END
	ELSE
	BEGIN
		SELECT *
		FROM ##DbStats;
	END

	DROP TABLE ##DbStats
END;
		--DROP TABLE ##DbStats
		/*CHECK TO RUN IN ANOTHER SSMS WINDOW*/
		/*
SELECT TOP 1 Id_DbStats
	,LogicalName
	,FreeMbRounded
	,TargetFreeMbRounded
	,DiffMb
	,ShrinkIncrementInMb
	,NumberOfShrinksLeft
FROM ##DbStats WITH (NOLOCK)
ORDER BY Id_DbStats DESC;
/*
/*CHECK PROGRESS*/
/*
SELECT percent_complete AS PercentageComplete
	,start_time AS StartTime
	,STATUS AS CurrentStatus
	,command AS Command
	,estimated_completion_time AS EstimatedCompletionTime
	,cpu_time AS CpuTime
	,total_elapsed_time AS TotalElapsedTime
FROM sys.dm_exec_requests
WHERE Command = 'DbccFilesCompact'
*/
*/
/*
DROP TABLE ##DbStats
*/
*/

 

If you found this post helpful please like, comment and share.