Category: DBA Utilities

DBA Utilities

How to re-queue an email in SQL that has been successfully sent at first time?

Let’s say you send an email from inside a SQL instance using something like the following:

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'SQLZEALOTS_Manager',  
    @recipients = 'Lkiran@xxx.com',  
    @body = 'This email Sent',  
    @subject = 'SqLZeaLoTs' ;

And recipient DOES successfully receive the email.

Now for one reason or another, they want the original email sent again (not an execution of msdb.dbo.sp_send_dbmail, but instead to basically re queue the original email to be sent again).

Step 1: Execute the following:

SELECT mailItem_id
       FROM [msdb].[dbo].[sysmail_mailitems]
       WHERE subject = 'SqLZeaLoTs'
       ORDER BY sent_date desc

Copy the mailItem_id value for the specific email you want to requeue, in this example 2738274.

Use msdb
GO
DECLARE @rc INT
,@sendmailxml VARCHAR(max)
,@mailID INT = 2738274 -- This is from the mailItem_ID column in [msdb].[dbo].[sysmail_mailitems] for the mail you want to resend. 2738274 is just an example.

SET @sendmailxml = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" xmlns:requests="http://schemas.microsoft.com/databasemail/requests"><MailItemId>'
                        + CONVERT(NVARCHAR(20), @mailID) + N'</MailItemId></requests:SendMail>'

    -- Send the send request on queue.
    EXEC @rc = sp_SendMailQueues @sendmailxml
    IF @rc <> 0
    BEGIN
       RAISERROR(14627, 16, 1, @rc, 'send mail')
END

If the query completes successfully, it has queued the original email again and the original recipient will receive another email with exactly the same subject and body.

Step 2 has only been confirmed to work on SQL 2008 R2 SP3.

Note: If you want to try it for later SQL Server versions, you may need to verify the code of step 2 by following the steps.

Script out the system procedure: msdb.dbo.sp_send_dbmail and look for the section near the bottom that executes sp_SendMailQueues. The most important piece to verify is the “SET @sendmailxml…” statement. The reason for this is that sp_SendMailQueues sends the email to a queue that is monitored by a broker that calls DatabaseMail.exe. It is entirely possible that different versions of SQL may have different versions of DatabaseMail.exe that requires the XML to be formatted differently so you want to make sure that the XML is formatted for the version required.

Please try this out on your environment and feedback if you have any comments.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

DBA Utilities, SCRIPT GALLERY

How to get FQDN (fully qualified domain name) from SQL Server?
A fully qualified domain name (FQDN) is referred to as an absolute domain name, is a domain name that specifies its exact location in the tree hierarchy of the Domain Name System (DNS).

You can see this information easily throughput Control Panel – > System and Security – > System – > Look out “Computer name, domain and workgroup settings”

But, I was looking for some way to identify this information without switching my windows (actually I was working on something when I was asked this information) . 

Select DEFAULT_DOMAIN()
EXEC master..xp_loginconfig 'Default Domain'

DECLARE @rootkey varchar(100) = 'HKEY_LOCAL_MACHINE',
@key varchar(100) = 'SYSTEM\ControlSet001\Services\Tcpip\Parameters\',
@value_name varchar(100) = 'Domain',@Domain_Name sysname -- output parameters
--Using xp_regread
EXEC master..xp_regread @rootkey = @rootkey, @key = @key, @value_name=@value_name
--Using xp_instance_regread
EXECUTE master.sys.xp_instance_regread
    @rootkey = @rootkey, @key = @key, @value_name=@value_name

/* Incase you need to assign to a variable*/
EXEC master..xp_regread @rootkey = @rootkey, @key = @key, @value_name=@value_name,@value=@Domain_Name OUTPUT 
SELECT @Domain_Name 'Domain Name'

Set @Domain_Name = ''

EXECUTE master.sys.xp_instance_regread
    @rootkey = @rootkey, @key = @key, @value_name=@value_name,@value=@Domain_Name OUTPUT 

SELECT @Domain_Name 'Domain Name'
Since, its domain information, I am not providing the screenshot, you may try it out yourself and share your thoughts.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

DBA Utilities, Powershell Scripts, SCRIPT GALLERY

How to get Disk space information in SQL Server

Problem Statement:

While troubleshooting an issue in SQL Server, I wanted to know the space information of the server. Since we did not have access to the server, we have come up with a simple script as below. I am sharing the script here hoping this helps you in similar situation.

Script:

declare @svrName varchar(255)
declare @sql varchar(400)
--User Options(By default Server name)
set @svrName = Cast(SERVERPROPERTY('MachineName') as varchar(255))
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@svrName,'''') 
	+ ' -Class Win32_Volume -Filter ''DriveType = 3'' '+
	'| select name,capacity,freespace |foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

--creating a temporary table
CREATE TABLE #Details
(line varchar(255))
--inserting disk name, total space and free space value in to temporary table
insert #Details
EXEC xp_cmdshell @sql

--Retrieve the Capacity values in GB from PS Script Details
select rtrim(ltrim(SUBSTRING(line,1,CHARINDEX('|',line) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('|',line)+1,
      (CHARINDEX('%',line) -1)-CHARINDEX('|',line)) )) as Float)/1024,0) as 'capacity(GB)'
      ,round(cast(rtrim(ltrim(SUBSTRING(line,CHARINDEX('%',line)+1,
      (CHARINDEX('*',line) -1)-CHARINDEX('%',line)) )) as Float) /1024 ,0)as 'freespace(GB)'
from #Details
where line like '[A-Z][:]%'
order by drivename

--Drop the temporary table
drop table #Details

See Also:

Using windows PowerShell to get the server disk space

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Always On Availability Group, DBA Utilities

Handy Troubleshooting T-SQL queries for Always On Availability Groups in SQL Server
Today, as part of a database migration from (one server to another server), wanted to check few things related to Always On Availability Groups to make sure all are intact. I am sharing those troubleshooting queries prepared for my verification with this blog post, hope this helps you as well.

How to identify available AGs to the server as Primary replica?

SELECT Groups.[Name] AS [AGname], [Primary_recovery_health_desc],[synchronization_health_desc],
automated_backup_preference_desc,dtc_support,db_failover,is_distributed, count(AGDatabases.database_name) [# Databases]
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
INNER JOIN sys.availability_databases_cluster AGDatabases ON Groups.group_id = AGDatabases.group_id
WHERE primary_replica = @@Servername
Group by Groups.[Name] , [Primary_recovery_health_desc],[synchronization_health_desc],
automated_backup_preference_desc,dtc_support,db_failover,is_distributed

How to identify available AGs to the server as Secondary replica?

SELECT Groups.[Name] AS [AGname], [secondary_recovery_health_desc],[synchronization_health_desc],
automated_backup_preference_desc,dtc_support,db_failover,is_distributed
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
WHERE primary_replica != @@Servername

How to identify AG – Databases distribution of a Primary replica?

SELECT
Groups.[Name] AS AGname,
AGD.database_name AS Databasename
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
INNER JOIN sys.availability_databases_cluster AGD ON Groups.group_id = AGD.group_id
WHERE primary_replica = @@Servername
ORDER BY AGname ASC, Databasename ASC;

How to identify AG – Databases distribution of a Secondary replica?

SELECT
Groups.[Name] AS AGname,
AGD.database_name AS Databasename
FROM sys.dm_hadr_availability_group_states States
INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id
INNER JOIN sys.availability_databases_cluster AGD ON Groups.group_id = AGD.group_id
WHERE primary_replica != @@Servername
ORDER BY AGname ASC, Databasename ASC;

A quick health check of your AG

SELECT DISTINCT
Groups.name AS AGname,
AGReplicas.replica_server_name,
AGStates.role_desc,
AGStates.synchronization_health_desc,
ISNULL(AGReplicaStates.suspend_reason_desc,'N/A') AS suspend_reason_desc
FROM sys.availability_groups Groups
INNER JOIN sys.dm_hadr_availability_replica_states as AGStates ON AGStates.group_id = Groups.group_id
INNER JOIN sys.availability_replicas as AGReplicas ON AGStates.replica_id = AGReplicas.replica_id
INNER JOIN sys.dm_hadr_database_replica_states as AGReplicaStates ON AGReplicas.replica_id = AGReplicaStates.replica_id
Order by Groups.name

In addition, SSMS provides a good dashboard to understand the Always On Availability Group and health status as below. I would highly recommend to refer for more details.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

DBA Utilities, SCRIPT GALLERY

SQL Server Inventory Queries – SQL Server Databases

Here is a T-SQL script used for inventory purpose on your SQL Server Databases.

Tested in: SQL Server 2016/SQL Server 2017

Script:

drop table if exists #SQLDatabaseInventory;
create table #SQLDatabaseInventory(
  ServerName varchar(128) default @@servername
, database_id bigint
, DBName varchar(128) default db_name()
, DBOwner varchar(128)
, CreateDate datetime2
, RecoveryModel varchar(12) 
, StateDesc varchar(60)
, CompatibilityLevel int
, DataFileSizeMB int
, LogFileSizeMB int
, DataUsageMB int
, IndexUsageMB int
, SizeMB decimal(17,2)
, Collation varchar(60)
, UserCount int
, RoleCount int
, TableCount int
, SPCount int
, UDFCount int
, ViewCount int
, DMLTriggerCount int
, IsCaseSensitive bit
, IsTrustWorthy bit
, LastFullBackupDate datetime2
, LastDiffBackupDate datetime2
, LastLogBackupDate datetime2
, IsTracked sql_variant);

insert into #SQLDatabaseInventory(database_id,DBName, DBOwner, CreateDate, RecoveryModel, StateDesc, CompatibilityLevel, IsCaseSensitive
, IsTrustWorthy, Collation, LastFullBackupDate, LastDiffBackupDate, LastLogBackupDate, IsTracked)
select database_id,name, suser_sname(owner_sid), create_date, recovery_model_desc, state_desc,compatibility_level
, IsCaseSensitive=CAST(CHARINDEX(N'_CS_', collation_name) AS bit), is_trustworthy_on, Collation_Name
, t.LastFullBackup, t.LastDiffBackup, t.LastLogBackup
, Case when is_cdc_enabled = 1 Then 'CDC' else '' End 
from master.sys.databases db
outer apply( SELECT
MAX(CASE WHEN b.type = 'D' THEN b.backup_finish_date END) AS LastFullBackup,
MAX(CASE WHEN b.type = 'I' THEN b.backup_finish_date END) AS LastDiffBackup,
MAX(CASE WHEN b.type = 'L' THEN b.backup_finish_date END) AS LastLogBackup
FROM msdb.dbo.backupset b 
where b.database_name = db.name
) t;

EXEC master.dbo.sp_msforeachdb'use [?]
update t set SizeMB=(select sum(size)/128. from dbo.sysfiles)
, DataUsageMB=x.DataUsageMB, IndexUsageMB=x.IndexUsageMB
   , DataFileSizeMB = u.DBSize, LogFileSizeMB = u.LogSize 
   , TableCount=y.TC, UDFCount=y.UC, SPCount = y.SC, ViewCount=y.VC
   , DMLTriggerCount=y.DC
   , UserCount = z.UC, RoleCount = z.RC
from #SQLDatabaseInventory t
   outer apply (
   SELECT SUM(case when df.type in (0,2,4) then df.size else 0 end)/128
   , SUM(case when df.type in (1,3) then df.size else 0 end)/128 
   FROM sys.database_files df 
   ) u(DBSize, LogSize)
   outer apply(select  DataUsageMB=sum(
    CASE
    When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
    When a.type <> 1 and p.index_id < 2 Then a.used_pages
    When p.index_id < 2 Then a.data_pages
    Else 0
    END)/128,
IndexUsageMB=(sum(a.used_pages)-sum(
    CASE
    When it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236) Then 0
    When a.type < > 1 and p.index_id < 2 Then a.used_pages
    When p.index_id < 2 Then a.data_pages
    Else 0
    END
    ))/128
    from sys.partitions p join sys.allocation_units a on p.partition_id = a.container_id
    left join sys.internal_tables it on p.object_id = it.object_id
   ) x
   outer apply 
   ( select SC=Sum(case Type when ''P'' then 1 else 0 end)
    , DC=Sum(case Type when ''TR'' then 1 else 0 end)
    , TC=Sum(case Type when ''U'' then 1 end)
    , UC= sum(case when Type in (''TF'', ''IF'', ''FN'') then 1 else 0 end)
    , VC=Sum(case Type when ''V'' then 1 else 0 end)
    from sys.objects where object_id > 1024
    and type in (''U'',''P'',''TR'',''V'',''TF'',''IF'',''FN'')
   ) y
   outer apply 
   ( select UC = sum(case when [Type] in (''G'',''S'',''U'') then 1 else 0 end)
      , RC = sum(case when Type = ''R'' then 1 else 0 end)
      from sys.database_principals
      where principal_id > 4
   ) z where t.DBName=db_name();
'
SELECT A.*,
Case when is_parameterization_forced = 1 then 'ON' ELSE 'OFF' END is_parameterization_forced,
Case when is_query_store_on = 1 Then 'ON' ELSE 'OFF' End is_query_store_on,
Case When is_broker_enabled = 1 Then 'ON' ELSE 'OFF' End is_broker_enabled,log_reuse_wait_desc,
Case when is_read_only = 1 Then 'READ-ONLY' Else 'ALL' End is_read_only,
Case When is_auto_close_on = 1 Then 'Enabled' Else 'Disabled' End is_auto_close_on,
Case When is_auto_shrink_on = 1 Then 'Enabled' Else 'Disabled' End is_auto_shrink_on,
Case When is_auto_update_stats_on  = 1 Then 'Enabled' Else 'Disabed' End is_auto_update_stats_on
FROM #SQLDatabaseInventory A
inner join sys.databases B
On B.database_id = A.database_id

I would really love to hear from you what else you would like to see to this list?

See Also:

https://sqlzealots.com/2020/04/26/sql-server-inventory-queries-sql-server-instances/

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!