Unlock sa account

Always use AD accounts now, skip SQL accounts and do not use sa for anything other than whatever you just can’t solve with an AD sysadmin account.

Ok, sa is locked out, you have sysadmin permissions, so you can unlock it with the following.

ALTER LOGIN [sa] WITH CHECK_POLICY = OFF;

ALTER LOGIN [sa] WITH CHECK_POLICY = ON;

Nice huh!

Get a mail whenever a process is taking a long time to finish

Simple monitoring of long running processes, it will mail you data about the currently long running processes if it finds any. Prereqs are that you define a database mail profile.

(See further below how to setup a database mail profile.)

This script will mail whenever it finds anything that’s been running more than 10 seconds, change the line

AND DATEDIFF(ss, r.start_time, GETDATE()) > 10

to whatever you want, but lower than 10 seconds wont help much as this script should be run in SQL Agent and it has a lower limit of 10 seconds as wait between runs.  Run it as a simple TSQL script every 10 seconds or what is good for you.

IF EXISTS ( 
	  SELECT CAST(r.session_id AS NVARCHAR(10)), db_name(r.database_id)
	FROM sys.dm_exec_requests r
	CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, 
			sys.dm_exec_sessions s
	WHERE r.session_id > 50 
	and r.session_id=s.session_id
	and s.program_name not like '%DatabaseMail%'
	and r.session_id <> @@SPID 
	AND DATEDIFF(ss, r.start_time, GETDATE()) > 10	  
	  ) 
BEGIN	
	EXEC msdb.dbo.sp_send_dbmail
    	@profile_name = 'DatabaseMail',
    	@recipients = 'receiver@yourdomain.com',
    	@query = 'SELECT db_name(r.database_id), r.session_id, r.blocking_session_id, r.cpu_time,
		SUBSTRING(qt.text,(r.statement_start_offset/2) +1,
		(CASE WHEN r.statement_end_offset = -1
				THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
		ELSE 
				r.statement_end_offset
		END -r.statement_start_offset)/2) AS stmt_executing,
		s.login_name, 	s.HOST_NAME, 	s.PROGRAM_NAME,	s.host_process_id, 
		r.status, 	r.wait_time,	wait_type, 	r.wait_resource,
		r.total_elapsed_time,r.reads,r.writes, r.logical_reads
      		FROM sys.dm_exec_requests r
      		CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) as qt, 
			sys.dm_exec_sessions s
      		WHERE r.session_id > 50  --ensure that we omit SQL server processes
	 	AND r.session_id=s.session_id
      		AND DATEDIFF(ss, r.start_time, GETDATE()) > 10',
	@subject = 'Long running processes',
    	@attach_query_result_as_file = 1
END

 

Mail profile setup via script, change the mailhost and some other stuff to what you like before trying to send database mail.

--Enable Database Mail features on server

sp_configure 'show advanced',1
go
reconfigure
go
sp_configure 'Database Mail XPs',1
go
reconfigure
go

-- Create a Database Mail Account and Profile
-- !!! Remember to change display_name to your server\instance!!!

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MailAccount',
@description = '',
@email_address = 'noreply@domain.com',
@display_name = 'SERVERNAME\INSTANCENAME',
@mailserver_name = 'mailhost.domain.com' ;


-- Create a Database Mail profile

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'DatabaseMail',
@description = '' ;

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'DatabaseMail',
@account_name = 'MailAccount',
@sequence_number =1 ;

-- Grant access to the profile to public

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'DatabaseMail',
@principal_name = 'public',
@is_default = 1 ;

 

 

TDE Transparent database encryption

Start encrypting your data folks, better be safe than sorry and TDE is a simple way of keeping at least the mdf files and backups safe from snooping eyes.

In more technical terms TDE encrypts data at rest, so data that is on the disk is encrypted, it is decrypted when read into memory, so if you have access to the database through a SQL Server login you can see the decrypted data just as usual. Also the backups are encrypted so an attacker can’t just copy a .bak file and run with it, they’ll need the certificate to be able to decrypt it.

