Stop a SQL Server Agent Job

The following code will check if a SQL Server Agent job is running and then stop it if it is.

The code can be placed in a SQL Server Agent job and scheduled to run at a suitable time i.e. to ensure a maintenance job is not going to run into business hours.

DECLARE @job_name_to_stop nvarchar(128);

/*only update this line with the name of the job to stop*/
SET @job_name_to_stop = N'long running job name';

IF (SELECT TOP 1 1
FROM msdb.dbo.sysjobs_view a, msdb.dbo.sysjobactivity b, msdb.dbo.syssessions c
WHERE 1=1
AND a.job_id = b.job_id
AND b.session_id = c.session_id
AND c.agent_start_date = (SELECT MAX(d.agent_start_date) FROM msdb.dbo.syssessions d)
AND b.run_requested_date IS NOT NULL AND b.stop_execution_date IS NULL
AND a.name = @job_name_to_stop 
) = 1
BEGIN
EXEC msdb.dbo.sp_stop_job @job_name = @job_name_to_stop;
END

DATA_COMPRESSION with REORGANIZE

You are unable to use DATA_COMPRESSION with ALTER INDEX REORGANIZE e.g.

ALTER INDEX CI_c1 ON test_compression_clustered REORGANIZE PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);

However if an index has DATA_COMPRESSION you can perform a reorganize with out losing the data compression:

/* compress index */
ALTER INDEX CI_c1 ON test_compression_clustered REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* REORGANIZE */
ALTER INDEX CI_c1 ON test_compression_clustered REORGANIZE;

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

SORT_IN_TEMPDB Yes or No

No.

When performing an index rebuild operation using SORT_IN_TEMPDB = ON or SORT_IN_TEMPDB = OFF (this is the default), both require additional free space in the data file (mdf) to hold the newly rebuilt index before the old one can be dropped.

When using SORT_IN_TEMPDB = ON, you also require additional space in tempdb, the size of the index being rebuilt!

So if you were under the impression that SORT_IN_TEMPDB = ON doesn’t require the additional space for the rebuilt index in the mdf file, that is not the case.

A basic example for say a 1GB index:

SORT_IN_TEMPDBData File Space Required (GB)Tempdb Space Required (GB)
OFF20
ON21

Azure UTC to Local Time

/* find your time zone name */
SELECT [name] FROM sys.time_zone_info ORDER BY [name];
/* New Zealand Standard Time example 1 */
SELECT 
 GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time' AS local_time_datetimeoffset
