/*============================================================================
  File:     3_ImpactOfShrinkOnFragmentation.sql

  Summary:  This script shows the affect of shrink on
			index fragmentation.

  Date:     April 2017

  SQL Server Version: 2005/2008/2008R2/2012/2014/2016
------------------------------------------------------------------------------
  Written by Paul S. Randal, SQLskills.com
	Modified by Erin Stellato, SQLskills.com

	http://www.sqlskills.com/blogs/paul/auto-shrink-turn-it-off/

  (c) 2015, SQLskills.com. All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  You may alter this code for your own *non-commercial* purposes. You may
  republish altered code as long as you include this copyright and give due
  credit, but you must obtain prior permission before blogging this code.
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

/*
	Create a new database
	Create a 10MB "filler" table which we will drop later
	to create space that can be used by the shrink operation
*/

USE [master];
GO

IF DATABASEPROPERTYEX ('ShrinkTest', 'Version') > 0
BEGIN
	DROP DATABASE [ShrinkTest];
END

CREATE DATABASE [ShrinkTest];
GO

USE ShrinkTest;
GO

SET NOCOUNT ON;
GO

/*
	Create and fill the filler table
*/

CREATE TABLE [dbo].[filler] (
	[c1] INT IDENTITY, 
	[c2] VARCHAR(8000));
GO

DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 1280) --insert 10MB
BEGIN
	INSERT INTO filler VALUES (REPLICATE ('a', 5000));
	SELECT @a = @a + 1;
END;
GO

/*
	Now create the "production" table that we want to 
	optimize for performance
*/

CREATE TABLE [dbo].[Production] (
	[c1] INT IDENTITY, 
	[c2] VARCHAR (8000));
GO

CREATE CLUSTERED INDEX [CL_Production] 
	ON [dbo].[Production] ([c1]);
GO

DECLARE @a INT;
SELECT @a = 1;
WHILE (@a < 1280) --insert 10MB
BEGIN
	INSERT INTO [dbo].[Production] VALUES (REPLICATE ('a', 5000));
	SELECT @a = @a + 1;
END;
GO 


/*
	Check table sizes for fun
*/
EXEC sp_spaceused 'dbo.filler';
GO
EXEC sp_spaceused 'dbo.Production';
GO


/*
	Check space used within the file
*/
SELECT
	[file_id],
	[type],
	substring([physical_name],1,1),
	[name],
	[physical_name],
	CAST([size] as DECIMAL(38,0))/128. [FileSize], 
	CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128. [SpaceUsed], 
	(CAST([size] as DECIMAL(38,0))/128) - (CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128.) [EmptySpace]
FROM [ShrinkTest].[sys].[database_files];
GO

/*
	Use the sys.dm_db_index_physical_stats DMV to check 
	fragmentation of the clustered index
	for [dbo].[Production] - it should be almost 0
*/
SELECT 
	[avg_fragmentation_in_percent], 
	[fragment_count]
FROM [sys].[dm_db_index_physical_stats] (
	DB_ID(N'ShrinkTest'), OBJECT_ID(N'dbo.Production'), 1, NULL, 'LIMITED');
GO




/*
	Now drop the filler table
*/
DROP TABLE [dbo].[filler];
GO
CHECKPOINT;
GO


/*
	Check space used within the file again
*/
SELECT
	[file_id],
	[type],
	substring([physical_name],1,1),
	[name],
	[physical_name],
	CAST([size] as DECIMAL(38,0))/128. [FileSize], 
	CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128. [SpaceUsed], 
	(CAST([size] as DECIMAL(38,0))/128) - (CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128.) [EmptySpace]
FROM [ShrinkTest].[sys].[database_files];



/*
	There's free space, let's shrink the database!
*/
DBCC SHRINKDATABASE ([ShrinkTest]);
GO
     

/*
	Check space used within the file
*/
SELECT
	[file_id],
	[type],
	substring([physical_name],1,1),
	[name],
	[physical_name],
	CAST([size] as DECIMAL(38,0))/128. [FileSize], 
	CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128. [SpaceUsed], 
	(CAST([size] as DECIMAL(38,0))/128) - (CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128.) [EmptySpace]
FROM [ShrinkTest].[sys].[database_files];
GO


/*
	Check index fragmentation again
*/
SELECT 
	[avg_fragmentation_in_percent], 
	[fragment_count]
FROM [sys].[dm_db_index_physical_stats] (
	DB_ID(N'ShrinkTest'), OBJECT_ID(N'dbo.Production'), 1, NULL, 'LIMITED');
GO


/*
	What happens if you rebuild the index?
*/
ALTER INDEX [CL_Production] ON [dbo].[Production] REBUILD;
GO


/*
	Check index fragmentation again
*/
SELECT 
	[avg_fragmentation_in_percent], 
	[fragment_count]
FROM [sys].[dm_db_index_physical_stats] (
	DB_ID(N'ShrinkTest'), OBJECT_ID(N'dbo.Production'), 1, NULL, 'LIMITED');
GO


/*
	Check space used within the file
*/
SELECT
	[file_id],
	[type],
	substring([physical_name],1,1),
	[name],
	[physical_name],
	CAST([size] as DECIMAL(38,0))/128. [FileSize], 
	CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128. [SpaceUsed], 
	(CAST([size] as DECIMAL(38,0))/128) - (CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128.) [EmptySpace]