Better than nothing and you can set it up in AlwaysOn too, I have documented a simple way to do it below.

IMPORTANT!!!

Keep your passwords and certificates safe, preferably NOT on the DB servers. Ok? Good 🙂

Run this on a test AO a couple times until you are sure of all the steps.

--Primary
USE Master;
GO

-- Create a master key
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='reallystrongpassword';
GO

-- Create certificate
CREATE CERTIFICATE TEST_Cert
WITH 
SUBJECT='Test Cert';
GO

-- Backup to somewhere on disk
BACKUP CERTIFICATE TEST_Cert
TO FILE = 'Z:\dbbackup\TEST_Cert'
WITH PRIVATE KEY (file='Z:\dbbackup\TEST_Cert_Private',
ENCRYPTION BY PASSWORD='alsoreallystrongpassword')

--Secondary
USE Master;
GO

CREATE MASTER KEY ENCRYPTION
BY PASSWORD='reallystrongpassword'; --does not need to match primarys master key password
GO

CREATE CERTIFICATE TEST_Cert
FROM FILE='Z:\dbbackup\TEST_Cert'
WITH PRIVATE KEY (
FILE = 'Z:\dbbackup\TEST_Cert_Private',
DECRYPTION BY PASSWORD='alsoreallystrongpassword')


--Now check on both servers that we have the certificate installed
SELECT name, certificate_id, principal_id, pvt_key_encryption_type_desc, start_date, expiry_date
FROM sys.certificates;

--Also check with AlwaysOn dashboard that the databases are in sync

--Primary
USE DB_To_Be_Encrypted  -- I am sure your databases have better names...
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TEST_Cert;
GO

ALTER DATABASE DB_To_Be_Encrypted --This will take a while even if the command returns immediately
SET ENCRYPTION ON;
GO

--On primary we can run some commands to check logspace used, encryption progress and such
USE MASTER;
GO

dbcc sqlperf(logspace)
go

sp_helpdb DB_To_Be_Encrypted
go

-- Check encryotion process
SELECT db.name,db.is_encrypted,dm.encryption_state,dm.percent_complete,
dm.key_algorithm,dm.key_length
FROM sys.databases db
LEFT OUTER JOIN 
sys.dm_database_encryption_keys dm
ON db.database_id = dm.database_id;
GO

 

How do we remove TDE? Easy, below will clean up what we added above.

--Primary
alter database DB_To_Be_Decrypted set encryption off

-- When it has finished decrypting
use DB_To_Be_Decrypted
drop database encryption key


--On both primary and secondary
use master 
drop certificate TEST_Cert
drop master key

 

 

 

Local Server Groups

There is a cool feature in SQL Server Management Studio I’m not sure every DBA knows about. It’s the possibility to save groups of servers in SSMS and either just using this to open a connection to a single server faster or to run SQL code against the whole group, yes, awesome feature!

So open SSMS, and under View click on Registered Servers.

On Local Server Groups you can start by adding a group of SQL Servers, lets call them TEST, so create New Server Group called ‘TEST’

Now on TEST we right click and choose New Server Registration, add 2 or more test servers and make sure they are under TEST group. Ok? Cool!

To run SQL on them all you just right click on the TEST group and choose New Query. In the query window you will see that the bottom part is now a pink-ish colour, that means you are connected to a whole group of servers.

Try running some code, like

select name from sys.databases

The output will be a list of database names, (Duh!), but also a column showing which server these belong to, so SSMS took your code and ran it on each of the servers in the group, received the output from each and printed it on your Results pane.

I use this feature to easily run some sanity checks on all databases on all servers, like below script where I check if a database has auto_close or auto_shrink on, if page verify is NOT checksum or if it is lacking an owner, simple and basic checks.  We can expand this to check for missing backups or errors or whatever you check regularly.

SELECT s.name, s.is_auto_close_on, 
       s.is_auto_shrink_on, s.owner_sid as 'Owner', 
       s.page_verify_option_desc
FROM sys.databases AS s
WHERE s.is_auto_shrink_on = 1
or s.page_verify_option_desc <> 'CHECKSUM'
or s.is_auto_close_on = 1
or s.owner_sid  is null

 

 

