Here are the steps to bring ‘SQL database suspect mode to normal mode’ :

  1. Open SQL Server Management Studio and connect your database
  2. Select the New Query option
  3. Turn off the suspect flag on the database and set it to EMERGENCY

EXEC sp_resetstatus ‘db_name’;
ALTER DATABASE db_name SET EMERGENCY

  1. Perform a consistency check on your suspect mode database

DBCC CHECKDB (‘database_name’)

  1. Bring the database into the Single User mode and roll back the previous transactions

ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

  1. Take a complete backup of the database
  2. Attempt the Database Repair allowing some data loss

DBCC CheckDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)

  1. Bring the database into the Multi-User mode

ALTER DATABASE database_name SET MULTI_USER

  1. Refresh the database server and verify the connectivity of database

Ideally, after these steps have been executed, users should be able to connect to the database without any issues. In the case of any data loss, you’ll have the database backup to restore from (step 4).

 

Advertisements
select distinct @@Servername as [Server Name], sd.name as [DB Name], HARS.role_desc as 'Role Type',
 rs.synchronization_state_desc, rs.synchronization_health_desc,
 rs.database_state_desc,
 CASE rs.is_suspended WHEN 0 THEN 'NO' WHEN 1 THEN 'YES'   END AS [Is Suspended],
 CASE rs.suspend_reason WHEN 0 THEN 'User Action'
 WHEN 1 THEN 'Suspend from Partner'
 WHEN 2 THEN 'Redo'
 WHEN 3 THEN 'Capture'
 WHEN 4 THEN 'Apply'
 WHEN 5 THEN 'Restart'
 WHEN 6 THEN 'Undo'
 WHEN 7 THEN 'Revalidation'
 WHEN 8 THEN 'Error in the calculation of the secondary-replica synchronization point' END AS [Suspended Reason],
 rs.suspend_reason_desc as [Suspended Reason], rs.last_commit_time
 from sys.dm_hadr_database_replica_states rs
 Join sys.databases  sd
 on rs.database_id = sd.database_id

JOIN sys.dm_hadr_availability_replica_states hars
 ON rs.group_id =  hars.group_id

JOIN sys.availability_replicas
 ON hars.group_id =  sys.availability_replicas.group_id

JOIN sys.dm_hadr_availability_group_states
 ON sys.dm_hadr_availability_group_states.group_id =  sys.availability_replicas.group_id

JOIN sys.dm_hadr_availability_replica_cluster_states
 ON sys.dm_hadr_availability_group_states.group_id =  sys.dm_hadr_availability_replica_cluster_states.group_id

JOIN sys.dm_hadr_database_replica_states HDRS
 ON sys.dm_hadr_availability_group_states.group_id =  HDRS.group_id

WHERE operational_state_desc IS NOT NULL
 AND rs.database_state_desc IS NOT NULL

Script:

SELECT TOP10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1, ((CASEqs.statement_end_offset WHEN-1 THENDATALENGTH(qt.TEXT) ELSEqs.statement_end_offset END- qs.statement_start_offset)/2)+1), qs.execution_count,qs.total_logical_reads, qs.last_logical_reads, qs.total_logical_writes, qs.last_logical_writes,qs.total_worker_time, qs.last_worker_time,qs.total_elapsed_time/1000000 total_elapsed_time_in_S, qs.last_elapsed_time/1000000 last_elapsed_time_in_S, qs.last_execution_time,qp.query_plan FROMsys.dm_exec_query_stats qs CROSSAPPLY sys.dm_exec_sql_text(qs.sql_handle) qt CROSSAPPLY sys.dm_exec_query_plan(qs.plan_handle) qp --based on your required edit your order by clause ORDERBYqs.total_logical_reads DESC-- logical reads -- ORDER BY qs.total_logical_writes DESC -- logical writes -- ORDER BY qs.total_worker_time DESC -- CPU time

