Day: June 11, 2018

SQL server 2017 – IDENTITY_CACHE

Here we will discuss about IDENTITY_CACHE , IDENTITY_CACHE  set to ON (default) in SQL Server 2017.

In below SQL server 2017  below versions when data inserting in any table suppose server have been rebooted then it will insert the data then  there is a gap in the identity values:

In this case we need to disable the this feature on 2017

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF

Now try to do the same there is no gap in the identity values

SQL Server 2017 provides a new database scoped configuration option IDENTITY_CACHE. By default, this option is set to ON. In SQL Server, the database engine stores a series of values for the identity columns and it uses them as needed. The database engine generates identity values in a batch to reduce transaction log write and it only logs the max value of the batch. Sometimes when the identity column is being populated and an unexpected SQL Server restart/failover occurs, after a restart the next value of last batch max identity value will be used, so there will be a gap between identity columns values before the restart and after the restart. This feature was introduced in SQL Server 2012.

SQL server mirroring status

DECLARE @xml NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)

SET @xml = CAST((
SELECT
d.name as ‘td’,”,
mirroring_state_desc as ‘td’,”,
mirroring_role_desc as ‘td’,”,
mirroring_partner_instance as ‘td’,”,
mirroring_role_sequence as ‘td’,”,
case (m.mirroring_witness_state_desc) when ‘Unknown’ then ‘No Witness Server Configured’
else m.mirroring_witness_state_desc end as ‘td’,” ,
case(m.mirroring_witness_state_desc) when ‘Unknown’ then ‘No Witness Server Configured’
else m.mirroring_witness_name end as ‘td’,”,
log_reuse_wait_desc as ‘td’,”
FROM
sys.database_mirroring M inner join SYS.DATABASES d
on m.database_id = d.database_id
WHERE mirroring_state_desc is not null
ORDER BY d.name,mirroring_state_desc
FOR XML PATH(‘tr’), ELEMENTS ) AS NVARCHAR(MAX))

SET @body ='<html><body>
<h3>Database Mirroring Report</h3>
<table border = 1>
<tr>
<tr bgcolor=”gray”>
<th>Database</th>
<th>Mirroring_State</th>
<th>Mirroring_state_desc </th>
<th>Mirroring_partner_instance </th>
<th>mirroring_role_sequence </th>
<th>Mirroring_witness_state_desc </th>
<th>Mirroring_Witness_Instance</th>
<th>Log_reuse_wait_desc </th>
</tr>’

SET @body = @body + @xml +'</table></body></html>’

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘Profile name’, — enter Database Mail profile name
@recipients = ‘Group DL ‘, — list of Email recipients
@subject = ‘Database Mirroring Report’,
@body = @body,
@body_format =’HTML’;