FROM [ShrinkTest].[sys].[database_files];


/*
	Clean up
*/
USE [master];
GO

DROP DATABASE [ShrinkTest];
GO


/*
	Example scripts to shrinking a log file
	Assumes the [GUIDTest] database exists, is in
	FULL recovery, and that its log file has grown 
	several times and created a high number of VLFs.
*/

/*
	Make sure a full backup exists
*/
DECLARE @BackupPath NVARCHAR(100);
SET @BackupPath = 'C:\Backups\GUIDTest_' + REPLACE(CONVERT(nvarchar(19),SYSDATETIME(), 126), ':','') + '.bak';
GO

BACKUP DATABASE [GUIDTest] 
	TO  DISK = @BackupPath
	WITH NOFORMAT, 
	INIT,   
	COMPRESSION,  
	STATS = 10;
GO

/*
	Check recovery model
*/
SELECT [name], [recovery_model_desc]
FROM [sys].[databases]
WHERE [name] = 'GUIDTest';
GO

/*
	Check the number of VLFs that exist
*/
DBCC LOGINFO('GUIDTest');
GO

/*
	Check the size of the log
*/
SELECT
	[file_id],
	[type],
	substring([physical_name],1,1),
	[name],
	[physical_name],
	CAST([size] as DECIMAL(38,0))/128. [FileSizeMB], 
	CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128. [SpaceUsedMB], 
	(CAST([size] as DECIMAL(38,0))/128) - (CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128.) [EmptySpaceMB]
FROM [GUIDTest].[sys].[database_files]
WHERE [type] = 1;
GO


/*
	Shrink the log file to 5MB
	Note that this is very small - you may start with
	a larger size, and then check to see how many VLFs 
	exist.
*/
USE [GUIDTest];
GO

DBCC SHRINKFILE (N'GUIDTest_log' , 5);
GO


/*
	Because shrink may not always shrink the file
	to the size specified, check to see.
*/
SELECT
	[file_id],
	[type],
	substring([physical_name],1,1),
	[name],
	[physical_name],
	CAST([size] as DECIMAL(38,0))/128. [FileSizeMB], 
	CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128. [SpaceUsedMB], 
	(CAST([size] as DECIMAL(38,0))/128) - (CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128.) [EmptySpaceMB]
FROM [GUIDTest].[sys].[database_files]
WHERE [type] = 1;



/*
	We have active VLFs...the log cannot shrink until those are cleared
*/
DECLARE @BackupPath NVARCHAR(100);
SET @BackupPath = 'C:\Backups\GUIDTest_TLog' + REPLACE(CONVERT(nvarchar(19),SYSDATETIME(), 126), ':','') + '.bak';
GO

BACKUP LOG [GUIDTest] 
	TO  DISK = @BackupPath
	WITH INIT,  
	STATS = 10;
GO


/*
	Try to shrink again
*/
USE [GUIDTest];
GO
DBCC SHRINKFILE (N'GUIDTest_log' , 5);
GO


/*
	Check the log size again
*/
SELECT
	[file_id],
	[type],
	substring([physical_name],1,1),
	[name],
	[physical_name],
	CAST([size] as DECIMAL(38,0))/128. [FileSizeMB], 
	CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128. [SpaceUsedMB], 
	(CAST([size] as DECIMAL(38,0))/128) - (CAST(FILEPROPERTY([name],'SpaceUsed') AS DECIMAL(38,0))/128.) [EmptySpaceMB]
FROM [GUIDTest].[sys].[database_files]
WHERE [type] = 1;
GO


/*
	Check the number of VLFs that exist.
	If the file needs to be smaller, take a log
	backup and then shrink the file again.  It may
	be necessary to repeat a few times, as VLFs at the
	end of the log may still be active (backups will
	truncate).
*/
DBCC LOGINFO('GUIDTest');
GO


/*
	Once the log has been shrunk, re-size the file
	If growing to a larger size (many GB), grow in
	increments of 8000MB.
	http://www.sqlskills.com/blogs/kimberly/8-steps-to-better-transaction-log-throughput/
	http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/
	http://www.sqlskills.com/blogs/paul/important-change-vlf-creation-algorithm-sql-server-2014/
*/
USE [master];
GO
ALTER DATABASE [GUIDTest] MODIFY FILE ( NAME = N'GUIDTest_log', SIZE = 8000MB );
GO


/*
Up through 2012, the algorithm for how many VLFs you get when 
you create, grow, or auto-grow the log is based on the size in question:

    Less than 1 MB, complicated, ignore this case.
    Up to 64 MB: 4 new VLFs, each roughly 1/4 the size of the growth
    64 MB to 1 GB: 8 new VLFs, each roughly 1/8 the size of the growth
    More than 1 GB: 16 new VLFs, each roughly 1/16 the size of the growth

e.g.	create a log at 1 GB			=	8 VLFs
		grow the log by 1GB 100 times	=	800 VLFs
										=	808 VLFs

For SQL Server 2014, the algorithm is now:

    Is the growth size less than 1/8 the size of the current log size?
    Yes: create 1 new VLF equal to the growth size
    No: use the formula above

e.g.	create a log at 1 GB			=	8 VLFs
		grow the log by 1GB 100 times	=	156 VLFs
			first 8GB follows old formula, 8*8=64
			next 92 GB of growth, 92*1=92


*/