The big problem with Shrink operation is that it increases fragmentation of the database to a very high value. Higher fragmentation reduces the performance of the database as reading from that particular table becomes very expensive.

How to reduce fragmentation?

One of the ways to reduce the fragmentation is to rebuild index on the database.  But fragmentation, increases size of a database. Rebuild indexes is not the best suggestion as that will create database grow again.

So, to reduce the size of a database Reindexing, Reorganizing is not a solution.

So to conclude, think well before you shrink your database.

 

 

SELECT
o.name AS ObjectName
, i.name AS IndexName
, i.index_id AS IndexID
, dm_ius.user_seeks AS UserSeek
, dm_ius.user_scans AS UserScans
, dm_ius.user_lookups AS UserLookups
, dm_ius.user_updates AS UserUpdates
, p.TableRows
, 'DROP INDEX ' + QUOTENAME(i.name)
+ ' ON ' + QUOTENAME(s.name) + '.'
+ QUOTENAME(OBJECT_NAME(dm_ius.OBJECT_ID)) AS 'drop statement'
FROM sys.dm_db_index_usage_stats dm_ius
INNER JOIN sys.indexes i ON i.index_id = dm_ius.index_id
AND dm_ius.OBJECT_ID = i.OBJECT_ID
INNER JOIN sys.objects o ON dm_ius.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
INNER JOIN (SELECT SUM(p.rows) TableRows, p.index_id, p.OBJECT_ID
FROM sys.partitions p GROUP BY p.index_id, p.OBJECT_ID) p
ON p.index_id = dm_ius.index_id AND dm_ius.OBJECT_ID = p.OBJECT_ID
WHERE OBJECTPROPERTY(dm_ius.OBJECT_ID,'IsUserTable') = 1
AND dm_ius.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
ORDER BY (dm_ius.user_seeks + dm_ius.user_scans + dm_ius.user_lookups) ASC
GO

Missing Indexes Script

Posted: January 12, 2018 in General
SELECT TOP 10
dm_mid.database_id AS DatabaseID,
dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
'CREATE INDEX [IX_' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + '_'
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''),', ','_'),'[',''),']','')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN '_'
ELSE ''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''),', ','_'),'[',''),']','')
+ ']'
+ ' ON ' + dm_mid.statement
+ ' (' + ISNULL (dm_mid.equality_columns,'')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN ',' ELSE
'' END
+ ISNULL (dm_mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + dm_mid.included_columns + ')', '') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC
GO
Note: To have a better performance, It is better not to have more than 10 Indexes on any table. Though this script provided Estimated Impact(Performance Improvement) after creating recommended Index. We should consider it only based on usage of columns in our query.

Please find MS SQL Cluster Installation Pre-Requisites as follows: and action items for each teams involved for this SQL Cluster Setup.

  1. SQL Server Virtual Name
  2. SQL Server Virtual IP
  3. Virtual Name & Virtual IP has to have DNS lookup & Reverse Lookup
  4. Storage:
  • User Database Data files (E:\)
  • User Database Log Files (L:\)
  • TempDB Data & Log Files (T:\)
  • Database Backup Files (F:\)

Action Items for AD Team:

Domain Controller (DC) and Windows Cluster Configuration:

  • Define the DC instance, if you don’t have one.
  • Designate computer names with DNS.
  • Create a domain user with domain controller permissions to update the DNS of these names.
  • Define the Windows cluster name and IP as follows: CC_CLUSTER (or choose your own name) – for example 10.33.16.100
  • Define the SQL cluster virtual name and IP as follows: DEFAULTSQL (or choose your own name) – for example 10.33.16.101
  • Grant CC_CLUSTER full permissions on DEFAULTSQL using advanced properties (ALT+M).
  • If using file share and a quorum witness file on the DC, create two shares on the DC with permissions to the SQL domain user.

Action Items for Windows Team:

Cluster nodes:

  • Define the Windows cluster. Set the cluster IP, for example: 10.33.16.100
  • In the cluster, set the File Share Witness file to be directed at the shared FS.
  • Perform custom verification of cluster omitting storage.

Action Items for SQL DBA Team:    

After verification succeeds, run the SQL failover installation.

  • Use a domain user for installing and for running the SQL server (this is necessary to access file/folder shares and change the IP in the DNS).
  • Set up temp folders to the local server; all other locations should point to shared storage.
  • Upon first server installation, test the connectivity to the DB.
  • Install an additional SQL server on an additional node/server, with an added node to the failover cluster option.
  • Provide passwords where needed.
  • Test the connectivity.
Script:

'DISABLE TRIGGER ' + OBJECT_NAME(object_id) + ' ON ' + OBJECT_NAME(parent_id)
 +  CHAR(13) +CHAR(10)
 + 'GO'

--OBJECT_NAME(parent_id) AS [Table],
 --OBJECT_NAME(object_id) AS TriggerName
 FROM
 sys.triggers
 WHERE
 is_disabled = 1 and
 object_id in
 (1111, 2222, 3333)

 

DECLARE @DB_USers TABLE
(DBName sysname, UserName sysname, LoginType sysname, AssociatedRole varchar(max),create_date datetime,modify_date datetime)
 
INSERT @DB_USers
EXEC sp_MSforeachdb
 
'
use [?]
SELECT ''?'' AS DB_Name,
case prin.name when ''dbo'' then prin.name + '' (''+ (select SUSER_SNAME(owner_sid) from master.sys.databases where name =''?'') + '')'' else prin.name end AS UserName,
prin.type_desc AS LoginType,
isnull(USER_NAME(mem.role_principal_id),'''') AS AssociatedRole ,create_date,modify_date
FROM sys.database_principals prin
LEFT OUTER JOIN sys.database_role_members mem ON prin.principal_id=mem.member_principal_id
WHERE prin.sid IS NOT NULL and prin.sid NOT IN (0x00) and
prin.is_fixed_role <> 1 AND prin.name NOT LIKE ''##%'''
 
SELECT
 
dbname,username ,logintype ,create_date ,modify_date ,
 
STUFF(
 
(
 
SELECT ',' + CONVERT(VARCHAR(500),associatedrole)
 
FROM @DB_USers user2
 
WHERE
 
user1.DBName=user2.DBName AND user1.UserName=user2.UserName
 
FOR XML PATH('')
 
)
 
,1,1,'') AS Permissions_user
 
FROM @DB_USers user1
where AssociatedRole is not null and AssociatedRole not like '' and AssociatedRole not like ' '
GROUP BY
 
dbname,username ,logintype ,create_date ,modify_date
 
ORDER BY DBName,username
Script:

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= 'Audit')
CREATE TABLE Audit
(
AuditID [int]IDENTITY(1,1) NOT NULL,
Type char(1), 
TableName varchar(128), 
PrimaryKeyField varchar(1000), 
PrimaryKeyValue varchar(1000), 
FieldName varchar(128), 
OldValue varchar(1000), 
NewValue varchar(1000), 
UpdateDate datetime DEFAULT (GetDate()), 
UserName varchar(128)
)
GO

DECLARE @sql varchar(8000), @TABLE_NAME sysname
SET NOCOUNT ON
/*DB Cursor to select all tables */
DECLARE db_cursor CURSOR FOR 
SELECT table_schema + '.'+ TABLE_NAME 
FROM INFORMATION_SCHEMA.Tables 
WHERE 
TABLE_TYPE= 'BASE TABLE' 
AND TABLE_NAME!= 'sysdiagrams'
AND TABLE_NAME!= 'Audit' 

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @TABLE_NAME 

WHILE @@FETCH_STATUS = 0 
BEGIN 

