How to check AG delay in SQL Server

SELECT  ar.replica_server_name, max(datediff(MINUTE, drs.last_commit_time, getdate())) as Delay_inMin

FROM sys.dm_hadr_database_replica_states AS drs INNER JOIN sys.availability_databases_cluster AS adc ON drs.group_id = adc.group_id

AND drs.group_database_id = adc.group_database_id INNER JOIN sys.availability_groups AS ag ON ag.group_id = drs.group_id INNER JOIN sys.availability_replicas AS ar ON drs.group_id = ar.group_id

AND drs.replica_id = ar.replica_id WHERE adc.database_name = 'DatabaseName'  --And replica_server_name <> 'UHSCMPRDROSQL01' And drs.is_local <> 1

group by  ar.replica_server_name 

How to move ms access database to SQL server

MOVE ACCESS TO SQL

A)  How to migrate access database to SQL server.

Steps:

1)      Copy all the access files from below path and keep it on another folder

2)      Run access migration tool and browse new access file path

3)      Set the target SQL version and click next.

 

4)      Click on Add databases and add access files and click next.

5) Select target SQL server and provide database name( new database will create). Click Next

6) Click on Link Tables and Click Next

7)      Click on Convert, Load and Migrate.

8)      Once migration done, please save the project and close.

9)      Now access database will act as front-end where SQL server will act as backend.

How to find out recent differential backup history in SQL Server

-- Get Backup History for required database
SELECT TOP 100
s.database_name,
m.physical_device_name,
CAST(CAST(s.backup_size / 1000000 AS INT) AS VARCHAR(14)) + ' ' + 'MB' AS bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' ' + 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,
CASE s.[type] WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END AS BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = DB_NAME() and s.type<>'L' and s.type='I'-- Remove this line for all the database
ORDER BY backup_start_date DESC, backup_finish_date
GO

How to fix orphaned SQL database users

To find out Orphaned users

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user then run below command:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by below command

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

update stats modified date in SQL server

SELECT  sp.stats_id ,
        OBJECT_NAME(s.object_id) AS tablename ,
        s.name ,
sp.last_updated,
        sp.rows ,
        sp.rows_sampled ,
        CAST(sp.rows_sampled AS NUMERIC) / CAST(sp.rows AS NUMERIC) * 100 AS Percentage_sampled ,
        CASE WHEN sp.modification_counter = 0 THEN 0
             ELSE CAST(sp.modification_counter AS NUMERIC)
                  / CAST(sp.rows AS NUMERIC) * 100
        END AS Percent_changed ,
        CASE WHEN sp.modification_counter = 0 THEN 'No Modifications'
ELSE
CAST(sp.modification_counter AS VARCHAR(20))
END AS Modification_counter
FROM    sys.stats AS s
        CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id)
        AS sp
WHERE   sp.last_updated <= DATEADD(DAY, -1, GETDATE())
        AND sp.rows > 10000

How to check fragmentation status in SQL server

IF OBJECT_ID('tempDB..#bbc') IS NOT NULL
    DROP TABLE #bbc

CREATE TABLE #bbc
    (
      DatabaseName VARCHAR(100) ,
      ObjectName VARCHAR(100) ,
      Index_id INT ,
      indexName VARCHAR(100) ,
      avg_fragmentation_percent FLOAT ,
      Page_Count INT ,
      IndexType VARCHAR(100) ,
      Action_Required VARCHAR(100) DEFAULT 'NA'
    )
go
INSERT  INTO #bbc
        ( DatabaseName ,
          ObjectName ,
          Index_id ,
          indexName ,
          avg_fragmentation_percent ,
 Page_Count,
          IndexType
        )
        EXEC master.sys.sp_MSforeachdb ' USE [?]

SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as ObjectName,

a.index_id, b.name as IndexName,

avg_fragmentation_in_percent,page_count, index_type_desc

-- , record_count, avg_page_space_used_in_percent --(null in limited)

FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a

JOIN sys.indexes AS b

ON a.object_id = b.object_id AND a.index_id = b.index_id

WHERE b.index_id <> 0 and avg_fragmentation_in_percent <>0'
go

UPDATE  #bbc
SET     Action_Required = 'Rebuild'
WHERE   avg_fragmentation_percent > 30
go

UPDATE  #bbc
SET     Action_Required = 'Reorganize'
WHERE   avg_fragmentation_percent BETWEEN 5 AND 30
go

DELETE #bbc
WHERE Page_Count < 25

go


SELECT  *
FROM    #bbc
WHERE   DatabaseName NOT IN ( 'master', 'model', 'msdb', 'tempdb' ) AND Page_Count > 10000
ORDER BY avg_fragmentation_percent

Find Index Name and columns

SELECT  S.NAME SCHEMA_NAME ,
        T.NAME TABLE_NAME ,
        I.NAME INDEX_NAME ,
        C.NAME COLUMN_NAME
FROM    SYS.TABLES T
        INNER JOIN SYS.SCHEMAS S ON T.SCHEMA_ID = S.SCHEMA_ID
        INNER JOIN SYS.INDEXES I ON I.OBJECT_ID = T.OBJECT_ID
        INNER JOIN SYS.INDEX_COLUMNS IC ON IC.OBJECT_ID = T.OBJECT_ID
        INNER JOIN SYS.COLUMNS C ON C.OBJECT_ID = T.OBJECT_ID
                                    AND IC.INDEX_ID = I.INDEX_ID
                                    AND IC.COLUMN_ID = C.COLUMN_ID
WHERE t.name = 'name'
ORDER BY I.NAME ,
        I.INDEX_ID ,
        IC.KEY_ORDINAL

GO

SELECT '['+Sch.name+'].['+ Tab.[name]+']' AS TableName,
Ind.[name] AS IndexName,
SUBSTRING(( SELECT ', ' + AC.name
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I
ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 0
ORDER BY IC.key_ordinal
FOR
XML PATH('')
), 2, 8000) AS KeyCols,
SUBSTRING(( SELECT ', ' + AC.name
FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I
ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id]
AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC
ON T.[object_id] = AC.[object_id]
AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id
AND IC.is_included_column = 1
ORDER BY IC.key_ordinal
FOR
XML PATH('')
), 2, 8000) AS IncludeCols
FROM sys.[indexes] Ind
INNER JOIN sys.[tables] AS Tab
ON Tab.[object_id] = Ind.[object_id]
INNER JOIN sys.[schemas] AS Sch
ON Sch.[schema_id] = Tab.[schema_id]
WHERE ind.name like 'idx_name_id_first_middle_last%'
ORDER BY TableName