Is a database being used?

Kinda tricky to find out sometimes, easiest is just to ask the application owner, but if that’s not an option below are a couple of ways to try to find out if it is being used.

Check with index stats

WITH LastActivity (ObjectID, LastAction) AS 
(
SELECT object_id AS TableName,
last_user_seek as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION 
SELECT object_id AS TableName,
last_user_scan as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
UNION
SELECT object_id AS TableName,
last_user_lookup as LastAction
FROM sys.dm_db_index_usage_stats u
WHERE database_id = db_id(db_name())
)
SELECT OBJECT_NAME(so.object_id) AS TableName,
MAX(la.LastAction) as LastSelect
FROM sys.objects so
LEFT
JOIN LastActivity la
on so.object_id = la.ObjectID
WHERE so.type = 'U'
AND so.object_id > 100
GROUP BY OBJECT_NAME(so.object_id)
ORDER BY OBJECT_NAME(so.object_id)

 Processes or plans being updated?

Run each query below and see if it is clear whether a database is being used or not.

SELECT @@ServerName AS server
,NAME AS dbname
,COUNT(STATUS) AS number_of_connections
,GETDATE() AS timestamp
FROM sys.databases sd
LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME

SELECT @@ServerName AS SERVER
,NAME
,login_time
,last_batch
,getdate() AS DATE
,STATUS
,hostname
,program_name
,nt_username
,loginame
FROM sys.databases d
LEFT JOIN sysprocesses sp ON d.database_id = sp.dbid
WHERE database_id NOT BETWEEN 0 AND 4 
AND loginame IS NOT NULL

SELECT *
FROM sys.dm_os_performance_counters
WHERE counter_name like 'Transactions/sec%'
and instance_name like 'MSSQL%';


SELECT
SUBSTRING(tx.[text],
(qs.statement_start_offset / 2) + 1,
(CASE WHEN qs.statement_end_offset =-1 THEN DATALENGTH(tx.text) ELSE qs.statement_end_offset END - qs.statement_start_offset)
/ 2 + 1) AS QueryText,
case when pl.query_plan LIKE '%<MissingIndexes>%' then 1 else 0 end as [Missing Indexes?],
qs.execution_count,
qs.total_worker_time/execution_count AS avg_cpu_time,
qs.total_worker_time AS total_cpu_time,
qs.total_logical_reads/execution_count AS avg_logical_reads,
qs.total_logical_reads,
qs.creation_time AS [plan creation time],
qs.last_execution_time [last execution time],
CAST(pl.query_plan AS XML) AS sqlplan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS pl
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS tx
WHERE pl.query_plan LIKE '%[DATABASENAME YOUR LOOKING FOR]%'
ORDER BY execution_count DESC OPTION (RECOMPILE);

Last login time per login

SELECT DB_NAME(r.database_id),max(login_time),login_name,max(last_request_end_time)
FROM sys.dm_exec_requests AS R JOIN sys.dm_exec_sessions S ON R.session_id=S.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS ST
WHERE plan_handle IS NOT NULL
GROUP BY DB_NAME(r.database_id),login_name

And if you still have no clue whether it is used or not just shut down the instance…. JUST KIDDING!!!!!! 🙂

 

Number of virtual log files per database

Here is an easy way to get the number of virtual log files (VLF) per database. If using AlwaysOn or mirroring make sure you run it on the primary as it will fail on the closed secondary databases.

Create Table #stage(
RecoveryUnitId int
, FileID int
, FileSize bigint
, StartOffset bigint
, FSeqNo bigint
, [Status] bigint
, Parity bigint
, CreateLSN numeric(38)
);

Create Table #results(
Database_Name sysname
, VLF_count int 
);

Exec sp_msforeachdb N'Use ?; 
Insert Into #stage 
Exec sp_executeSQL N''DBCC LogInfo(?)''; 

Insert Into #results 
Select DB_Name(), Count(*) 
From #stage; 

Truncate Table #stage;'