EXEC('IF OBJECT_ID (''' + @TABLE_NAME+ '_ChangeTracking'', ''TR'') IS NOT NULL DROP TRIGGER ' + @TABLE_NAME+ '_ChangeTracking')
SELECT @sql = 
'
create trigger ' + @TABLE_NAME+ '_ChangeTracking on ' + @TABLE_NAME+ ' for insert, update, delete
as
declare @bit int ,
@field int ,
@maxfield int ,
@char int ,
@fieldname varchar(128) ,
@TableName varchar(128) ,
@PKCols varchar(1000) ,
@sql varchar(2000), 
@UpdateDate varchar(21) ,
@UserName varchar(128) ,
@Type char(1) ,
@PKFieldSelect varchar(1000),
@PKValueSelect varchar(1000)
select @TableName = ''' + @TABLE_NAME+ '''
select @UserName = system_user ,
@UpdateDate = convert(varchar(8), getdate(), 112) + '' '' + convert(varchar(12), getdate(), 114)
/*Action */
if exists (select * from inserted)
if exists (select * from deleted)
select @Type = ''U''
else
select @Type = ''I''
else
select @Type = ''D''
/*get list of columns */
select * into #ins from inserted
select * into #del from deleted
/*Get primary key columns for full outer join */
select @PKCols = coalesce(@PKCols + '' and'', '' on'') + '' i.'' + c.COLUMN_NAME + '' = d.'' + c.COLUMN_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
/*Get primary key fields select for insert */
select @PKFieldSelect = coalesce(@PKFieldSelect+''+'','''') + '''''''' + COLUMN_NAME + '''''''' 
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
where pk.TABLE_NAME = @TableName
and CONSTRAINT_TYPE = ''PRIMARY KEY''
and c.TABLE_NAME = pk.TABLE_NAME
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME
select @PKValueSelect = coalesce(@PKValueSelect+''+'','''') + ''convert(varchar(100), coalesce(i.'' + COLUMN_NAME + '',d.'' + COLUMN_NAME + ''))''
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk , 
INFORMATION_SCHEMA.KEY_COLUMN_USAGE c 
where pk.TABLE_NAME = @TableName 
and CONSTRAINT_TYPE = ''PRIMARY KEY'' 
and c.TABLE_NAME = pk.TABLE_NAME 
and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME 
if @PKCols is null
begin
raiserror(''no PK on table %s'', 16, -1, @TableName)
return
end
select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName
while @field < @maxfield
begin
select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field
select @bit = (@field - 1 )% 8 + 1
select @bit = power(2,@bit - 1)
select @char = ((@field - 1) / 8) + 1
if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (''I'',''D'')
begin
select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field
select @sql = ''insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)''
select @sql = @sql + '' select '''''' + @Type + ''''''''
select @sql = @sql + '','''''' + @TableName + ''''''''
select @sql = @sql + '','' + @PKFieldSelect
select @sql = @sql + '','' + @PKValueSelect
select @sql = @sql + '','''''' + @fieldname + ''''''''
select @sql = @sql + '',convert(varchar(1000),d.'' + @fieldname + '')''
select @sql = @sql + '',convert(varchar(1000),i.'' + @fieldname + '')''
select @sql = @sql + '','''''' + @UpdateDate + ''''''''
select @sql = @sql + '','''''' + @UserName + ''''''''
select @sql = @sql + '' from #ins i full outer join #del d''
select @sql = @sql + @PKCols
select @sql = @sql + '' where i.'' + @fieldname + '' <> d.'' + @fieldname 
select @sql = @sql + '' or (i.'' + @fieldname + '' is null and d.'' + @fieldname + '' is not null)'' 
select @sql = @sql + '' or (i.'' + @fieldname + '' is not null and d.'' + @fieldname + '' is null)'' 
exec (@sql)
end
end
'
SELECT @sql
EXEC(@sql)
 
 FETCH NEXT FROM db_cursor INTO @TABLE_NAME 
 END 

CLOSE db_cursor 
DEALLOCATE db_cursor