Defrag Database

Fragmentation could be caused by much manipulating on data such as Insert, Update or/and Delete. Als

Fragmentation could be caused by much manipulating on data such as Insert, Update or/and Delete.

Also, shrinking would cause fragmentation and would require the Database admins to defrag the database by rebuilding and/or reorganizing all the indices.
For more info about Indexes, check my post SQL Server Indexes Management Overview.

You can check the fragmentation by the following Syntax:

Use Database_Name;
SELECT (Sch.Name + '.[' + T.Name + ']') As TableName, I.Name AS IndexName,
INS.index_type_desc As IndexType, Fill_Factor, Page_Count,
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL) As INS
INNER JOIN sys.indexes I ON I.object_id = INS.object_id AND I.index_id = INS.index_id
Inner Join sys.Tables As T On T.object_id = INS.object_id
Inner Join sys.Schemas As Sch On T.Schema_ID = Sch.Schema_ID
WHERE I.Name IS Not Null And INS.avg_fragmentation_in_percent > 0
ORDER BY INS.avg_fragmentation_in_percent DESC;

Defragmentation can be performed by rebuilding (if fragmentation is above 30%) and/or reorganizing (if fragmentation is equal to or less than 30%) all the indexes in the database.
Hence, I had built a T-SQL script for Microsoft SQL Server 2008 and above versions to defrag the database by rebuilding and/or reorganizing all the indices for a specified database.

You can download and use one of the following two T-SQL scripts:

  1. Defrag_Database.sql” can be executed this script direct using the parameters listed on the underneath table.
  2. JSP_Defrag_Database - Stored Procedure.sql” can be executed once per a time to create a temporary stored procedure in the memory until the current query session is closed. This Stored Procedure can be used to defrag multiple databases in the same opened query session using the following syntax:
    Execute ##JSP_Defrag_Database @DataBase = 'AdventureWorks2008R2';

Parameter Name


Data Type

Default Value

Values Range







The target database to be defragged. If not set, it would defrag the current connected database.





0 - 100

The fill factor ratio; between 0 to 100 while 0 is the same respects as 100.
Or leave it with the default value -1 to keep the existing fill factor.





On | Off

If set to “On”, the database would be kept in the “Online” mode, if set to “Off” the database would be set to the “Offline” mode.





Off | Reorg

Both XML and Spatial indexes are not supported in rebuilding with “Online” mode (@Online = ‘On’), therefore, it must be to set wither to “Off” to be rebuilt with “Offline” mode, or “Reorg” to be reorganized instead of the rebuilding.





Off | Reorg


Important Remarks:

  1. The “Online = On” option will not work with the following cases:
  • Non Enterprise Edition of SQL.
  • The index is an XML
  • The index is a Spatial
  • The index is on a local temp table.
  • The index is clustered and the table contains a LOB database column(s).
  • The index is not clustered and the index itself contains a LOB database column(s).
  1. Do not execute the above script on Microsoft SQL Server 2012 or Microsoft SQL Server 2014 with “Online” option.

If using SQL Server 2012 or SQL Server 2014, mind there is a bug where the online index rebuild can cause index corruption or data loss when it is used together with concurrent queries that modify many rows.

Apply a CU that includes hotfix
For SQL Server 2012 SP1, apply at least CU11.
For SQL Server 2012 SP2, apply at least CU1.
For SQL Server 2014, apply at least CU2.

The following T-SQL script is useful to get all the required info about the current installed SQL:

		SERVERPROPERTY('ServerName') AS ServerName,
		SERVERPROPERTY('MachineName') AS MachineName,
		SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS CurrentActiveClusterMachine,
		SERVERPROPERTY('InstanceName') AS InstanceName,
		SERVERPROPERTY('Edition') AS Edition,
		SERVERPROPERTY('ProductVersion') AS ProductVersion,
		SERVERPROPERTY('ProductBuild') As ProductBuild,
		SERVERPROPERTY('ProductLevel') AS ProductLevel,
		SERVERPROPERTY('ProductUpdateLevel') As UpdateLevel,
		SERVERPROPERTY('ProductUpdateReference') As UpdateReferenceKB,
		SERVERPROPERTY('ResourceLastUpdateDateTime') As UpdateDateTime,
		@@version As Version;

Add comment