Select * 
From #results
Order By VLF_count Desc;

Drop Table #stage;
Drop Table #results;

So why do we want to know how many VLF’s we have? It’s actually quite important as it can have a large effect on performance and log backups.

Long story short, try to keep it below 1000 for large databases and for small above 10 at least. It has to do with how the log file is extended, for each extension we create new VLF’s and if we have too many there is an overhead managing these during backup/restores.

If you know your database is going to grow than it will probably also use more log, so change from the default 10% or 1MB extension to a more sensible value, disk is cheap these days and you don’t want to run out of log, the database will grind to a halt.  What’s a sensible value? Well for a 100GB database I use approx 10GB log file and set extend to 1GB if we have fast disks.

Make sure to grant Local Security Policy ‘Perform volume mainenance tasks’ to the SQL Server service user. Otherwise extending the file will take some time. Read more here https://www.sqlshack.com/perform-volume-maintenance-tasks-security-policy/

Database size and type

Simple script, shows database name, type of file and size of that file.

It does not count the system databases not any database that has a ‘snapshot’ in its name. Customize as you like 🙂

select sysdb.name,
CASE sysmaster.type_desc
WHEN 'ROWS' THEN 'DATA'
WHEN 'LOG' THEN 'LOG'
END as filetype,
sum((sysmaster.size/128)) as MB
from sys.databases sysdb
join sys.master_files sysmaster on sysdb.database_id = sysmaster.database_id
where sysdb.name not like '%snapshot%'
and sysdb.name not in ('tempdb','master','model','msdb')
group by sysdb.name, sysmaster.type_desc

 

What is holding the transaction log from shrinking?

If the database transaction log is ever growing, or you are trying to shrink it and it just sits there stubbornly refusing, run the below script and check the log_reuse_wait column. If it is saying anything other than LOG_BACKUP or NOTHING than you have to continue investigating.

select name, log_reuse_wait_desc from sys.databases

If it is LOG_BACKUP than just run one or more LOG backups, normally that will resolve it. You may have a problem that the active part of the log is at the end of the log file. You can check that with

dbcc loginfo()

Scroll down to the bottom, looking at the Status column, whenever there is a 2 in there it means that part of the log file is currently used, you can never shrink past a status 2 block counting from the bottom.

Check if you have any open transactions with

dbcc opentran()

or you can use the below script that does a more thourough check for long running transactions.

SELECT 	db_name(r.database_id), r.session_id, r.blocking_session_id, r.cpu_time,
		SUBSTRING(qt.text,(r.statement_start_offset/2) +1,
		(CASE WHEN r.statement_end_offset = -1
				THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
		ELSE 
				r.statement_end_offset
		END -r.statement_start_offset)/2) AS stmt_executing,
	s.login_name, r.percent_complete, r.start_time
	,CASE s.transaction_isolation_level
		WHEN 0 THEN 'Unspecified'
		WHEN 1 THEN 'ReadUncomitted'
		WHEN 2 THEN 'ReadComitted'
		WHEN 3 THEN 'Repeatable'
		WHEN 4 THEN 'Serializable'
		WHEN 5 THEN 'Snapshot'
	END as 'Isolation level'
	,	s.HOST_NAME, 	s.PROGRAM_NAME,	s.host_process_id, 
	r.status, 	r.wait_time,	wait_type, 	r.wait_resource,
	r.total_elapsed_time,r.reads,r.writes, r.logical_reads, r.plan_handle
	, (select query_plan from sys.dm_exec_query_plan(r.plan_handle)) as xml_plan
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, 
			sys.dm_exec_sessions s
WHERE r.session_id > 50 
and r.session_id=s.session_id
and r.session_id <> @@SPID
ORDER BY r.cpu_time desc

If log_reuse_wait is anything but LOG_BACKUP or NOTHING than have a look here at Microsoft site about factors that can delay log truncation.

If the log disk is full first investigate WHY it is filling up before just extending the file/disk, if it for example is because your log backups are failing than it will never stop growing and you are just wasting disk instead of trying to solve the real problem.

