Get drive data for ALL drives in T-SQL

Posted on March 24, 2021March 24, 2021 by David

There are a few methods you can use to get drive information using T-SQL. The problem is all these methods have some limitations. They provide free space but not drive capacity or they only provide data for volumes that contain database files. Ideally Microsoft would provide an easy way to get both drive capacity and free space for all drives. In this article I’ll demonstrate a way to do this using T-SQL.

Here are a few exiting methods that you might consider before we continue:

-- Gets size and free space but only for volumes that have SQL data files.
SELECT DISTINCT vs.volume_mount_point AS DriveLetter,
	vs.total_bytes / POWER(1024.0,3) AS SizeGB,
	vs.available_bytes / POWER(1024.0,3) AS FreeGB,
	vs.logical_volume_name as Label
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs

-- Needs SQL 2019 & free space only
SELECT fixed_drive_path, 
	drive_type_desc, 
	free_space_in_bytes / POWER(1024.0,3) FreeGB
FROM sys.dm_os_enumerate_fixed_drives;

-- Gets free space on drive only
EXEC xp_fixeddrives

-- Gets size and free space but only for volumes that have SQL data files.

SELECT DISTINCT vs.volume_mount_point AS DriveLetter,

vs.total_bytes / POWER(1024.0,3) AS SizeGB,

vs.available_bytes / POWER(1024.0,3) AS FreeGB,

vs.logical_volume_name as Label

FROM sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) vs

-- Needs SQL 2019 & free space only

SELECT fixed_drive_path,

drive_type_desc,

free_space_in_bytes / POWER(1024.0,3) FreeGB

FROM sys.dm_os_enumerate_fixed_drives;

-- Gets free space on drive only

EXEC xp_fixeddrives

If these work for your needs, great! If you need capacity and free space for ALL drives you will need a different solution. The best option might be to query that data outside of SQL Server using PowerShell or your programming/scripting language of choice. If you need to do this in T-SQL though, the script below provides a way.

The script uses xp_cmdshell to run a powershell command to get data for ALL the volumes on your system. Powershell formats this data as XML which we capture in the @output table variable – 1 row per line of XML. Then we convert this back to a single string and store as XML in variable @x. We then shred the XML into a table format. This is a bit of a hack – sys.dm_os_volume_stats is a cleaner method but doesn’t give you data for all drives. As a bonus you get more detailed data about those drives; file system, allocation unit size & more.

/* 
	Created by David Wiseman
	https://wiseDATAman.com

	Get drive information for all drives via T-SQL.
	This requires access to xp_cmdshell
*/
IF EXISTS(SELECT * FROM sys.configurations
			WHERE name = 'xp_cmdshell'
			AND value_in_use=1
			) AND EXISTS(SELECT * FROM fn_my_permissions ( 'xp_cmdshell', 'OBJECT' ) WHERE permission_name='EXECUTE')
