Author: Richard

As an exercise in ‘Clean code’ this is about the best I could do:-

--fnGetAcademicYear.sql


/* Returns academic year of supplied date */
CREATE FUNCTION [dbo].[fn_Get_AcademicYear]
(
   @in DATE
)
RETURNS INT
AS
BEGIN

  DECLARE @out INT;

  SELECT @out=CASE WHEN DATEPART(MONTH, @in) > 7 
              THEN CONCAT(YEAR(@in), YEAR(@in) + 1)
              ELSE CONCAT(YEAR(@in) - 1, YEAR(@in))
              END;

  RETURN @out;

END;
GO

With these sorts of things, traditionally you say item1 + item2 + number, where you want item1 bolted onto item2, and item2 to have had some maths done to it. The ‘+’ symbol serving as both a combiner and a mathematical operator.

To force ‘+’ to be a combiner and not a maths operator you use CONVERT or CAST to change the objects to text strings.

Happily CONCAT is an alternative we can use to get away from the confusing dual purpose of ‘+’. And clean up all those conversions. This operator only joins items together, and never does maths on them. 🙂

I have only had to do this a few times in a VM but want to capture the steps. Note that with Azure VM – The New datafiles will be the same size as the Old datafiles.

/* MovingTempDB.sql */
 
/* 1 save the current catalog paths to "rollback" (just-in-case) */
 
    USE master;
    GO
	select space(4) + 'USE master;' cmd
	union
	select space(4) + 'GO'
	union
    SELECT space(4) + 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + name + '], FILENAME = [' + physical_name + ']);'
    FROM sys.master_files 
    WHERE database_id = DB_ID(N'TempDB')
    ORDER BY cmd desc;

/* 2 manually create the "new" destination folder(s) */
  
/* 3 change the catalog */
 
    USE master;
    GO
    ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], FILENAME = [D:\TempDB\tempdb.mdf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2], FILENAME = [D:\TempDB\tempdb_2.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp3], FILENAME = [D:\TempDB\tempdb_3.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp4], FILENAME = [D:\TempDB\tempdb_4.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp5], FILENAME = [D:\TempDB\tempdb_5.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp6], FILENAME = [D:\TempDB\tempdb_6.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp7], FILENAME = [D:\TempDB\tempdb_7.ndf]);
    ALTER DATABASE tempdb MODIFY FILE (NAME = [temp8], FILENAME = [D:\TempDB\tempdb_8.ndf]);
 
    /* confirmation message
    "The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started." */
   
/* 4 manually reboot the server */
 
/* 5 manually check the new files have been created */
  
/* 6 manually delete old files, and empty the recycle-bin */

/* 7 ideally pre-grow all tempdb data files */

/* x rollback if needed */

    USE master;
    GO
	ALTER DATABASE tempdb MODIFY FILE (NAME = [templog], FILENAME = [F:\SQLLogs\templog.ldf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], FILENAME = [H:\TempDB\tempdb.mdf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp2], FILENAME = [H:\TempDB\tempdb_2.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp3], FILENAME = [H:\TempDB\tempdb_3.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp4], FILENAME = [H:\TempDB\tempdb_4.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp5], FILENAME = [H:\TempDB\tempdb_5.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp6], FILENAME = [H:\TempDB\tempdb_6.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp7], FILENAME = [H:\TempDB\tempdb_7.ndf]);
	ALTER DATABASE tempdb MODIFY FILE (NAME = [temp8], FILENAME = [H:\TempDB\tempdb_8.ndf]);

Normally I adopt the local standard, but failing that here is my current Stored Procedure template suitable for all cloud environments. This year I have gone ultra simple 🙂

-- NewProcName.sql

USE [SomeDbName];
GO

/* =====================================
   Author:		Richard
   Date:		10 Jul 2023
   Description: What it should do
   ===================================== */

CREATE PROCEDURE [schema].[NewProcName]
AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON;

		/* Do things */

END
GO

The challenge with this one, was to remove over 2,000 duplicate rows from a 600,000,000 row heap, before a multi-column clustered primary key could be added.

(I am so happy to finally be able to document this, from a while ago. The phrase “uniquely challenging” was never so apt.)