Get SQL Server port number with TSQL

Normally you’d go through SQL Server Configuration Manager and look at the TCP/IP settings but if you want to use just TSQL then try this

DECLARE @InstName VARCHAR(16)
DECLARE @RegLoc VARCHAR(100)
SELECT @InstName = @@SERVICENAME

IF @InstName = 'MSSQLSERVER'
  BEGIN
    SET @RegLoc='Software\Microsoft\MSSQLServer\MSSQLServer\SuperSocketNetLib\Tcp\'
  END
 ELSE
  BEGIN
   SET @RegLoc='Software\Microsoft\Microsoft SQL Server\' + @InstName + '\MSSQLServer\SuperSocketNetLib\Tcp\'
  END

EXEC [master].[dbo].[xp_regread] 'HKEY_LOCAL_MACHINE', @RegLoc, 'tcpPort'

Get SQL Server basic info

select 'Domain' as 'Parameter', DEFAULT_DOMAIN() as 'Value'
UNION ALL
SELECT 'Productversion', SERVERPROPERTY('productversion')
UNION ALL
SELECT 'Productlevel', SERVERPROPERTY ('productlevel')
UNION ALL
SELECT 'Edition', SERVERPROPERTY ('edition')
UNION ALL
SELECT 'BuildClrVersion' ColumnName, SERVERPROPERTY('BuildClrVersion') ColumnValue
UNION ALL
SELECT 'Collation', SERVERPROPERTY('Collation')
UNION ALL
SELECT 'CollationID', SERVERPROPERTY('CollationID')
UNION ALL
SELECT 'ComparisonStyle', SERVERPROPERTY('ComparisonStyle')
UNION ALL
SELECT 'ComputerNamePhysicalNetBIOS', SERVERPROPERTY('ComputerNamePhysicalNetBIOS') 
UNION ALL
SELECT 'Edition', SERVERPROPERTY('Edition')
UNION ALL
SELECT 'EditionID', SERVERPROPERTY('EditionID')
UNION ALL
SELECT 'EngineEdition', SERVERPROPERTY('EngineEdition')
UNION ALL
SELECT 'InstanceName', SERVERPROPERTY('InstanceName')
UNION ALL
SELECT 'IsClustered', SERVERPROPERTY('IsClustered')
UNION ALL
SELECT 'IsFullTextInstalled', SERVERPROPERTY('IsFullTextInstalled')
UNION ALL
SELECT 'IsIntegratedSecurityOnly', SERVERPROPERTY('IsIntegratedSecurityOnly')
UNION ALL
SELECT 'IsSingleUser', SERVERPROPERTY('IsSingleUser')
UNION ALL
SELECT 'LCID', SERVERPROPERTY('LCID')
UNION ALL
SELECT 'LicenseType', SERVERPROPERTY('LicenseType')
UNION ALL
SELECT 'MachineName', SERVERPROPERTY('MachineName')
UNION ALL
SELECT 'NumLicenses', SERVERPROPERTY('NumLicenses')
UNION ALL
SELECT 'ProcessID', SERVERPROPERTY('ProcessID')
UNION ALL
SELECT 'ProductVersion', SERVERPROPERTY('ProductVersion')
UNION ALL
SELECT 'ProductLevel', SERVERPROPERTY('ProductLevel')
UNION ALL
SELECT 'ResourceLastUpdateDateTime', SERVERPROPERTY('ResourceLastUpdateDateTime')
UNION ALL
SELECT 'ResourceVersion', SERVERPROPERTY('ResourceVersion')
UNION ALL
SELECT 'ServerName', SERVERPROPERTY('ServerName')
UNION ALL
SELECT 'SqlCharSet', SERVERPROPERTY('SqlCharSet')
UNION ALL
SELECT 'SqlCharSetName', SERVERPROPERTY('SqlCharSetName')
UNION ALL
SELECT 'SqlSortOrder', SERVERPROPERTY('SqlSortOrder')
UNION ALL
SELECT 'SqlSortOrderName', SERVERPROPERTY('SqlSortOrderName')