,CAST(GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time' AS datetime) AS local_time_datetime
,CONVERT(char(19),GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time' , 121) AS local_time_char;
/* New Zealand Standard Time example 2 */
SELECT 
 GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time' AS local_time_datetimeoffset
,CAST(GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time' AS datetime) AS local_time_datetime;
/* New Zealand Standard Time example 3 */
SELECT
 session_id
,command
,CAST(start_time AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time' AS datetime) AS local_start_time
,percent_complete AS percent_completed
,CASE
 WHEN percent_complete <> 0
 THEN CAST(DATEADD(SECOND,estimated_completion_time/1000,GETUTCDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'New Zealand Standard Time') AS datetime) END
 AS local_estimated_completion_time
FROM sys.dm_exec_requests
ORDER BY percent_complete;

Understanding the is_autogrow_all_files setting

Starting with SQL Server 2016, this setting is enabled by default for tempdb.

Below is a run down on how it works.

/* use tempdb */
USE [tempdb]
GO

/* file groups shows is_autogrow_all_files as enabled*/
SELECT
 name
,is_autogrow_all_files 
FROM sys.filegroups;
is_autogrow_all_files enabled
/* current size */
SELECT 
 name
,physical_name
,type_desc
,(growth* 8)/1024 AS growth_mb
,(size * 8)/1024 AS size_mb
FROM sys.database_files
ORDER BY name;
Ideally all ROWS are the same size
/* change 1 file size */
ALTER DATABASE [tempdb] MODIFY  FILE ( NAME = N'tempdev', SIZE = 10MB );

/* current size */
SELECT 
 name
,physical_name
,type_desc
,(growth* 8)/1024 AS growth_mb
,(size * 8)/1024 AS size_mb
FROM sys.database_files
ORDER BY name;
Modifying 1 file size has no impact on others
/* grow database */
CREATE TABLE #t1 (c1 nchar(4000));
GO
INSERT INTO #t1 (c1) VALUES ('1')
GO 5000

/* current size */
SELECT 
 name
,physical_name
,type_desc
,(growth* 8)/1024 AS growth_mb
,(size * 8)/1024 AS size_mb
FROM sys.database_files
ORDER BY name;
Auto growth kicked in, but still a mismatch in sizes.
Notice that LOG file is not impacted.

Take away from this should be to ensure that your tempdb data files are the same size on startup and use the same growth settings.

You can use sys.master_files to check your tempdb initial size settings.

tempdb sys.master_files vs sys.database_files

For tempdb:

  • sys.master_files = shows file sizes at startup
  • sys.database_files = shows current file sizes
/* sys.master_files*/
SELECT
 name
,physical_name
,type_desc
,(size * 8) /1024 AS size_mb
FROM tempdb.sys.master_files 
WHERE DB_NAME(database_id) = 'tempdb'
ORDER BY name DESC;

/* sys.database_files */
SELECT
 name
,physical_name
,type_desc
,(size * 8) /1024 AS size_mb
FROM tempdb.sys.database_files
ORDER BY name DESC;

SQL Server Configuration Manager Location

Below is the file location of SQL Server Configuration Manager based on the SQL Server version:

SQL VersionLocation
2019C:\Windows\SysWOW64\SQLServerManager15.msc
2017C:\Windows\SysWOW64\SQLServerManager14.msc
2016C:\Windows\SysWOW64\SQLServerManager13.msc
2014C:\Windows\SysWOW64\SQLServerManager12.msc
2012C:\Windows\SysWOW64\SQLServerManager11.msc
2008 R2C:\Windows\SysWOW64\SQLServerManager10.msc
2008C:\Windows\SysWOW64\SQLServerManager10.msc
2005C:\Windows\SysWOW64\SQLServerManager.msc

Does Compressing A Heap/Clustered Index Rebuild Other Indexes?

No; data compression only targets what you specify.

Things to be aware of:

  • Compression locks the index, unless you have Enterprise Edition and then you can use the ONLINE option.
  • The compression process will consume space in the Data file, and it also generates transaction log records as it is a rebuild operation. The space required is only for the index being compressed so work on the index size as a guide for the Data file space being required to compress.

Heap test:

/* create table */
CREATE TABLE test_compression_heap (c1 int IDENTITY (1,1), c2 float);

/* insert some records */
INSERT INTO test_compression_heap (c2) SELECT RAND();
GO 10000

/* create non-clustered index */
CREATE NONCLUSTERED INDEX NCI_c2 ON test_compression_heap(c2);

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_heap'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

/* compress heap */
SET STATISTICS IO ON;
ALTER TABLE test_compression_heap REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
SET STATISTICS IO OFF;

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_heap'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

/* remove heap compression */
SET STATISTICS IO ON;
ALTER TABLE test_compression_heap REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
SET STATISTICS IO OFF;

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_heap'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

/* compress non-clustered index */
SET STATISTICS IO ON;
ALTER INDEX NCI_c2 ON test_compression_heap REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
SET STATISTICS IO OFF;

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_heap'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

/* drop test table */
DROP TABLE test_compression_heap;

Clustered index test:

/* create table */
CREATE TABLE test_compression_clustered (c1 int IDENTITY (1,1), c2 float);

/* insert some records */
INSERT INTO test_compression_clustered (c2) SELECT RAND();
GO 10000

/* create clustered index */
CREATE CLUSTERED INDEX CI_c1 ON test_compression_clustered(c1);

/* create non-clustered index */
CREATE NONCLUSTERED INDEX NCI_c2 ON test_compression_clustered(c2);

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

/* compress clustered index */
SET STATISTICS IO ON;
ALTER INDEX CI_c1 ON test_compression_clustered REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
SET STATISTICS IO OFF;

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

/* remove heap compression */
SET STATISTICS IO ON;
ALTER INDEX CI_c1 ON test_compression_clustered REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
SET STATISTICS IO OFF;

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

/* compress non-clustered index */
SET STATISTICS IO ON;
ALTER INDEX NCI_c2 ON test_compression_clustered REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
SET STATISTICS IO OFF;

/* view compression */
SELECT 
 OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;

/* drop test table */
DROP TABLE test_compression_clustered;

Below is a query to help you identify candidates for compressing.

Run in the database you would like to check.

NOTE: sp_estimate_data_compression_savings samples (up to 5000 used pages) the source object and loads this data into an equivalent table (called #sample_tableDBA05385A6FF40F888204D05C7D56D2B) and index created (called sample_indexDBA05385A6FF40F888204D05C7D56D2B) in tempdb. The table or index create in tempdb is then compressed to the requested setting and the estimated compression savings is computed.

SELECT 'EXEC sp_estimate_data_compression_savings @schema_name=''' +b.[name] + ''',@object_name=''' + a.[name] + ''',@index_id=' + CAST(c.index_id AS varchar(10)) +',@partition_number=' + CAST(c.partition_number AS varchar(10)) +',@data_compression=''PAGE'';/*indexname=' + CASE WHEN d.[name] IS NULL THEN 'heap' ELSE d.[name] END + ',size_gb=' + CAST(CAST(((d.dpages * 8) / 1024 / 1024.) AS decimal(20,2)) AS varchar(100)) + '*/'
FROM sys.tables a, sys.schemas b, sys.partitions c, sysindexes d
WHERE 1=1
AND a.[schema_id] = b.[schema_id]
AND a.[object_id] = c.[object_id]
AND a.[object_id] = d.[id]
AND c.[index_id]= d.[indid]
AND ((d.dpages * 8) / 1024 / 1024.) > 1 /* larger than 1GB */
AND c.data_compression_desc = 'NONE'
ORDER BY ((d.dpages * 8) / 1024 / 1024.) DESC;