My first idea was to create a empty copy of the source table, add a “ignore duplicates” index, then start an INSERT [dbo].[targettable] SELECT * FROM [dbo].[sourcetable]. However, I stopped after an hour as it was going to take too long.

My second idea was to use SSIS to populate the target-table. Again this was just too slow.

My third idea was to just remove the duplicates (2,000 being a much smaller number than 600,000,000). First though, a disclaimer, I only considered this ** high risk ** solution because the data was in a QA environment (not Live), and people were waiting for me.

Step one then, was to populate a control-table with the duplicates …

SELECT column1, column2, column3, column4, count(*)
INTO #tmp1
FROM [dbo].[sometablename]
GROUP BY column1, column2, column3, column4
HAVING COUNT(*) > 1;

(“column1” etc were not the real column names by-the-way 🙂 ). Step two was to loop through this list removing duplicates from the permanent table

WHILE exists (SELECT 1 FROM #tmp1)
BEGIN

	DECLARE @1 BIGINT, @2 INT, @3 BIGINT
	DECLARE @4 VARCHAR(10)

	SELECT TOP(1) 
		@1 = column1, 
		@2 = column2, 
		@3 = column3, 
		@4 = column4 
	FROM #tmp1

	DELETE TOP(1) 
	FROM [dbo].[sometablename] 
	WHERE column1 = @1
	AND column2 = @2
	AND column3 = @3
	AND column4 = @4

	DELETE 
	FROM #tmp1
	WHERE column1 = @1
	AND column2 = @2
	AND column3 = @3
	AND column4 = @4

END

Step three was to implement the clustered primary key. (by-the-way I was half expecting this to fail with a duplicates error, but happily the above loop had cleared a single duplicate from each pair, and thats all that was needed).

ALTER TABLE [dbo].[sometablename] 
ADD CONSTRAINT pk_sometablename
 PRIMARY KEY CLUSTERED
 ([column1], [column2], [column3], [column4]);

** just to point out – I consider the loop “high risk” because, an error message, a typo, accidentally running it twice, or faulty logic, could have resulted in disaster 🙂

The Resource Governor is one of those rarely used tools, as its impact is inherently ‘negative’. It restricts resources from a user-group, but cannot be seen to boost performance of a more favoured group.

For example, you could restrict the SA login to use just 5% of the CPU resource. The effect of this would be that SA can use 100% of the CPU resource, until another user-group wants to use more CPU than is currently available. SA will then be throttled back, and back, until it is only using 5%. Five percent is the minimum SA can use.

It is not easy to see this throttling in action – without firing up perf-mon. Also, the GUI view within SSMS is not-so-good, showing only some of the configuration. Therefore this is one of those things that I work with completely at the code level.

So, here then is my crib-sheet …

-- ResPool.sql

-- 1 create custom resource pool of available hardware
USE master;
GO
CREATE RESOURCE POOL MxResourcePool WITH
(
	MIN_CPU_PERCENT = 0,
	MAX_CPU_PERCENT = 5
);
GO

-- 2 create a group and link it to the resource pool
USE master;
GO
CREATE WORKLOAD GROUP MxWorkloadGroup
USING MxResourcePool;
GO

-- 3 populate the group
USE master;
GO
CREATE FUNCTION dbo.fn_MxClassifier()
RETURNS sysname WITH SCHEMABINDING
AS
BEGIN
DECLARE @wg AS sysname
IF SUSER_NAME() = 'sa'
	SET @wg = 'MxWorkloadGroup'
ELSE
	SET @wg = 'default'

RETURN @wg
END;
GO

ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.fn_MxClassifier);
ALTER RESOURCE GOVERNOR RECONFIGURE;
ALTER RESOURCE GOVERNOR RECONFIGURE;

-- check config

SELECT s.session_id,
s.host_name,
s.program_name,
s.nt_user_name,
s.login_name, 
w.name WorkgroupAssignment,
r.name ResourcePoolAssignment

FROM sys.dm_exec_sessions s
JOIN sys.dm_resource_governor_workload_groups w
  ON s.group_id = w.group_id
JOIN sys.dm_resource_governor_resource_pools r
  ON w.pool_id = r.pool_id