BEGIN
	SET NOCOUNT ON
	DECLARE @x XML
	DECLARE @output TABLE(
		ID INT IDENTITY(1,1) PRIMARY KEY,
		line NVARCHAR(MAX)
	)
	INSERT INTO @output
	EXEC xp_cmdshell 'powershell -noprofile -Command " & { ConvertTo-Xml -As "String" -NoTypeInformation -InputObject (Get-Volume | Select-Object -Property DriveLetter,Size,SizeRemaining,FileSystem,FileSystemLabel,AllocationUnitSize,DriveType,FileSystemType,HealthStatus,OperationalStatus,UniqueId )}"'

	/* Alternative option for SQL 2017+
	SELECT @x = STRING_AGG(line,CHAR(13) + CHAR(10)) WITHIN GROUP (ORDER BY ID) 
	FROM @output
	WHERE ID>1 -- skip encoding line
	*/

	SELECT @x = (SELECT line + CHAR(13) + CHAR(10)
	FROM @output 
	WHERE ID >1 -- skip encoding line
	ORDER BY ID
	FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')

	SELECT T.c.value('(Property[@Name="DriveLetter"])[1]','CHAR(1)')  as DriveLetter,
		T.c.value('(Property[@Name="Size"])[1]','BIGINT')/POWER(1024.0,3)  as SizeGB,
		T.c.value('(Property[@Name="SizeRemaining"])[1]','BIGINT')/POWER(1024.0,3)  as FreeGB,
		T.c.value('(Property[@Name="FileSystem"])[1]','NVARCHAR(256)')  as FileSystem,
		T.c.value('(Property[@Name="FileSystemLabel"])[1]','NVARCHAR(256)')  as FileSystemLabel,
		T.c.value('(Property[@Name="AllocationUnitSize"])[1]','INT')  as AllocationUnitSize,
		T.c.value('(Property[@Name="DriveType"])[1]','NVARCHAR(256)')  as DriveType,
		T.c.value('(Property[@Name="FileSystemType"])[1]','NVARCHAR(256)')  as FileSystemType,
		T.c.value('(Property[@Name="HealthStatus"])[1]','NVARCHAR(256)')  as HealthStatus,
		T.c.value('(Property[@Name="OperationalStatus"])[1]','NVARCHAR(256)')  as OperationalStatus,
		T.c.value('(Property[@Name="UniqueId"])[1]','NVARCHAR(256)')  as UniqueId
	FROM @x.nodes('/Objects/Object/Property') T(c)
END
ELSE
BEGIN
	RAISERROR('xp_cmdshell required',11,1)
END

Created by David Wiseman

https://wiseDATAman.com

Get drive information for all drives via T-SQL.

This requires access to xp_cmdshell

IF EXISTS(SELECT * FROM sys.configurations

WHERE name = 'xp_cmdshell'

AND value_in_use=1

) AND EXISTS(SELECT * FROM fn_my_permissions ( 'xp_cmdshell', 'OBJECT' ) WHERE permission_name='EXECUTE')

BEGIN

SET NOCOUNT ON

DECLARE @x XML

DECLARE @output TABLE(

ID INT IDENTITY(1,1) PRIMARY KEY,

line NVARCHAR(MAX)

)

INSERT INTO @output

EXEC xp_cmdshell 'powershell -noprofile -Command " & { ConvertTo-Xml -As "String" -NoTypeInformation -InputObject (Get-Volume | Select-Object -Property DriveLetter,Size,SizeRemaining,FileSystem,FileSystemLabel,AllocationUnitSize,DriveType,FileSystemType,HealthStatus,OperationalStatus,UniqueId )}"'

/* Alternative option for SQL 2017+

SELECT @x = STRING_AGG(line,CHAR(13) + CHAR(10)) WITHIN GROUP (ORDER BY ID)

FROM @output

WHERE ID>1 -- skip encoding line

SELECT @x = (SELECT line + CHAR(13) + CHAR(10)

FROM @output

WHERE ID >1 -- skip encoding line

ORDER BY ID

FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)')

SELECT T.c.value('(Property[@Name="DriveLetter"])[1]','CHAR(1)') as DriveLetter,

T.c.value('(Property[@Name="Size"])[1]','BIGINT')/POWER(1024.0,3) as SizeGB,

T.c.value('(Property[@Name="SizeRemaining"])[1]','BIGINT')/POWER(1024.0,3) as FreeGB,

T.c.value('(Property[@Name="FileSystem"])[1]','NVARCHAR(256)') as FileSystem,

T.c.value('(Property[@Name="FileSystemLabel"])[1]','NVARCHAR(256)') as FileSystemLabel,

T.c.value('(Property[@Name="AllocationUnitSize"])[1]','INT') as AllocationUnitSize,

T.c.value('(Property[@Name="DriveType"])[1]','NVARCHAR(256)') as DriveType,

T.c.value('(Property[@Name="FileSystemType"])[1]','NVARCHAR(256)') as FileSystemType,

T.c.value('(Property[@Name="HealthStatus"])[1]','NVARCHAR(256)') as HealthStatus,

T.c.value('(Property[@Name="OperationalStatus"])[1]','NVARCHAR(256)') as OperationalStatus,

T.c.value('(Property[@Name="UniqueId"])[1]','NVARCHAR(256)') as UniqueId

FROM @x.nodes('/Objects/Object/Property') T(c)

END

ELSE

BEGIN

RAISERROR('xp_cmdshell required',11,1)

END

A similar technique could be used to capture the output of other powershell commands in T-SQL. It might not be a good idea but I think it is an interesting technique to demo.

How to handle SQL Agent Jobs with Always On Availability Groups?

Posted on March 17, 2021March 17, 2021 by David

Introduction

When you configure your SQL servers for high availability you want the failover to be as seamless as possible. If you use availability groups, mirroring or log shipping as part of your high availability strategy you will need to take care of anything that lives outside the context of your databases. That might include logins, SQL Server agent jobs and ensuring consistent server level configuration across your availability nodes. This article will discuss how to handle SQL server agent jobs.

Not so seamless failover of agent jobs

If you have any SQL agent jobs that run in the context of your user databases these will need special handling to ensure a seamless failover. Ideally Microsoft would have added some improvements to the SQL agent to help with this. Maybe given us agent jobs that can live within the context of a user database instead of msdb?

As it stands you will need to keep your agent jobs in sync between the primary and secondaries and figure out a mechanism to activate the jobs on the new primary following failover and disable them on the old primary.

A Solution

There are a few ways to handle this, but my approach is to run all the jobs in the context of the master DB. I then check if the user DB is accessible and execute the command with a 3 part name. See the script below for an example. This will work for availability groups, mirroring and log shipping.

DECLARE @DB SYSNAME = 'MyDB'
IF DATABASEPROPERTYEX(@DB, 'Updateability') = 'READ_WRITE'
	AND EXISTS(SELECT 1 FROM sys.databases WHERE name=@DB and state = 0)
BEGIN
	PRINT 'Run command'
	--EXEC MyDB.dbo.MyProc
END
ELSE
BEGIN
	PRINT 'Not run: Database is not writeable'
END

DECLARE @DB SYSNAME = 'MyDB'

IF DATABASEPROPERTYEX(@DB, 'Updateability') = 'READ_WRITE'

AND EXISTS(SELECT 1 FROM sys.databases WHERE name=@DB and state = 0)

BEGIN

PRINT 'Run command'

--EXEC MyDB.dbo.MyProc

END

ELSE

BEGIN

PRINT 'Not run: Database is not writeable'

END

With this approach you can create your jobs and have them active on your primary and secondaries. You don’t need to worry about enabling/disabling jobs on failover – the jobs are already running but they only do any work if the database is primary. This makes the failover totally seamless. The downside to this approach is that the jobs will report that they ran successfully even if they didn’t do any work which could cause confusion.

The next problem is keeping the jobs in sync between your servers. You can script out the jobs using SSMS easy enough but this could get tedious if you have a larger number of jobs. I’ve created a powershell script below to help with this which you can use below. You can use this to report if your jobs are out of sync and also for performing a synchronization. See the comments at the top of the script for some usage examples.

Param(
    [Parameter(mandatory=$true)] 
    [string]$ServerA, 
    [Parameter(mandatory=$true)] 
    [string]$ServerB, 
    [Parameter(mandatory=$false)] 
    [System.Array]$ExcludedCategories=@(), #Job categories to exclude from the synchronization process.
    [Parameter(mandatory=$false)] 
    [System.Array]$IncludedCategories=@(), #Job categories to include in the synchronization process.
    [Parameter(mandatory=$false)] 
    [switch]$DoSync #perform a two way synchronization if specified, otherwise run in reporting mode.
)
<# Created: 2017-12-06
   Author:  David Wiseman
  
   Description: Sync agent jobs between two SQL Server instances or check synchronization status.  This is useful if you are using a high availability technology like
   availability groups, log shipping or database mirroring that does not include SQL Agent jobs in the failover.  The script can be used to validate that the jobs are in sync
   or it can synchronize the jobs.  

   Requires SqlServer powershell module:
   Install-Module -Name SqlServer -AllowClobber

   Example1: 
   ./SyncSQLAgentJobs -ServerA "SERVER1" -ServerB "SERVER2"
   Agent jobs will be checked and differences will be reported. 
   
   Example2: 
   ./SyncSQLAgentJobs -ServerA "SERVER1" -ServerB "SERVER2" -DoSync
   Any jobs that don't exist will be copied from SERVER1 to SERVER2 and from SERVER2 to SERVER1.  Any jobs that are different will be copied from the server with the newer version of the job (Based on DateLastModified).  

   Example3: 
   ./SyncSQLAgentJobs -ServerA "SERVER1" -ServerB "SERVER2" -DoSync ExcludedCategories "ADHOC"
   Same as example 2 but jobs in the category "ADHOC" are excluded from synchronization.

   Note: SQL Agent loads SQLPS which isn't compatible with SqlServer module.  A workaround is to run the following powershell in the SQL Agent job.
    $Error.Clear()
    $out = powershell  -NoProfile -Command "C:\Scripts\SyncSQLAgentJobs.ps1" -ServerA "SERVER1" -ServerB "SERVER2"
    if ($Error.Count -gt 0){
    throw $out
    }
    else{
    $out
    }
   This will ensure the job fails if anything is out of sync.  The SQL Server agent account will need appropriate access to msdb database on both instances.

#>
$ErrorActionPreference="stop"
Import-Module SqlServer -DisableNameChecking

$IncludedCategories

$jobs = Get-SqlAgentJob -ServerInstance $ServerA
$ValidationStatus=$true
$errorMsg=""

$jobs | ForEach-Object{
    $jobA = $_
    $jobB = $null
    $scriptA = ""
    $jobA.Script() | ForEach-Object {$scriptA += $_}
    $jobB = Get-SqlAgentJob -ServerInstance $ServerB -Name $jobA.Name -ErrorAction Ignore

    if ($ExcludedCategories.Contains($jobA.Category)){
        "Skipping " + $ServerA + ": " + $jobA.Name + " : Excluded category: " + $jobA.Category
    }
    elseif($jobB -ne $null -and $ExcludedCategories.Contains($jobB.Category)){
        "Skipping " + $ServerB + ": " + $jobB.Name + " : Excluded category: " + $jobB.Category
    }
    elseif($jobA -ne $null -and $IncludedCategories.Count -gt 0 -and (!$IncludedCategories.Contains($jobA.Category))){
        "Skipping " + $ServerA + ": " + $jobA.Name + " : Not included category: " + $jobA.Category
    }
    elseif($jobB -ne $null -and $IncludedCategories.Count -gt 0 -and (!$IncludedCategories.Contains($jobB.Category))){
        "Skipping " + $ServerB + ": " + $jobB.Name + " : Not included category: " + $jobB.Category
    }
    elseif ($jobB -eq $null){
        $ErrorMsg += "Missing on " + $ServerB + ":" + $jobA.Name + "`n"
        "Missing on " + $ServerB + ":" + $jobA.Name
        $ValidationStatus=$false
        if ($DoSync){
            "Creating job " + $jobA.Name + " on " + $ServerB
             Invoke-Sqlcmd -ServerInstance $ServerB -Query $scriptA -DisableVariables -DisableCommands
        }
    }
        else {
       
        $scriptB = ""
        $jobB.Script() | ForEach-Object {$scriptB += $_}
    
        if ($scriptA -eq $scriptB){
            "OK: " + $_.Name
        }
        else {
            if ($jobA.DateLastModified -lt $jobB.DateLastModified){
                $ErrorMsg += "Different (Newer on $ServerB): " + $_.Name + "`n"
                "Different (Newer on $ServerB): " + $_.Name
                $ValidationStatus=$false
                if ($DoSync){
                    "Dropping job " + $jobA.Name + " on " + $ServerA
                     $JobA.Drop()
                     "Creating job " + $jobA.Name + " on " + $ServerA
                     Invoke-Sqlcmd -ServerInstance $ServerA -Query $scriptB -DisableVariables -DisableCommands
                }
            }
            else{
                $ErrorMsg += "Different (Newer on $ServerA): " + $_.Name + "`n"
                "Different (Newer on $ServerA): " + $_.Name
                $ValidationStatus=$false
                if ($DoSync){
                    "Dropping job " + $jobB.Name + " on " + $ServerB
                     $JobB.Drop()
                     "Creating job " + $jobB.Name + " on " + $ServerB
                     Invoke-Sqlcmd -ServerInstance $ServerB -Query $scriptA -DisableVariables -DisableCommands
                }
            }
        }
    }
}

 $missingA = Get-SqlAgentJob -ServerInstance $ServerB | Where-Object {@(Get-SqlAgentJob -ServerInstance $ServerA -Name $_.Name -ErrorAction Ignore).Count -eq 0}
 $missingA | ForEach-Object{
     $jobB = $_
     if ($ExcludedCategories.Contains($jobB.Category)){
        "Skipping " + $ServerB + ": " + $jobB.Name + " : Excluded category: " + $jobB.Category
     }
     else{
         $ErrorMsg += "Missing on " + $ServerA + ":" + $jobB.Name + "`n"
         "Missing on " + $ServerA + ":" + $jobB.Name 
         $ValidationStatus=$false
         if ($DoSync){
                $scriptB = ""
                $jobB.Script() | ForEach-Object {$scriptB += $_}
                "Creating job " + $jobB.Name + " on " + $ServerA
                 Invoke-Sqlcmd -ServerInstance $ServerA -Query $scriptB -DisableVariables -DisableCommands

         }
     }
 }
 if ($ValidationStatus -eq $false -and $DoSync -eq $false){
    $errorMsg = "SQL Agent jobs require synchronization`n" + $errorMsg
    throw $errorMsg
 }

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

Param(

[Parameter(mandatory=$true)]

[string]$ServerA,

[Parameter(mandatory=$true)]

[string]$ServerB,

[Parameter(mandatory=$false)]

[System.Array]$ExcludedCategories=@(), #Job categories to exclude from the synchronization process.

[Parameter(mandatory=$false)]

[System.Array]$IncludedCategories=@(), #Job categories to include in the synchronization process.

[Parameter(mandatory=$false)]

[switch]$DoSync #perform a two way synchronization if specified, otherwise run in reporting mode.

)

<# Created: 2017-12-06

Author: David Wiseman

Description: Sync agent jobs between two SQL Server instances or check synchronization status. This is useful if you are using a high availability technology like

availability groups, log shipping or database mirroring that does not include SQL Agent jobs in the failover. The script can be used to validate that the jobs are in sync

or it can synchronize the jobs.

Requires SqlServer powershell module:

Install-Module -Name SqlServer -AllowClobber

Example1:

./SyncSQLAgentJobs -ServerA "SERVER1" -ServerB "SERVER2"

Agent jobs will be checked and differences will be reported.

Example2:

./SyncSQLAgentJobs -ServerA "SERVER1" -ServerB "SERVER2" -DoSync

Any jobs that don't exist will be copied from SERVER1 to SERVER2 and from SERVER2 to SERVER1. Any jobs that are different will be copied from the server with the newer version of the job (Based on DateLastModified).

Example3:

./SyncSQLAgentJobs -ServerA "SERVER1" -ServerB "SERVER2" -DoSync ExcludedCategories "ADHOC"

Same as example 2 but jobs in the category "ADHOC" are excluded from synchronization.

Note: SQL Agent loads SQLPS which isn't compatible with SqlServer module. A workaround is to run the following powershell in the SQL Agent job.

$Error.Clear()

$out = powershell -NoProfile -Command "C:\Scripts\SyncSQLAgentJobs.ps1" -ServerA "SERVER1" -ServerB "SERVER2"

if ($Error.Count -gt 0){

throw $out

}

else{

$out

}

This will ensure the job fails if anything is out of sync. The SQL Server agent account will need appropriate access to msdb database on both instances.

$ErrorActionPreference="stop"

Import-Module SqlServer -DisableNameChecking

$IncludedCategories

$jobs = Get-SqlAgentJob -ServerInstance $ServerA

$ValidationStatus=$true

$errorMsg=""

$jobs | ForEach-Object{

$jobA = $_

$jobB = $null

$scriptA = ""

$jobA.Script() | ForEach-Object {$scriptA += $_}

$jobB = Get-SqlAgentJob -ServerInstance $ServerB -Name $jobA.Name -ErrorAction Ignore

if ($ExcludedCategories.Contains($jobA.Category)){

"Skipping " + $ServerA + ": " + $jobA.Name + " : Excluded category: " + $jobA.Category

}

elseif($jobB -ne $null -and $ExcludedCategories.Contains($jobB.Category)){

"Skipping " + $ServerB + ": " + $jobB.Name + " : Excluded category: " + $jobB.Category

}

elseif($jobA -ne $null -and $IncludedCategories.Count -gt 0 -and (!$IncludedCategories.Contains($jobA.Category))){

"Skipping " + $ServerA + ": " + $jobA.Name + " : Not included category: " + $jobA.Category

}

elseif($jobB -ne $null -and $IncludedCategories.Count -gt 0 -and (!$IncludedCategories.Contains($jobB.Category))){

"Skipping " + $ServerB + ": " + $jobB.Name + " : Not included category: " + $jobB.Category

}

elseif ($jobB -eq $null){

$ErrorMsg += "Missing on " + $ServerB + ":" + $jobA.Name + "`n"

"Missing on " + $ServerB + ":" + $jobA.Name

$ValidationStatus=$false

if ($DoSync){

"Creating job " + $jobA.Name + " on " + $ServerB

Invoke-Sqlcmd -ServerInstance $ServerB -Query $scriptA -DisableVariables -DisableCommands

}

else {

$scriptB = ""

$jobB.Script() | ForEach-Object {$scriptB += $_}

if ($scriptA -eq $scriptB){

"OK: " + $_.Name

}

else {

if ($jobA.DateLastModified -lt $jobB.DateLastModified){

$ErrorMsg += "Different (Newer on $ServerB): " + $_.Name + "`n"

"Different (Newer on $ServerB): " + $_.Name

$ValidationStatus=$false

if ($DoSync){

"Dropping job " + $jobA.Name + " on " + $ServerA

$JobA.Drop()

"Creating job " + $jobA.Name + " on " + $ServerA

Invoke-Sqlcmd -ServerInstance $ServerA -Query $scriptB -DisableVariables -DisableCommands

}

else{

$ErrorMsg += "Different (Newer on $ServerA): " + $_.Name + "`n"

"Different (Newer on $ServerA): " + $_.Name

$ValidationStatus=$false

if ($DoSync){

"Dropping job " + $jobB.Name + " on " + $ServerB

$JobB.Drop()

"Creating job " + $jobB.Name + " on " + $ServerB

Invoke-Sqlcmd -ServerInstance $ServerB -Query $scriptA -DisableVariables -DisableCommands

}

$missingA = Get-SqlAgentJob -ServerInstance $ServerB | Where-Object {@(Get-SqlAgentJob -ServerInstance $ServerA -Name $_.Name -ErrorAction Ignore).Count -eq 0}

$missingA | ForEach-Object{

$jobB = $_

if ($ExcludedCategories.Contains($jobB.Category)){

"Skipping " + $ServerB + ": " + $jobB.Name + " : Excluded category: " + $jobB.Category

}

else{

$ErrorMsg += "Missing on " + $ServerA + ":" + $jobB.Name + "`n"

"Missing on " + $ServerA + ":" + $jobB.Name

$ValidationStatus=$false

if ($DoSync){

$scriptB = ""

$jobB.Script() | ForEach-Object {$scriptB += $_}

"Creating job " + $jobB.Name + " on " + $ServerA

Invoke-Sqlcmd -ServerInstance $ServerA -Query $scriptB -DisableVariables -DisableCommands

}

if ($ValidationStatus -eq $false -and $DoSync -eq $false){

$errorMsg = "SQL Agent jobs require synchronization`n" + $errorMsg

throw $errorMsg

}

Set Max Server Server Memory at Startup

Posted on March 3, 2021March 3, 2021 by David

Max server memory is one of the SQL Server configuration options that you should change from the default value. Brent Ozar has some good advice on this here. TLDR: You should leave some extra memory for the o/s.

A good starting point is to leave 10% (4GB minimum). If you are running other processes on your SQL server instances like SSRS, SSAS or if you are running multiple instances you might need to leave a lot more.

You can easily set the max server memory to your desired value in MB using sp_configure or using SSMS and simply forget about it:

-- Replace 12345 with your value in MB 
EXEC sys.sp_configure N'show advanced options', N'1'  RECONFIGURE WITH OVERRIDE
EXEC sys.sp_configure 'max server memory (MB)', '12345'; RECONFIGURE;

-- Replace 12345 with your value in MB

EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure 'max server memory (MB)', '12345'; RECONFIGURE;

What if you add/remove memory from your SQL instance? This is a setting that you need to remember to change. Gone are the days where you need to physically open your servers and install new DIMMS. With the advent of cloud and hypervisors – adding memory can be as easy as a few mouse clicks or running a script.

The point of this article is to consider doing this as a script at startup. If you scale up or down to a new instance type in the cloud – your max server memory could automatically be set to an appropriate value as a startup script. It’s something less to think about.

Show me the code!

This script will apply the 10%/4GB rule. You could adjust the formula as required.

EXEC sys.sp_configure N''show advanced options'', N''1''  RECONFIGURE WITH OVERRIDE

DECLARE @MaxServerMemory INT
SELECT @MaxServerMemory=FLOOR((CASE WHEN total_physical_memory_kb <= (8*POWER(1024,2)) -- < 8GB, leave 50% for o/s
       THEN (total_physical_memory_kb * 0.5)
    WHEN total_physical_memory_kb <= (40*POWER(1024,2)) -- < 40GB, leave 4GB for o/s
       THEN total_physical_memory_kb-(4*POWER(1024,2))
       ELSE total_physical_memory_kb*0.9 --  leave 10% for o/s
       END)/1024)
FROM sys.dm_os_sys_memory

EXEC sys.sp_configure N''max server memory (MB)'', @MaxServerMemory
GO
RECONFIGURE WITH OVERRIDE
GO

EXEC sys.sp_configure N''show advanced options'', N''0''  RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure N''show advanced options'', N''1'' RECONFIGURE WITH OVERRIDE

DECLARE @MaxServerMemory INT

SELECT @MaxServerMemory=FLOOR((CASE WHEN total_physical_memory_kb <= (8*POWER(1024,2)) -- < 8GB, leave 50% for o/s

THEN (total_physical_memory_kb * 0.5)

WHEN total_physical_memory_kb <= (40*POWER(1024,2)) -- < 40GB, leave 4GB for o/s

THEN total_physical_memory_kb-(4*POWER(1024,2))

ELSE total_physical_memory_kb*0.9 -- leave 10% for o/s

END)/1024)

FROM sys.dm_os_sys_memory

EXEC sys.sp_configure N''max server memory (MB)'', @MaxServerMemory

RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure N''show advanced options'', N''0'' RECONFIGURE WITH OVERRIDE

You just need to run this as a SQL Agent job at startup which you can do using the script below:

USE [msdb]
GO

/****** Object:  Job [SET MAX SERVER MEMORY]    Script Date: 03/03/2021 22:22:46 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 03/03/2021 22:22:46 ******/
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'SET MAX SERVER MEMORY',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@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'DBA', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [SET MAX SERVER MEMORY]    Script Date: 03/03/2021 22:22:46 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SET MAX SERVER MEMORY',
@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'
EXEC sys.sp_configure N''show advanced options'', N''1''  RECONFIGURE WITH OVERRIDE

DECLARE @MaxServerMemory INT
SELECT @MaxServerMemory=FLOOR((CASE WHEN total_physical_memory_kb <= (8*POWER(1024,2)) -- < 8GB, leave 50% for o/s
       THEN (total_physical_memory_kb * 0.5)
    WHEN total_physical_memory_kb <= (40*POWER(1024,2)) -- < 40GB, leave 4GB for o/s
       THEN total_physical_memory_kb-(4*POWER(1024,2))
       ELSE total_physical_memory_kb*0.9 --  leave 10% for o/s
       END)/1024)
FROM sys.dm_os_sys_memory

EXEC sys.sp_configure N''max server memory (MB)'', @MaxServerMemory
GO
RECONFIGURE WITH OVERRIDE
GO

EXEC sys.sp_configure N''show advanced options'', N''0''  RECONFIGURE WITH OVERRIDE',
@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'AgentStart',
@enabled=1,
@freq_type=64,
@freq_interval=0,
@freq_subday_type=0,
@freq_subday_interval=0,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20171101,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959
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

USE [msdb]

/****** Object: Job [SET MAX SERVER MEMORY] Script Date: 03/03/2021 22:22:46 ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 03/03/2021 22:22:46 ******/

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'SET MAX SERVER MEMORY',

@enabled=1,

@notify_level_eventlog=0,

@notify_level_email=2,

@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'DBA', @job_id = @jobId OUTPUT

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

/****** Object: Step [SET MAX SERVER MEMORY] Script Date: 03/03/2021 22:22:46 ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SET MAX SERVER MEMORY',

@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'

EXEC sys.sp_configure N''show advanced options'', N''1'' RECONFIGURE WITH OVERRIDE

DECLARE @MaxServerMemory INT

SELECT @MaxServerMemory=FLOOR((CASE WHEN total_physical_memory_kb <= (8*POWER(1024,2)) -- < 8GB, leave 50% for o/s

THEN (total_physical_memory_kb * 0.5)

WHEN total_physical_memory_kb <= (40*POWER(1024,2)) -- < 40GB, leave 4GB for o/s

THEN total_physical_memory_kb-(4*POWER(1024,2))

ELSE total_physical_memory_kb*0.9 -- leave 10% for o/s

END)/1024)

FROM sys.dm_os_sys_memory

EXEC sys.sp_configure N''max server memory (MB)'', @MaxServerMemory

RECONFIGURE WITH OVERRIDE

EXEC sys.sp_configure N''show advanced options'', N''0'' RECONFIGURE WITH OVERRIDE',

@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'AgentStart',

@enabled=1,

@freq_type=64,

@freq_interval=0,

@freq_subday_type=0,

@freq_subday_interval=0,

@freq_relative_interval=0,

@freq_recurrence_factor=0,

@active_start_date=20171101,

@active_end_date=99991231,

@active_start_time=0,

@active_end_time=235959

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: