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

DBCC Checkdb on SQL Server Express Edition

Express edition is a really good database engine although it has its limits like 10GB max size, no AlwaysOn and other stuff. But what is really annoying is that SQL Server Agent is not there. How are we supposed to run backups, checkdb, index maintenance, update statistics and so on without it? Not to mention that the business will want to run SSIS jobs and/or simple T-SQL scripts?

To get around this limitation I created simple Powershell scripts for maintenance purposes, they can be run from Windows Task Scheduler. I’ll start with the DBCC CHECKDB jobs as they are very important and later upload scripts for the other maint tasks.

This is run on the SQL Server host and you need to define a catalog where you want to log the results.

It needs to be run as a sysadmin in SQL Server Express, that is, you need to know what to do if CHECKDB throws an error, most often it involves calling Microsoft as fast as you can, or just restoring a recent good backup and trying to puzzle together the missing data.

The script writes to Windows event log so you need to pick up errors from there, it also sends all failed tests to an email you set  in the function, via your smtp server which you also have to set.

It optionally sends successful checks, that is the $sendOnlyFailed parameter, set it to 0 and it will send an email everytime it has done a checkdb.

Now it has been tested but I may have changed something while writing this, maybe the server is full of gremlins or my texteditor has a bug in it, whatever the reason, test this thoroughly on your test servers before putting it on a production system, I will not take any responsibility if something bad happens. Your servers are your responsibility.

# Run checkdb on all databases
$ServerInstance = "localhost"
$DateTimestring = Get-Date -Format yyyyMMdd
$logFilePath = "W:\SQLBackup\scripts\CHECKDBOutput\"

$sendOnlyFailed = 1 # Set to 0 to send status for all checks

# Send status email
Function StatusEmail {
    Param([string]$mailstring)
    Send-MailMessage -SmtpServer "YOU SMTP SERVER" -From "SERVERNAME OR SIMILAR" -To @('LIST OF EMAIL ADRESSES') -Subject "DBCC CHECKDB" -Body $mailstring
}

$sqldatabases = "select name from sys.databases"
$dbs = (Invoke-Sqlcmd -Serverinstance $ServerInstance -Query $sqldatabases).name

foreach ($db in $dbs){
    $logFile = $logFilePath+"CHECKDB_Log_"+$db+"_"+$DateTimestring+".txt"
    $sqlcheckdb = "dbcc checkdb($db) with all_errormsgs"
    Invoke-Sqlcmd -Serverinstance $ServerInstance -Query $sqlcheckdb -QueryTimeout 0 -Verbose *> $logFile

    #Read logfile and if we do not have "CHECKDB found 0 allocation errors and 0 consistency errors in database" somewhere we have an error
    $searchForError = "CHECKDB found 0 allocation errors and 0 consistency errors in database"
    if(Select-String -Pattern $searchForError -Path $logFile) {
        $parameters = @{
        'LogName'  = 'Application'
        'EventId'  = 8957
        'EntryType'  = 'Information'
        'Source' = 'MSSQL$SQLEXPRESS'
        'Message'  = 'DBCC CheckDB success'
        }
        Write-EventLog @parameters
        if($sendOnlyFailed -eq 0) {
            StatusEmail "DBCC CHECKDB for database $db succeeded"
        }
    }else{
        $parameters = @{
        'LogName'  = 'Application'
        'EventId'  = 8957
        'EntryType'  = 'Error'
        'Source' = 'MSSQL$SQLEXPRESS'
        'Message'  = 'DBCC CheckDB failed, please check SQL error log'
        }
        Write-EventLog @parameters
        StatusEmail "DBCC CHECKDB for database $db failed, investigate!"
    }
}

# Delete old log files, older than 15 days
$limit = (Get-Date).AddDays(-15)
Get-ChildItem -Path $logFilePath -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | Remove-Item 

 

AlwaysOn Readable secondary

Set up AlwaysOn to use readable secondaries and have the readers automatically connect to the secondary.

You need a couple of things done, nope it’s not totally automatic, first the developers have to decide which SQL they want/can run on a readonly database, only SELECT’s will work so they have to be careful.

When they have found the SQL’s that will work the connections for them will have to have an added parameter applicationintent=readonly

The thing you as a DBA have to do is configure AlwaysOn to actually route the readonly connections to the secondary.

See below for an example:

ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'SERVER1' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'SERVER1' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SERVER1.domain.com:1433'));

ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'SERVER2' WITH 
(SECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY));

ALTER AVAILABILITY GROUP [SQLAG]
MODIFY REPLICA ON
N'SERVER2' WITH 
(SECONDARY_ROLE (READ_ONLY_ROUTING_URL = N'TCP://SERVER2.domain.com:1433'));

ALTER AVAILABILITY GROUP [SQLAG] 
MODIFY REPLICA ON
N'SERVER1' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SERVER2','SERVER1')));

ALTER AVAILABILITY GROUP [SQLAG] 
MODIFY REPLICA ON
N'SERVER2' WITH 
(PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SERVER1','SERVER2')));
GO

Check that the routing list looks ok with

SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is",

 rl.routing_priority as "Routing Priority", ar2.replica_server_name as "RO Routed To",

 ar.secondary_role_allow_connections_desc, ar2.read_only_routing_url

 FROM sys.availability_read_only_routing_lists rl

 inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id

 inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id

 inner join sys.availability_groups ag on ar.group_id = ag.group_id

 ORDER BY ag.name, ar.replica_server_name, rl.routing_priority

I’ll write more later on how to detect how far behind the secondary is.

What does a DBA do really?

Good to have when the boss asks 🙂

 

  • Installations (Configuration/Disk/File placements/Maxdop/Memory/Collation/Version/Pachlevel/Cost threshold….)

 

  • Upgrades (See above…)

 

  • Configurations (Database options and instance configuration…….)

 

  • Sizing (SAN/Network/Memory/CPU/Have to be a hardware wiz too)

 

  • Performance tuning (Indexes/IO/CPU/Wait stats/SQL Plans/TSQL/EF/Plan cache/Buffer cache/…)

 

  • Backup (Time taken/IO Performance/Network Performance/Impact on Prod/Secondary backups/Configuration/Test/Disk/Networker/Scripts/Alarms/)

 

  • Restore (See backups, plus testing/Solve replication marker in log/logins/users/security/permissions/snapshots/Native/striped/Networker……)

 

  • Consistency (DBCC CheckDB/How to solve inconsistent database, a DBA’s biggest fear)

 

  • Clients (Advice/Run scripts/Configure instance-clients/Solve problems/Explain-Design-Architect solutions….answer all and any questions…)

 

  • Knowledge of (SAN/Disk/Network/CPU/Memory/Clients/Protocols/Problem solving/….)

 

  • Design databases (tables/hardware/firewall/indexes/triggers/views/stored procedures/functions/roles/……)

 

  • Security procedures and settings (Snapshot/sysadmin/dbo/read/write/….)

 

  • HA/DR solutions and how they work and their differences, see list below

 

  • AlwaysOn (2 or multiple nodes/differences Standard-Enterprise/Secondary reads/Secondary backups/Failover/Disaster recovery/Problem solving/

Readonly routing/Not synchronizing/Copy-only…….)

 

  • Replication (Merge/Transactional/Snapshot/When to use which and how to configure/Implications of moving a replicated database/Monitoring/Detecting problems)

 

  • Mirroring (Configure/Setup/Failover/Automated Failover Multiple DB’s/Monitor….)

 

  • Log shipping (Configuration/When to use/Fix issues….)

 

  • Stored Procedures(Coding/Speed up/Query Plans/”Parameter sniffing”/…)

 

  • Functions (When and when not to use/Performance/Coding/Fix/)

 

  • Sessions (Blocking processes/Deadlocks/How to solve them/How to monitor for them…)

 

  • Performance monitor (Perfmon/Spotlight/SQL Monitor/Scripts)

 

  • Compat mode (How it works/why/optimizer/TSQL compat)

 

  • Recovery mode (Full/bulk load/Simple and their respective implications)

 

  • Collations(tempdb/instance/how to fix (collate…) )

 

  • Tempdb (Number of files/trace flags/file placements/latch contention/full disk/…..)

 

  • Transactions (Explain to developers/track down long running/Fix long running)

 

  • TSQL (Understand developers code/Fix developers code/Make everything faster/…)

 

  • Isolations levels (read committed/read uncommitted/repeatable read/snapshot/serializable/…)

 

  • Differences between Standard and Enterprise edition (2005/2008/2008R2/2012/2014/2016/E.g. Rebuild clustered index online on a standard edition…)

 

  • Capacity planning (CPU/Disk/Network/Memory/PLE/Buffer cache/CLR/….)

 

  • Maintenance jobs (Backup/index update stats/index rebuild/index reorg/checkdb…..)