WHERE s.host_name IS NOT NULL
ORDER BY nt_user_name-- s.session_id desc

-- undo

ALTER RESOURCE GOVERNOR DISABLE;
DROP WORKLOAD GROUP MxWorkloadGroup;
DROP RESOURCE POOL MxResourcePool;
DROP FUNCTION dbo.fn_MxClassifier;
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = NULL);
ALTER RESOURCE GOVERNOR RECONFIGURE;

The SQL Server “Recovery Time Interval” setting used to be ‘0’ by default, which could have a performance impact during a Checkpoint, by amplifying lazywriter contention.

Indirect checkpointing can alleviate this and has been available since SQL Server 2012. Indeed the default value was changed to ’60’ (seconds) from SQL Server 2016 onwards.

Whilst removing orphaned user accounts, I came across this error …

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.

Looking at the properties of the user within the database I noticed that they owned the db_datawriter schema. Within the properties of that schema I was able to type db_datawriter as the new owner. After which I was able to remove the user from the database.

Here is a scripted way to do this …

https://www.mssqltips.com/sqlservertip/3439/script-to-drop-all-orphaned-sql-server-database-users/

There are much better and easier ways to test a read-only routing configuration of an Availability Group. But here is my walk-through using SQL Server Management Studio.

1. In SSMS connect to “Database Engine”
2. Type in the name of the listener, a comma, then the port used by the listener
3. Click the “Options” button
4. On the Connection Properties tab choose “Connect to Database” then “Browse Server”
5. Choose a database protected by the AG
6. On the Additional Parameters tab type “ApplicationIntent=ReadOnly”
7. Click “Connect”
8. Run a read-only query like “SELECT @@SERVERNAME”
9. The result should be the Secondary instance (AKA secondary replica)
10. Delete the query-pane and connection
11. Then set it up again – but without “ApplicationIntent=ReadOnly”
12. Now the result of the query should be the Primary replica.

BONUS

The “much better ways” include using SQLCMD from another server. For example running this should return the name of the Secondary replica.

sqlcmd -S (ListenerName),(ListnerPort) 
-d (ProtectedDatabaseName) 
-q "SELECT @@SERVERNAME;" 
-K ReadOnly

Then, running it again without the last line should return the name of the Primary replica.

Note: It does not matter which Listener/Port you choose if there is more than one.

I cobbled this together to run on multiply servers via CMS. It is simpler and more trustworthy than alerts from monitoring-software.

--JobFailures.sql

SELECT
    J.name,
	H.FailedAt,
    H.Message
FROM
    msdb.dbo.sysjobs AS J
    CROSS APPLY (
        SELECT TOP(1)
            FailedAt = msdb.dbo.agent_datetime(T.run_date, T.run_time),
            Message = T.message
        FROM
            msdb.dbo.sysjobhistory AS T
        WHERE
            T.job_id = J.job_id
		AND
			T.run_status = 0 -- failed
		AND
			msdb.dbo.agent_datetime(T.run_date, T.run_time) > getdate()-1 -- in the last 24 hrs
--			msdb.dbo.agent_datetime(T.run_date, T.run_time) > getdate()-3 -- covering the weekend
        ORDER BY
            T.instance_id) H

Original code from here …

https://stackoverflow.com/questions/54215008/sql-agent-job-last-run-status

Looking through the Database Mail log today, I accidentally discovered a job that had been busy sending emails for I-don’t-know-how-long using an email profile that no longer worked. The output of the job was ‘success’ as the emails had been successfully queued with the Database Mail sub-system.

After finding the emails would have been empty anyway, I disabled the job. But it made me wonder if there might be other jobs that were busy doing nothing – hour after hour – day after day.

Knowing the dangers of weakening the system, I did not want to fail a job or job-step just to flag a maintenance issue.

The lowest-risk change I could think of making (to the many, legacy, unfamiliar jobs) was to leave pertinent messages in the job history log using the PRINT command. For example:-

IF EXISTS (SELECT 1 FROM SomeTable)
   BEGIN 
      PRINT 'YES: there is new data'
      (Do meaningful stuff)
   END
   ELSE
      PRINT 'NO: there is no new data';

Then in the future I might notice that there is Never any new data!