-- Change service level objective for a database
-- Must be connected to the master database or disconnection will occur
-- Must be a server administrator
DECLARE @DatabaseName sysname = 'beanperfectiondatawarehouse';
DECLARE @ServiceLevelObjective sysname = 'S0';
DECLARE @DelaySecondsPerCheck int = 10;
DECLARE @MaximumDelaySeconds int = 600;
SET NOCOUNT ON;
SET XACT_ABORT ON;
DECLARE @SQL nvarchar(max);
DECLARE @QUOTE nchar(1) = NCHAR(39);
DECLARE @DelaySoFar int = 0;
DECLARE @Duration varchar(8)
= CASE WHEN @DelaySecondsPerCheck BETWEEN 0 AND 86399
THEN LEFT(CONVERT(varchar(20), DATEADD(second, @DelaySecondsPerCheck, CAST(CAST(SYSDATETIME() AS date) AS datetime)), 14), 8)
END;
IF @Duration IS NULL
BEGIN
RAISERROR('Invalid duration value', 16, 1);
END;
DECLARE @CurrentServiceLevelObjective sysname =
(
SELECT [service_objective]
FROM sys.databases AS d
INNER JOIN sys.database_service_objectives AS dso
ON dso.database_id = d.database_id
WHERE d.[name] = @DatabaseName
);
IF @CurrentServiceLevelObjective IS NULL
BEGIN
RAISERROR('Cannot obtain current service level objective. Was the database name correct ?', 16, 1);
END ELSE BEGIN
IF @CurrentServiceLevelObjective = @ServiceLevelObjective
BEGIN
PRINT 'Already at the required service level objective';
END ELSE BEGIN
SET @SQL = N'ALTER DATABASE ' + QUOTENAME(@DatabaseName)
+ N' MODIFY (SERVICE_OBJECTIVE = '
+ @QUOTE
+ @ServiceLevelObjective
+ @QUOTE
+ N');';
EXEC sp_executesql @SQL;
WAITFOR DELAY @Duration;
SET @DelaySoFar = @DelaySecondsPerCheck;
WHILE (SELECT [service_objective]
FROM sys.databases AS d
INNER JOIN sys.database_service_objectives AS dso
ON dso.database_id = d.database_id
WHERE d.[name] = @DatabaseName) <> @ServiceLevelObjective
AND @DelaySoFar <= @MaximumDelaySeconds
BEGIN
WAITFOR DELAY @Duration;
SET @DelaySoFar += @DelaySecondsPerCheck;
IF @DelaySoFar > @MaximumDelaySeconds
BEGIN
RAISERROR('Maximum delay for changing service level objective exceeded.', 16, 1);
END;
END;
END;
END;