Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 27 September 2018

TSQL Query to find analyze SQL memory and utilization and other important Querys for DBA

--------------Currently user running Query-------

use master
SELECT 'Currently Running Query'as RunningQuery,DATEDIFF(MINUTE, A.last_batch, GETDATE()) RunningTime,
A.spid, A.blocked, A.waittime, db_name(A.dbid) As dbname,B.text as sqlstatement,
A.hostname, A.loginame,A.program_name,A.last_batch,A.nt_username
FROM sys.sysprocesses A CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) B
WHERE A.dbid > 4


---------------------------------Batch requests/sec

DECLARE @BRPS BIGINT
SELECT @BRPS=cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Batch Requests/sec%'
WAITFOR DELAY '000:00:01'
SELECT  @@SERVERNAME AS ServerName,(cntr_value-@BRPS)/10.0 AS "Batch Requests/sec"
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Batch Requests/sec%'



-------------------------------------find most consumed objects/statements info------------------------------

SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.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
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
--ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
 ORDER BY qs.total_worker_time DESC -- CPU time

----------------------------------------------find most consumed stored procedure info
 SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
      ,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
      ,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
      ,cached_time
      ,last_execution_time
      ,execution_count
      ,total_worker_time / execution_count AS AVG_CPU
      ,total_elapsed_time / execution_count AS AVG_ELAPSED
      ,total_logical_reads / execution_count AS AVG_LOGICAL_READS
      ,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
      ,total_physical_reads  / execution_count AS AVG_PHYSICAL_READS
FROM sys.dm_exec_procedure_stats
where database_id<>32767 and DB_NAME(database_id) not in ('master','msdb')
ORDER BY AVG_LOGICAL_READS DESC




----------------------------------------------------------Table Row count----------

SELECT @@servername as servername,DB_NAME() AS [Database Name],T.name      AS [TABLE NAME],
       I.row_count AS [ROWCOUNT]
FROM   sys.tables AS T
       INNER JOIN sys.dm_db_partition_stats AS I
               ON T.object_id = I.object_id
 
                  AND I.index_id < 2
ORDER  BY I.row_count DESC


-------------------------------------------blocking info with dbcc inputbuffer

select distinct top 1 @id=spid from sys.sysprocesses where blocked<>0

set @x=@id
if @x>0

begin

select @x as blockingspid

dbcc inputbuffer(@x)

end

else

select  'nothing blocked' as blockingstatus

---------------------------------------Page life expectancy

SELECT @@SERVERNAME AS ServerName,[object_name],
[counter_name],
[cntr_value] FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'

Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references.

check Page Life Expectancy. If its value is low (below 300 seconds), this is a clear indication of memory pressure.


--------------------------------------Lazy writer------------

SELECT object_name, counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Lazy writes/sec'

The lazy writer is a process that periodically checks the available free space in the buffer cache between two checkpoints and ensures that there is always enough free memory.

When the lazy writer determines free pages are needed in the buffer for better performance, it removes the old pages before the regular checkpoint occurs

If a dirty data page (a page read and/or modified) in the buffer hasn’t been used for a while, the lazy writer flushes it to disk and then marks as free in the buffer cache

The Lazy writes metric is defined as "Number of times per second SQL Server relocates dirty pages from buffer pool (memory) to disk"


The threshold value for Lazy Writes is 20

If the Lazy Writes value is constantly higher than 20, to be sure that the server is under memory pressure, check Page Life Expectancy. If its value is low (below 300 seconds), this is a clear indication of memory pressure.

------------------------------check point--------------------------------------------------------------------------------------------------------------------------------------------------------

 A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction

at a check point, which can be automatic (occurs automatically to meet the recovery interval request) , indirect (occurs automatically to meet the database target recovery time),
manual (occurs when the CHECKPOINT command is executed), and internal (occurs along with some server-level operations, such as backup creation)

--------------------------------------------------------Latch--------------------------

a latch is a lightweight synchronization object used by the Storage Engine to protect memory structures used internally by SQL Server.

3 Different types of Latches:

IO Latches
Buffer Latches (BUF)
Non-Buffer Latches (Non-BUF)

Latch Modes

Keep
Shared
Update
Exclusive
Destroy

-------------------------------Free list stalls/sec---------------------------

SELECT  'Free list stalls/sec',
        cntr_value
FROM    sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%' and counter_name LIKE  '%Free list stalls/sec%'

----------------------------------------find number of core is SQL using-----------

select scheduler_id, cpu_id, status, is_online
from sys.dm_os_schedulers
where status = 'VISIBLE ONLINE'

------------------------------------To find out Unused DB info------------


SELECT @@ServerName AS server
 ,NAME AS dbname
 ,COUNT(STATUS) AS number_of_connections
 ,GETDATE() AS queryexecutedtimestamp
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 TOP 10000 s.[database_id]
,d.name
,d.create_date
,s.[index_id]
,s.[object_id]
,s.[user_seeks]
,s.[user_scans]
,s.[user_lookups]
,s.[user_updates]
,s.[last_user_seek]
,s.[last_user_scan]
,s.[last_user_lookup]
,s.[last_user_update]


  FROM [master].[sys].[dm_db_index_usage_stats] as s right outer join sys.databases d on s.database_id = d.database_id
  where d.database_id > 4
  order by d.name


select d.name, x1 =

(select X1= max(bb.xx)

from (

    select xx = max(last_user_seek)

        where max(last_user_seek) is not null

    union all

    select xx = max(last_user_scan)

        where max(last_user_scan) is not null

    union all

    select xx = max(last_user_lookup)

        where max(last_user_lookup) is not null

    union all

        select xx = max(last_user_update)

        where max(last_user_update) is not null) bb)

FROM master.dbo.sysdatabases d

left outer join

sys.dm_db_index_usage_stats s

on d.dbid= s.database_id

 where d.dbid > 4

group by d.name

-----------------------DMV query that will tell us high level information of disk latency


SELECT
    *
   ,wait_time_ms/waiting_tasks_count AS 'Avg Wait in ms',(wait_time_ms/waiting_tasks_count)/1000 as 'AVG Wait in  Sec',
(wait_time_ms/waiting_tasks_count)/1000/60 as 'AVG Wait in Mint'
FROM
   sys.dm_os_wait_stats
WHERE
   waiting_tasks_count > 0
ORDER BY
   wait_time_ms DESC

----------------------------------------Number of user connections info ---------------

SELECT ConnectionStatus = CASE WHEN dec.most_recent_sql_handle = 0x0
        THEN 'Unused'
        ELSE 'Used'
        END
    , CASE WHEN des.status = 'Sleeping'
        THEN 'sleeping'
        ELSE 'Not Sleeping'
        END
    , ConnectionCount = COUNT(1)
FROM sys.dm_exec_connections dec
    INNER JOIN sys.dm_exec_sessions des ON dec.session_id = des.session_id
GROUP BY CASE WHEN des.status = 'Sleeping'
        THEN 'sleeping'
        ELSE 'Not Sleeping'
        END
    , CASE WHEN dec.most_recent_sql_handle = 0x0
        THEN 'Unused'
        ELSE 'Used'
        END;

---------------------------------------------Adding tempdb files based on core/processor-------------------------


Select  @@servername as servername,DB_NAME(mf.database_id) database_name
, mf.name logical_name, mf.file_id
, CONVERT (DECIMAL (20,2)
, (CONVERT(DECIMAL, size)/128)) as [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(DECIMAL(20,2)
,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN CONVERT(DECIMAL(20,2)
, (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, physical_name from sys.master_files mf
where database_id =2 and type_desc= 'rows'

SELECT  @@servername as servername,cpu_count AS logicalCPUs FROM sys.dm_os_sys_info



--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev2.ndf' , SIZE =8MB , FILEGROWTH = 5MB) --<<--Update the data file location/Size/AutoGrowth
--GO

--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev3',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev3.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO

--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev4',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev4.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO

--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev5',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev5.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO

--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev6',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev6.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO
---ETC, add files as per processors count

------------------------------------------Enable all Agent Jobs-----------------

SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N'''
+ cast(job_id as varchar(40)) + ''', @enabled=1'
FROM msdb..DBA_Agent_jobs_Snapshot

--Run output from above


------------------------------------------Disable all Agent Jobs

Select * into msdb..DBA_Agent_jobs_Snapshot
FROM msdb..sysjobs
WHERE ENABLED = 1

SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N'''
+ cast(job_id as varchar(40)) + ''', @enabled=0'
FROM msdb..DBA_Agent_jobs_Snapshot

--Run output from above


-----------------------------------Kill the unused session on db bulk------------------------------------------


ALTER DATABASE teis_wfl SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE teis_wfl SET MULTI_USER



----------------------------------------------------------------DB Mail for Backup status report---------------------------------

  DECLARE @tableHTML  NVARCHAR(MAX) ;
  DECLARE @SER NVARCHAR(20);
  DECLARE @StrSubject VARCHAR(100);
  SET @SER=@@SERVERNAME

  SELECT @StrSubject = 'Texmo DB Server Backup Status Report ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'


SET @tableHTML =
    N'<H1>Database Backup Status Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Server_Name</th><th>Database_Name</th><th>DaysSinceLastBackup</th>' +
    N'<th>Last FullBackup Date & time</th><th>Backup_Status_Today</th><th>DB_Status</th></tr>' +
 
    CAST ( ( SELECT td = @SER,       '',td = B.name,       '',
                    td = ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER'), '',
                    td =ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER'), '',
                    td = case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(MST.backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
                                                                                                          when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(MST.backup_finish_date)))), 'NEVER')=0 then 'Completed'
  else 'Not completed' end,d.state_desc
                                  FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
join sys.databases d ON B.name=d.name
where B.name not in ('tempdb')
 GROUP BY B.name,B.status,d.state_desc
  ORDER BY B.name
              FOR XML PATH('tr'), TYPE 

    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

PRINT @tableHTML

EXEC msdb.dbo.sp_send_dbmail  @profile_name='Database Administrator',
    @recipients = 'JAR@texmo.net;Shalvansha.Razak@texmocastings.com;RSK@texmo.net',
--@recipients = 'JAR@texmo.net',
    @subject = @StrSubject,
    @body = @tableHTML,
    @body_format = 'HTML' ;




DECLARE @tableHTML  NVARCHAR(MAX) ;
  DECLARE @SER NVARCHAR(20);
  DECLARE @StrSubject VARCHAR(100);
  SET @SER=@@SERVERNAME

  SELECT @StrSubject = 'Texmo DB Server Backup Status Report ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'


SET @tableHTML =
    N'<H1>Database Backup Status Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Server_Name</th><th>Database_Name</th><th>DaysSinceLastBackup</th>' +
    N'<th>Last FullBackup Date & time</th><th>Backup_Status_Today</th><th>DB_Status</th><th>Backup_type</th><th>Backup Size in MB</th><th>Backup Size in GB</th></tr>' +
 
    CAST ( ( SELECT  td = @SER,       '',td = LTRIM(RTRIM(B.name)),       '',
                    td = ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER'), '',
                    td =ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER'), '',
                    td = case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(MST.backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
                                                                                                          when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(MST.backup_finish_date)))), 'NEVER')=0 then 'Completed'
  else 'Not completed' end,  '',td=d.state_desc,    '',
td= CASE MST.type
       WHEN 'D' THEN 'Full Database'
       WHEN 'L' THEN 'Log'
       WHEN 'I' THEN 'Differential'
       WHEN 'F' THEN 'File Level'
       WHEN 'G' THEN 'File Level Differential'
       WHEN 'P' THEN 'Partial'
       WHEN 'Q' THEN 'Differential partial'     
   END,  '',
   td= LTRIM(RTRIM(convert(varchar,cast(MST.backup_size/1024/1024 as money),10))),  '',td= LTRIM(RTRIM(convert(varchar,cast(MST.backup_size/1024/1024 as INT),10)))/1024,    ''
 
                                  FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
join sys.databases d ON B.name=d.name
where B.name not in ('tempdb')
and  (CONVERT(datetime, MST.backup_start_date, 102) >= GETDATE() - 1)
 GROUP BY B.name,B.status,d.state_desc,MST.type,convert(varchar,cast(MST.backup_size/1024/1024 as INT),10),convert(varchar,cast(MST.backup_size/1024/1024 as money),10)
  ORDER BY B.name,B.status,d.state_desc,MST.type,convert(varchar,cast(MST.backup_size/1024/1024 as INT),10),convert(varchar,cast(MST.backup_size/1024/1024 as money),10)
              FOR XML PATH('tr'), TYPE 

    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

PRINT @tableHTML

EXEC msdb.dbo.sp_send_dbmail  @profile_name='Database Administrator',
    --@recipients = 'JAR@texmo.net;Shalvansha.Razak@texmocastings.com;RSK@texmo.net',
@recipients = 'JAR@texmo.net',
    @subject = @StrSubject,
    @body = @tableHTML,
    @body_format = 'HTML' ;
----------------------------------------------------Backup status query----------------------------------------------------


SELECT     B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
    ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER') as 'Last FullBackup Date & time',case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
                                                                                                          when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(backup_finish_date)))), 'NEVER')=0 then 'Completed'
  else 'Not completed' end as BackupStatus
    FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST

    ON MST.database_name = B.name AND MST.type = 'D'
where B.name not in ('tempdb')
 GROUP BY B.name,B.status
  ORDER BY B.name


SELECT     @@SERVERNAME AS ServerName,B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
    ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER') as 'Last FullBackup Date & time',case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
                                                                                                          when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
    MAX(backup_finish_date)))), 'NEVER')=0 then 'Completed'
  else 'Not completed' end as Backup_Status_Today,d.state_desc,
  CASE MST.type
       WHEN 'D' THEN 'Full Database'
       WHEN 'L' THEN 'Log'
       WHEN 'I' THEN 'Differential'
       WHEN 'F' THEN 'File Level'
       WHEN 'G' THEN 'File Level Differential'
       WHEN 'P' THEN 'Partial'
       WHEN 'Q' THEN 'Differential partial'     
   END AS backup_type,
   convert(varchar,cast(MST.backup_size/1024/1024 as money),10) as 'Backup Size in MB',convert(varchar,cast(MST.backup_size/1024/1024 as INT),10) /1024 as 'Backup Size in GB'
    FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
join sys.databases d ON B.name=d.name


 --------------------------------------Databases not backed up within 24 hrs----------


DECLARE
  @dblist NVARCHAR(MAX) = N'',
  @c CHAR(2) = CHAR(13) + CHAR(10), @t CHAR(1) = CHAR(9),
  @s SYSNAME = @@SERVERNAME;

SELECT @dblist += @c + @c + @s + @c + @t + db + @c + @t
  + COALESCE(CONVERT(CHAR(10), d, 120) + ' ' + CONVERT(CHAR(8), d, 108), 'NULL')
  + @c + @t + CONVERT(VARCHAR(11), age)
FROM
(
  SELECT
   bs.database_name AS db,
   MAX(bs.backup_finish_date) AS d,
   DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS age
-------------^^^^ please don't use lazy shorthand like hh
  FROM    msdb.dbo.backupset AS bs
  WHERE [type] = 'D'
  GROUP BY database_name
  HAVING (MAX(backup_finish_date) < DATEADD(HOUR, -24, GETDATE()))

UNION ALL -- why UNION? By definition there are no duplicates to filter.
          -- in fact you could re-write this without two separate queries at all.

  SELECT
    name AS db,
    NULL AS d,
    9999 AS age
  FROM
    master.sys.databases AS d
------ don't use sysdatabases - old and deprecated
  WHERE name <> N'tempdb' AND NOT EXISTS
  (
    SELECT 1 FROM msdb.dbo.backupset
      WHERE database_name = d.name
  )
) AS x
ORDER BY db;

IF @dblist > N''
BEGIN
  PRINT @dblist;
  --EXEC msdb.dbo.sp_send_dbmail
  --  @recipients = 'dba@someemailaddress.com',
  --  @subject = N'Databases not backed up within 24 hrs',
  --  @body = @dblist;
END

----------------------------------------------Missing index Bulk-------------------------

EXEC sp_MSforeachdb 'USE ? SELECT ''?''dbname,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 '

------------------------------------To send DB offline status report via db mail------------------------------------

if(select count(*) from sys.databases where state_desc<>'Online')>0
Begin

DECLARE @table  NVARCHAR(MAX) ;

SET @table =
    N'<H1>Offline Databases Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Database Name</th><th>Database Status</th></tr>' +
    CAST ( ( Select td=name, '',td=state_desc from sys.databases where state_desc<>'Online'
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) )    +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @profile_name='ProfileName', --Change to your Profile Name
      @recipients='email@domain.com;email1@domain.com', --Put the email address of those who want to receive the e-mail
    @subject = 'Offline Databases Report',
    @body = @table,
    @body_format = 'HTML' ;

END
Else Print 'All Databases are Online'

-------------------------------------Index fragmentation report-----------------------


SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,CASE
        WHEN indexstats.avg_fragmentation_in_percent < 10
            THEN 'NOTHING'
        WHEN indexstats.avg_fragmentation_in_percent >= 10
            AND indexstats.avg_fragmentation_in_percent < 30
            THEN 'REORGANIZE'
        WHEN indexstats.avg_fragmentation_in_percent >= 30
            THEN 'REBUILD'
        END as fragmentation_comments,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() and indexstats.page_count>=1000 and indexstats.avg_fragmentation_in_percent>=10
ORDER BY indexstats.avg_fragmentation_in_percent desc

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,CASE
        WHEN indexstats.avg_fragmentation_in_percent < 10
            THEN 'NOTHING'
        WHEN indexstats.avg_fragmentation_in_percent >= 10
            AND indexstats.avg_fragmentation_in_percent < 30
            THEN 'REORGANIZE'
        WHEN indexstats.avg_fragmentation_in_percent >= 30
            THEN 'REBUILD'
        END as fragmentation_comments,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (14, NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = 14 and indexstats.page_count>=1000 and indexstats.avg_fragmentation_in_percent>=10
ORDER BY indexstats.avg_fragmentation_in_percent desc

Fragmentation is less than 10% – no de-fragmentation is required. It is generally accepted that in majority of environments index fragmentation less than 10% in negligible and its performance impact on the SQL Server is minimal.
Fragmentation is between 10-30% – it is suggested to perform index reorganization
Fragmentation is higher than 30% – it is suggested to perform index rebuild
-------------------------------------------------To view statistics status info against Database-------------------------
SELECT Distinct @@servername as Servername,db_name(db_id()) as DB_Name, SCHEMA_NAME(schema_id) SchemaName,t.name TableName, s.[name] StatName, STATS_DATE(t.object_id,s.[stats_id]) LastUpdated ,'['+SCHEMA_NAME(schema_id)+'].['+t.name+']' AS SchemaTable
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
    ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'
and convert(varchar(10), STATS_DATE(t.object_id,s.[stats_id]),121)<>'2018-11-15'

SELECT  Distinct 'Update Statistics'+ '['+SCHEMA_NAME(schema_id)+'].['+t.name+']' AS SchemaTable
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
    ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'
and convert(varchar(10), STATS_DATE(t.object_id,s.[stats_id]),121)<>'2018-11-15'

-------------------------------------------Recovery pending----------------------------


SELECT name, state_desc from sys.databases
where state_desc='RECOVERY_PENDING'

exec sp_resetstatus dbname

ALTER DATABASE dbname SET ONLINE

DBCC CHECKDB('dbname') WITH NO_INFOMSGS

ALTER DATABASE dbname SET SINGLE_USER

DBCC CHECKDB('dbname',REPAIR_REBUILD)

ALTER DATABASE dbname SET MULTI_USER

https://promx.net/en/2016/11/microsoft-sql-server-database-repair-following-recovery-pending-status/
-----------------------------------------Alter Query for move tempdb secondary files-----------------------

Transact-SQL

SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
+ ' FILENAME = ''Z:\MSSQL\DATA\' + f.name
+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END
+ ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb');

------------------------------------------------to check what job is running at a particular time----------------------


SELECT * FROM
(
 SELECT JobName, RunStart, DATEADD(second, RunSeconds, RunStart) RunEnd, RunSeconds,RunSeconds/60 Mints,RunSeconds/60/60 hrs
 FROM
 (
  SELECT j.name AS 'JobName',
    msdb.dbo.agent_datetime(run_date, run_time) AS 'RunStart',
    ((jh.run_duration/1000000)*86400)
    + (((jh.run_duration-((jh.run_duration/1000000)*1000000))/10000)*3600)
    + (((jh.run_duration-((jh.run_duration/10000)*10000))/100)*60)
    + (jh.run_duration-(jh.run_duration/100)*100) RunSeconds
  FROM msdb.dbo.sysjobs j
  INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
  WHERE jh.step_id=0 --The Summary Step
 ) AS H
) AS H2
WHERE '2018-10-22 03:30:00' BETWEEN RunStart AND RunEnd
ORDER BY JobName, RunEnd

------------------------------------------------Log reuse monitor---------------------------------------------

dbcc sqlperf(logspace)

Select  @@SERVERNAME AS ServerName,name,log_reuse_wait_desc,recovery_model_desc,state_desc,* from sys.databases
where log_reuse_wait_desc<>'NOTHING'

SELECT instance_name
 , [Data File(s) Size (KB)] * 1.0 / 1024 [Data File(s) Size (MB)]
 , [Log File(s) Size (KB)] * 1.0 / 1024 [Log File(s) Size (MB)]
 , [Log File(s) Used Size (KB)] * 1.0 / 1024 [Log File(s) Used Size (MB)]
 , [Percent Log Used]
 , log_reuse_wait_desc
FROM (   SELECT os.counter_name, os.instance_name, os.cntr_value
 , db.log_reuse_wait_desc
   FROM sys.dm_os_performance_counters os
   JOIN sys.databases db ON os.instance_name = db.name
   WHERE os.counter_name IN
   (
       'Data File(s) Size (KB)'
       , 'Log File(s) Size (KB)'
       , 'Log File(s) Used Size (KB)'
       , 'Percent Log Used'
   )
     --AND os.instance_name = 'TLogTruncate'
) as SourceTable
PIVOT (
MAX(cntr_value) FOR counter_name IN
  ([Data File(s) Size (KB)]
       , [Log File(s) Size (KB)]
       , [Log File(s) Used Size (KB)]
       , [Percent Log Used])) as PivotTable



--------------------------------------------To see DB growth for all DB-----------------------------------------------

SELECT   'Database Name' = DB_NAME(database_id)
,'FileName' = NAME
,FILE_ID
,'size' = CONVERT(NVARCHAR(15), CONVERT(BIGINT, size) * 8) + N' KB'
,'maxsize' = (
CASE max_size
WHEN - 1
THEN N'Unlimited'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, max_size) * 8) + N' KB'
END
)
,'growth' = (
CASE is_percent_growth
WHEN 1
THEN CONVERT(NVARCHAR(15), growth) + N'%'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, growth) * 8) + N' KB'
END
)
,'type_desc' = type_desc
FROM sys.master_files
ORDER BY database_id



SELECT @@SERVERNAME as ServerName,*
FROM (SELECT [DBName],YEAR([Dateofobservation]) [Year],
        convert(varchar(10),[Dateofobservation],121) [Date],
       SUM([OccupiedinMB]) [OccupiedinMB]
      FROM [db_admin].[dbo].[DBGrowth]
      GROUP BY [DBName],YEAR([Dateofobservation]),
       convert(varchar(10),[Dateofobservation],121)) AS MontlySalesData
PIVOT( SUM([OccupiedinMB]) 
    FOR [Date] IN (  [2018-11-29],[2018-11-30],[2018-12-01],[2018-12-02],[2018-12-03],[2018-12-04],[2018-12-05], [2018-12-06],[2018-12-07],[2018-12-08],[2018-12-09],[2018-12-10],[2018-12-11]
,[2018-12-12],[2018-12-13],[2018-12-14],[2018-12-15],[2018-12-16],[2018-12-17],[2018-12-18],[2018-12-19],[2018-12-20],[2018-12-21],[2018-12-22],[2018-12-23],[2018-12-24]
,[2018-12-25],[2018-12-26],[2018-12-27],[2018-12-28],[2018-12-29],[2018-12-30],[2018-12-31]
     
       )) AS MNamePivot




Declare @s varchar(max)=''
Declare @i int
Declare @j int

set @i=1

select @j=right(convert(varchar(7),getdate(),121),2)

select @j as j

set @s='SELECT @@SERVERNAME  as ServerName,*'
set @s=@s +'FROM (SELECT [DBName],YEAR([Dateofobservation]) [Year],
        convert(varchar(10),[Dateofobservation],121) [Date],
       SUM([OccupiedinMB]) [OccupiedinMB]
      FROM [db_admin].[dbo].[DBGrowth]
      GROUP BY [DBName],YEAR([Dateofobservation]),
       convert(varchar(10),[Dateofobservation],121)) AS MontlySalesData'
set @s=@s +' PIVOT( SUM([OccupiedinMB]) 
    FOR [Date] IN ( '
set @s=@s+' [2018-'+cast(@j as varchar)+'-01],[2018-'+cast(@j as varchar)+'-02],[2018-'+cast(@j as varchar)+'-03],[2018-'+cast(@j as varchar)+'-04]
,[2018-'+cast(@j as varchar)+'-05],[2018-'+cast(@j as varchar)+'-06],[2018-'+cast(@j as varchar)+'-07],[2018-'+cast(@j as varchar)+'-08]
,[2018-'+cast(@j as varchar)+'-09],[2018-'+cast(@j as varchar)+'-10],[2018-'+cast(@j as varchar)+'-11],[2018-'+cast(@j as varchar)+'-12]
,[2018-'+cast(@j as varchar)+'-13],[2018-'+cast(@j as varchar)+'-14],[2018-'+cast(@j as varchar)+'-15],[2018-'+cast(@j as varchar)+'-16]
,[2018-'+cast(@j as varchar)+'-17],[2018-'+cast(@j as varchar)+'-18],[2018-'+cast(@j as varchar)+'-19],[2018-'+cast(@j as varchar)+'-20]
,[2018-'+cast(@j as varchar)+'-21],[2018-'+cast(@j as varchar)+'-22],[2018-'+cast(@j as varchar)+'-23],[2018-'+cast(@j as varchar)+'-24]
,[2018-'+cast(@j as varchar)+'-25],[2018-'+cast(@j as varchar)+'-26],[2018-'+cast(@j as varchar)+'-27],[2018-'+cast(@j as varchar)+'-28]
,[2018-'+cast(@j as varchar)+'-29],[2018-'+cast(@j as varchar)+'-30],[2018-'+cast(@j as varchar)+'-31]
) '
set @s =@s+') as MNamePivot'

print @s
Exec(@s)


-----------------------------------------To Create and test linked server connection----------------------------------------------------

EXEC sp_addlinkedserver 
   @server=N'TPCDB1\TPCSQL',             -- Remote Computer Name
   @srvproduct=N'',                 -- Not Needed
   @provider=N'SQLNCLI',            -- SQL Server Driver
   @datasrc=N'TPCDB1\TPCSQL';  -- Server Name And Instance


declare @srvr nvarchar(128), @retval int;
set @srvr = '192.168.231.108';
begin try
    exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
    set @retval = sign(@@error);
end catch;
if @retval <> 0
  raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );

------------------------------------------To see MSDB table size info-----------------------------

USE msdb
GO

SELECT TOP(10)
      o.[object_id]
    , obj = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , o.[type]
    , i.total_rows
    , i.total_size
FROM sys.objects o
JOIN (
    SELECT
          i.[object_id]
        , total_size = CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(18,2))
        , total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END)
    FROM sys.indexes i
    JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
    JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
    WHERE i.is_disabled = 0
        AND i.is_hypothetical = 0
    GROUP BY i.[object_id]
) i ON o.[object_id] = i.[object_id]
WHERE o.[type] IN ('V', 'U', 'S')
ORDER BY i.total_size DESC


------------------------------------------------- Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */

SELECT  'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context';

-- Role Members
SELECT  'EXEC sp_addsrvrolemember @rolename =' + SPACE(1) + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1) + QUOTENAME(usr2.name, '''') AS '--Role Memberships'
FROM   sys.server_principals AS usr1
                                INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
                                INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
ORDER BY rm.role_principal_id ASC;

-- Permissions
SELECT  server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
                                + ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']' AS '--Server Level Permissions'
FROM   sys.server_permissions AS server_permissions WITH (NOLOCK)
                                INNER JOIN sys.server_principals AS server_principals WITH (NOLOCK) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE server_principals.type IN ('S', 'U', 'G')
ORDER BY server_principals.name ,
                                server_permissions.state_desc ,
                                server_permissions.permission_name;


------------------------------sp_help_revlogin-------

USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
  DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
    @binvalue varbinary(256),
    @hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
  DECLARE @tempint int
  DECLARE @firstint int
  DECLARE @secondint int
  SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
  SELECT @firstint = FLOOR(@tempint/16)
  SELECT @secondint = @tempint - (@firstint*16)
  SELECT @charvalue = @charvalue +
    SUBSTRING(@hexstring, @firstint+1, 1) +
    SUBSTRING(@hexstring, @secondint+1, 1)
  SELECT @i = @i + 1
END

SELECT @hexvalue = @charvalue
GO

IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
  DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary  varbinary (256)
DECLARE @PWD_string  varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr  varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)

DECLARE @defaultdb sysname

IF (@login_name IS NULL)
  DECLARE login_curs CURSOR FOR

      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
  DECLARE login_curs CURSOR FOR


      SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
      ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs

FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
  PRINT 'No login(s) found.'
  CLOSE login_curs
  DEALLOCATE login_curs
  RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
  IF (@@fetch_status <> -2)
  BEGIN
    PRINT ''
    SET @tmpstr = '-- Login: ' + @name
    PRINT @tmpstr
    IF (@type IN ( 'G', 'U'))
    BEGIN -- NT authenticated account/group

      SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
    END
    ELSE BEGIN -- SQL Server authentication
        -- obtain password and sid
            SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
        EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
        EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT

        -- obtain password policy state
        SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
        SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name

            SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'

        IF ( @is_policy_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
        END
        IF ( @is_expiration_checked IS NOT NULL )
        BEGIN
          SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
        END
    END
    IF (@denylogin = 1)
    BEGIN -- login is denied access
      SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
    END
    ELSE IF (@hasaccess = 0)
    BEGIN -- login exists but does not have access
      SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
    END
    IF (@is_disabled = 1)
    BEGIN -- login is disabled
      SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
    END
    PRINT @tmpstr
  END

  FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
   END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO


--EXEC sp_help_revlogin

-------------------------------------------------------------TSQL query to stop long running SQL Job  and start it back---------------------------


--if elapsed time is actually needed, uncomment
DECLARE @elapsed_minutes_max int
SET @elapsed_minutes_max = 240 --4 hours, adjust as needed

IF OBJECT_ID('tempdb.dbo.#jobactivity') IS NOT NULL
DROP TABLE #jobactivity
CREATE TABLE #jobactivity (
session_id int NULL,
job_id uniqueidentifier NULL,
job_name sysname NULL,
run_requested_date datetime NULL,
run_requested_source sysname NULL, --1=via schedule; 2=via alert; 3=via startup; 4=via user; 6=via CPU idle.
queued_date datetime NULL,
start_execution_date datetime NULL,
last_executed_step_id int NULL,
last_executed_step_date datetime NULL,
stop_execution_date datetime NULL,
next_scheduled_run_date datetime NULL,
job_history_id int NULL,
message nvarchar(1024) NULL,
run_status int NULL,
operator_id_emailed int NULL,
operator_id_netsent int NULL,
operator_id_paged int NULL
)

INSERT INTO #jobactivity
EXEC msdb.dbo.sp_help_jobactivity

IF EXISTS(
SELECT *
FROM #jobactivity ja
WHERE
    ja.start_execution_date IS NOT NULL AND
    ja.stop_execution_date IS NOT NULL AND
    DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) >= @elapsed_minutes_max
AND ja.job_name = N'Order Planning2')
BEGIN
 
    EXEC msdb.dbo.sp_stop_job N'Order Planning2'

print 'stop it'
END

ELSE
BEGIN


PRINT 'No JOB FOUND'

END

drop table #jobactivity


---------------------------------------Shrink TEMPDB without restart(last try before restart)--------------------------------------------

CHECKPOINT
GO
DBCC FREEPROCCACHE
GO
DBCC SHRINKFILE (TEMPDEV, 1024)
GO

-----------------------------------------TSQL code to find table with backedup with date----------------------

sp_tables '%_[1-30][1-12][1991-2020]%'

--------------------------------------General Guidelines to improve query performance-------------

Table should have primary key
Table should have minimum of one clustered index
Table should have appropriate amount of non-clustered index
Non-clustered index should be created on columns of table based on query which is running
Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
Do not to use Views or replace views with original source table
Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
Remove any adhoc queries and use Stored Procedure instead
Check if there is atleast 30% HHD is empty – it improves the performance a bit
If possible move the logic of UDF to SP as well
Remove * from SELECT and use columns which are only necessary in code
Remove any unnecessary joins from table
If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)

-------------- Disable all constraints for database

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

----------------------- Enable all constraints for database

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

---------To find column name of collation

Select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'col_test

----Change Collation of a SQL Server Table Column---------------------
USE databasename
GO
ALTER TABLE [dbo].[col_test]
ALTER COLUMN Text NVARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL

------------------------------Change collation of database----------------------------
USE master;
GO
ALTER DATABASE dbname
COLLATE French_CI_AS ;
GO

Note;
if you change the database collation you need to check whether column of table collation also changed or not. If not you need to change one by one use below script

USE databasename
GO
ALTER TABLE [dbo].[col_test]
ALTER COLUMN Text NVARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL

before change collation of column you need to verify following

You cannot change the collation of a column that is currently referenced by any one of the following:

A computed column

An index

Distribution statistics, either generated automatically or by the CREATE STATISTICS statement

A CHECK constraint

A FOREIGN KEY constraint

When you work with tempdb, the COLLATE clause includes a database_default option to specify that a column in a temporary table uses the collation default of the current user database for the connection instead of the collation of tempdb.

see for more

https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-column-collation?view=sql-server-2017


---------------------Verify the collation setting for database
SELECT name, collation_name
FROM sys.databases
WHERE name = N'dbname';
------------------------------------------------------------------------------------

ALTER DATABASE [msdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [msdb] SET DISABLE_BROKER
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;
ALTER DATABASE [msdb] SET ENABLE_BROKER
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;
ALTER DATABASE [msdb] SET MULTI_USER

EXEC msdb.dbo.sysmail_stop_sp;
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_start_sp;
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';

-------------------------table with rowcount and size------------------

SELECT
    t.NAME AS TableName, 
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
    SUM(a.used_pages)  * 8 / 1024 AS UsedSpaceMB ,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN   
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
--WHERE
--    t.is_ms_shipped = 0
--    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    UsedSpaceMB DESC, t.Name

------------------------------How much Memory SQL DB used----------------------------------------------

SELECT TOP 10 DB_NAME(database_id) Database_Name,
COUNT (*) * 8 / 1024 AS MBUsed,COUNT (*) * 8 / 1024 /1024 GBused
FROM sys.dm_os_buffer_descriptors
where DB_NAME(database_id) is not null
GROUP BY database_id
ORDER BY COUNT (*) * 8 / 1024 DESC
---------------------------------------------index fragment based on conditions-----


Depending on fragmentation index level appropriated action will be taken (no action, rebuild or reorganize)

If average fragmentation is less than 10% no action will be taken, if average fragmentation is between 10% and 30% index will be reorganized, finally if index average fragmentation is greater than 30% index will be rebuilt.

-- Ensure a USE <databasename> statement has been executed first.

SET NOCOUNT ON;

DECLARE @objectid int;

DECLARE @indexid int;

DECLARE @partitioncount bigint;

DECLARE @schemaname nvarchar(130);

DECLARE @objectname nvarchar(130);

DECLARE @indexname nvarchar(130);

DECLARE @partitionnum bigint;

DECLARE @partitions bigint;

DECLARE @frag float;

DECLARE @command nvarchar(4000);

DECLARE @dbid smallint;

-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function

-- and convert object and index IDs to names.

SET @dbid = DB_ID();

SELECT

    [object_id] AS objectid,

    index_id AS indexid,

    partition_number AS partitionnum,

    avg_fragmentation_in_percent AS frag, page_count

INTO #work_to_do

FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')

WHERE avg_fragmentation_in_percent > 10.0  -- Allow limited fragmentation

AND index_id > 0 -- Ignore heaps

AND page_count > 25; -- Ignore small tables

-- Declare the cursor for the list of partitions to be processed.

DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;

-- Open the cursor.

OPEN partitions;

-- Loop through the partitions.

WHILE (1=1)

BEGIN

FETCH NEXT

FROM partitions

INTO @objectid, @indexid, @partitionnum, @frag;

IF @@FETCH_STATUS < 0 BREAK;

SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)

FROM sys.objects AS o

JOIN sys.schemas as s ON s.schema_id = o.schema_id

WHERE o.object_id = @objectid;

SELECT @indexname = QUOTENAME(name)

FROM sys.indexes

WHERE object_id = @objectid AND index_id = @indexid;

SELECT @partitioncount = count (*)

FROM sys.partitions

WHERE object_id = @objectid AND index_id = @indexid;

-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.

IF @frag < 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';

IF @frag >= 30.0

SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';

IF @partitioncount > 1

SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));

EXEC (@command);

PRINT N'Executed: ' + @command;

END

-- Close and deallocate the cursor.

CLOSE partitions;

DEALLOCATE partitions;

-- Drop the temporary table.

DROP TABLE #work_to_do;

GO

-----------------------------------------------------------------------------------------------


Phases of recovery(SQL Server)

the recovery algorithm has 3 phases based around the last checkpoint in the transaction log.

Phase 1: Analysis. Starts at the last checkpoint in transaction log. This pass determines and constructs a dirty page table (DPT) consisting of pages that might be dirty at the time SQL Server stopped. An active transaction table is built of the uncommitted transactions at the time of the SQL Server stopped also.

Phase 2: Redo. This phase returns the database to the state at the time the SQL service stopped. Starting point for this forward passbeing the oldest uncommitted transaction. The mininum Log Sequence name (each log record is labelled with an LSN) in the DPT is the first time SQL Server expects to have to redo an operation on a page, redoing the logged operations starting right back at the oldest open transaction so that the neccessary locks can be aquired.

Phase 3: Undo: Here the list of active transaction (uncommitted at the time SQL Server stoopped) which where indentified in Phase 1 are rolled back individually. SQL Server follows the links between entries in the transaction log for each transaction. Any transaction that was not committed at the time SQL Server stopped is undone.

Recovery can be done when you restore the database, but it is also done at the startup of the database (crash recovery).


---------------------------------------------------------VLF  and log file Info------------------------------
Understanding the VLF(Virtual Log File)

A database can have one or more log file. In general there will be only one log file as there is no performance improvement by having multiple log file. SQL server uses the transaction log in sequential manner.As the data file divided into pages,log files are divided into virtual log file(VLF).The size of the VLFs  in a log file may not be in equal size. SQL server decide the size and number of VLF in a log file based on the size of the log file growth as given below.

Growth upto 64 MB          = 4  VLF
From 64 MB to 1 GB       = 8   VLF
Larger than 1 GB             = 16 VLF

dbcc loginfo--------VLF info

dbcc sqlperf(logspace)-------------


ref:
http://www.sqlservercentral.com/blogs/practicalsqldba/2013/09/30/sql-server-part-1-architecture-of-transaction-log/


--From SQL 2017 To know VLF Count below DMV can be used

SELECT [name], s.database_id,
COUNT(l.database_id) AS 'VLF Count',
SUM(vlf_size_mb) AS 'VLF Size (MB)',
SUM(CAST(vlf_active AS INT)) AS 'Active VLF',
SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',
COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF',
SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name], s.database_id
ORDER BY 'VLF Count' DESC
GO

-----------------------------Change collation of SQL Server---------------


To rebuild system databases for an instance of SQL Server:

Insert the SQL Server 2014 installation media into the disk drive, or, from a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Release.

From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter the brackets. When using a Windows operating system that has User Account Control (UAC) enabled, running Setup requires elevated privileges. The command prompt must be run as Administrator.

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]



Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=SHELL2_P2\Administrator /SAPWD= reset$123 /SQLCOLLATION=SQL_Latin1_General_CP850_BIN

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=SHELL2_P1\Administrator /SAPWD= reset$123 /SQLCOLLATION=SQL_Latin1_General_CP850_BIN

https://docs.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-2014

https://www.sanssql.com/2012/10/how-to-change-server-collation.html

https://www.sanssql.com/2013/10/change-sql-server-collation-without.html

-------------------------------------------Permuations and Combinations----------------------------------

--option 1

Create table #pk(col1 varchar(100),col2 varchar(100),primary key(col1,col2))

declare @t1 table  (col1 varchar(100))
insert @t1
    select 'Data1' UNION
    select 'Data2' UNION
    select 'Data3'  UNION
    select 'Data4' UNION
    select 'Data5'
Declare @t2 table  (col2 varchar(100))
insert @t2
    select 'Relation1' UNION
    select 'Relation2' UNION
    select 'Relation3' UNION
    select 'Relation4' UNION
    select 'Relation5'UNION
    select 'Relation6'



;with cteAllColumns as (
    select col1 as col
        from @t1
    union
    select col2 as col
        from @t2

)
insert into #pk
select c1.col , c2.col
    from cteAllColumns c1
        cross join cteAllColumns c2
    where c1.col < c2.col
order by c1.col

select * from #pk


--option 2


--Create table #pk(col1 varchar(4000),col2 varchar(4000),primary key(col1,col2))

DECLARE   @ConcatString1 VARCHAR(4000)
SELECT   top 11 @ConcatString1 = COALESCE(@ConcatString1 + ', ', '') + name
FROM master.dbo.sysdatabases

DECLARE   @ConcatString2 VARCHAR(4000)


DECLARE @tags NVARCHAR(4000) = 'Data1,Data2,Data3,Data4,Data5,Data6,Data7,Data8,Data9,Data10,Data11'

SELECT @ConcatString2 = COALESCE(@ConcatString2 + ', ', '') +value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';


Create table #CSV1(Data1 VARCHAR(4000))
Insert into #CSV1
Values(@ConcatString1)

Create table #CSV2(Data2 VARCHAR(4000))
Insert into #CSV2
Values(@ConcatString2)


select * from #CSV1
select * from #CSV2

--drop table #CSV1
--drop table #CSV2


SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
into #C1
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Data1, ',', '</r><r>')  + '</r></H>' AS XML) AS [vals]
FROM #CSV1) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)

SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
into #C2
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Data2, ',', '</r><r>')  + '</r></H>' AS XML) AS [vals]
FROM #CSV2) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)

Select * from #C1
Select * from #C2

declare @t1 table  (Info1 varchar(8000),col1 varchar(8000))

insert into @t1
Select * FROM #CSV1
    CROSS APPLY STRING_SPLIT(Data1, ',');

declare @t2 table  (Info2 varchar(8000),col2 varchar(8000))

insert into @t2
Select * FROM #CSV2
    CROSS APPLY STRING_SPLIT(Data2, ',');


Select * from @t1
Select * from @t2

;with cteAllColumns as (
    select col1 as col
        from @t1
    union
    select col2 as col
        from @t2

)
select c1.col, c2.col
    from cteAllColumns c1
        cross join cteAllColumns c2
    where c1.col < c2.col
order by c1.col
--select * from #pk

drop table #CSV1
drop table #CSV2
--drop table #pk
drop table #C1
drop table #C2

--option 3

DECLARE   @ConcatString VARCHAR(4000)
SELECT   top 11 @ConcatString = COALESCE(@ConcatString + ', ', '') + name
FROM master.dbo.sysdatabases

DECLARE   @ConcatString1 VARCHAR(4000)

SELECT   top 11 @ConcatString1 = COALESCE(@ConcatString1 + ', ', '') + name
FROM ts4.master.dbo.sysdatabases
where name not in ('Data1', 'Data2', 'Data3', 'Data4', 'Data5', 'Data6', 'Data7', 'Data8', 'Data9', 'Data10', 'Data11')

Create table #CSV(Data VARCHAR(4000))
Insert into #CSV
Values(@ConcatString)

Insert into #CSV
Values(@ConcatString1)

select * from #CSV

--drop table #CSV


--select parsename(Data,4)as [4],parsename(Data,3)as [3],parsename(Data,2)as [2],parsename(Data,1)as [1] from
--(Select replace(Data,',','.') Data from #CSV) t



SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name1
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name5
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name1
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name5
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Data, ',', '</r><r>')  + '</r></H>' AS XML) AS [vals]
FROM #CSV) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)



drop table #CSV

-super query-------------------

declare @numrows int
declare @numrows2 int
declare @numrows3 int
declare @numrows4 int
set @numrows=1
set @numrows2=4
set @numrows3=2
set @numrows4=4


create table #c1(Id int,Name varchar(300),Value int)
declare @vSQL varchar(1000)
--select @numrows = 5
select @vSQL = 'select top ' + convert(varchar, @numrows) + ' * from t11 Order by Id'
insert into #c1
Execute (@vSQL)
update #c1 set Name=Name+'('+'WK'+')'



create table #c2(Id int,Name varchar(300),Value int)
declare @vSQL2 varchar(1000)
--select @numrows2 = 4
select @vSQL2 = 'select top ' + convert(varchar, @numrows2) + ' * from t12 Order by Id'
insert into #c2
Execute (@vSQL2)
update #c2 set Name=Name+'('+'BT'+')'

create table #c3(Id int,Name varchar(300),Value int)
declare @vSQL3 varchar(1000)
--select @numrows2 = 4
select @vSQL3 = 'select top ' + convert(varchar, @numrows3) + ' * from t13 Order by Id'
insert into #c3
Execute (@vSQL3)
update #c3 set Name=Name+'('+'ALL'+')'


create table #c4(Id int,Name varchar(300),Value int)
declare @vSQL4 varchar(1000)
--select @numrows = 5
select @vSQL4 = 'select top ' + convert(varchar, @numrows4) + ' * from t14 Order by Id'
insert into #c4
Execute (@vSQL4)
update #c4 set Name=Name+'('+'BL'+')'



;with cteAllColumns as (
    select Name as col
        from #c1
    union
    select Name as col
        from #c2
union
    select Name as col
        from #c3
union
    select Name as col
        from #c4


)
select  *
into #fmtb4
  from cteAllColumns cte1

order by col desc


;with cteAllColumns as (
    select Name as col
        from #c1
    union
    select Name as col
        from #c2
union
    select Name as col
        from #c3
union
    select Name as col
        from #c4

)
select  cte1.col col1,cte2.col col2
into #fmtbl
  from cteAllColumns cte1
        cross join cteAllColumns cte2
    where cte1.col < cte2.col
order by cte1.col

       

Declare @Column1 Nvarchar(300)
Declare @Column2 Nvarchar(300)
Declare @Column1After Nvarchar(300)
Declare @Column2After Nvarchar(300)


DECLARE CC CURSOR FOR
SELECT DISTINCT col1,col2
FROM #fmtbl

OPEN CC

FETCH NEXT FROM CC INTO @Column1,@Column2
WHILE @@FETCH_STATUS = 0
BEGIN

set @Column1After=@Column1+'['+'C'+']'
set @Column2After=@Column2+'['+'VC'+']'

update #fmtbl set col1=@Column1After where col1=@Column1 and col1<>@Column1+'['+'C'+']'

update #fmtbl set col2=@Column2After where col2=@Column2 and col1<>@Column2+'['+'VC'+']'




 FETCH NEXT FROM CC INTO  @Column1,@Column2



  END


--select ''[afterfmtb4],* from  #fmtbl
--order by col1,col2

  SELECT Distinct top 2
stuff(
(
    SELECT ','+ col FROM #fmtb4  FOR XML PATH('')
),1,1,'')  Results
into #commatbl
FROM (SELECT DISTINCT top 1 col FROM #fmtb4  ) #fmtb4

CLOSE CC
DEALLOCATE CC


select col1,col2,Results
Into #CP
 FROM #fmtbl P CROSS Join
#commatbl C

Select ''BEFORE,* from #CP


--update #CP set Results = replace(Results,substring(Results,charindex(col1,(substring(Results,charindex(col1,(Results)),charindex(',',Results))+'['+'C'+']')),charindex(',',Results)-1),col1)

update #CP set Results = Stuff(Results,charindex(Stuff(col1,charindex('[',col1),len(right(col1,3)),''),(Results)),len(Stuff(col1,charindex('[',col1),len(right(col1,3)),'')),col1)

update #CP set Results = Stuff(Results,charindex(Stuff(col2,charindex('[',col2),len(right(col2,4)),''),(Results)),len(Stuff(col2,charindex('[',col2),len(right(col2,4)),'')),col2)



Select 'AFTER'AFTER,* from #CP
Order by col1,col2

SELECT DISTINCT col1,col2,
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
--into #C1
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Results, ',', '</r><r>')  + '</r></H>' AS XML) AS [vals]
FROM #CP) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)

drop table #c1
drop table #c2
drop table #c3
drop table #c4
drop table #fmtbl
drop table #fmtb4
drop table #CP
drop table #commatbl
Posted by at 01:11 0 comments

Sunday, 29 July 2018

Fix Microsoft SQL SERVER Error 5120 Permission Denied Error While Restore/Alter/Copy One Drive to Another Drive

Fix Microsoft SQL SERVER Error 5120
Case 1:
Sometimes you need to know more about permissions on file, service account etc. to fix certain errors. In my recent visit to a customer location, one of the database developers came to me with a problem. He said that when he attaches MDF and LDF file to the SQL instance, attach is successful, but the issue was next to database name it was saying read-only. Let us see how we can FIX Error 5120.
His problem was interesting, so I asked him to follow-up with me. I asked him to go through try below.
1
2
3
4
USE [master]
GO
ALTER DATABASE [SQLAuthority] SET READ_WRITE WITH NO_WAIT
GO
But he told that this is not helping and he is getting error.

Error 5120

Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “D:\MySoftware\Data\Data_Data.MDF”. Operating system error 5: “5(Access is denied.)”.
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “D:\MySoftware\Data\Data_Log.LDF”. Operating system error 5: “5(Access is denied.)”.
File activation failure. The physical file name “D:\MySoftware\Data\Data_Log.LDF” may be incorrect.
Msg 945, Level 14, State 2, Line 1
Database ‘Lis’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
The databases failed to attach due to permissions issues and the cases being marked as read only. We were able to resolve the issue by granting SQL exclusive rights to the database files and unselecting the read-only attribute. Here are the steps which helped him.

FIX Error 5120

First step was to find the Service account, which can be done by opening configuration manager.
SQL SERVER - FIX Error 5120 - Database is in Read Only Mode After Attaching Files ro-attach-01
So, in my lab machine, its “NT Service\MSSQL$SQL2016” for SQL Server 2016 instance and NT AUTHORITY\NetworkService for SQL Server 2014 instance. Here are the steps to give permission to files/folder.
  • Right click the database (mdf/ldf) file or folder and select “Properties”.
  • Select “Security” tab and click the “Edit” button.
  • Click the “Add” button.
  • Enter the object name to select as NT Service\MSSQL$SQL2016′ and click “Check Names” button.
  • It would become MSSQL$SQL2016
  • Click “OK” button.
  • Give this service account “Full control” to the file or folder.
  • Click “OK” button.
Then if you try to attach, it would work fine. I was able to reproduce the error by marking files as read-only.
SQL SERVER - FIX Error 5120 - Database is in Read Only Mode After Attaching Files ro-attach-02
Have you faced such permission errors? These are common and sometimes can be hard to even identify what the problem is. Do let me know via the comments section.
Case 2:
I took my database offline and when I went to place it online; I am getting the same errors above, which is detailed below. I have been working on this for hours and the network guys are stuck on this as well. Any help to get this database back online would be really appreciated.
(Can I take the same steps above to put it online? I do not have access to the security tab since the database is not available. )
Thank you!
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “C:\MSSQL_DATA\Log”. Operating system error 5: “5(Access is denied.)”.
Msg 5120, Level 16, State 101, Line 1
Unable to open the physical file “C:\MSSQL_DATA\Log”. Operating system error 5: “5(Access is denied.)”.
Msg 945, Level 14, State 2, Line 1
Database ‘DP205_Staging’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1

solution:

Run administrator on ssms

Posted by at 08:35 0 comments

Wednesday, 13 June 2018

What's New in SQL Server 2017

What's New in SQL Server 2017


Background

SQL Server 2017 released on (general availability release) on October 2017 (Really? we are just trying to understand features of SQL 2016, ok, jokes apart :)) SQL 2017 released in parts, its first part, that is SQL 2017 CTP 1.0 (SQL version 14.0.1.246) was released on Nov-2016 (How can they release 2017 version in 2016?). Until now, SQL 2017 has come up with its 10 releases, the current release is SQL 2017 Release GA (SQL version 14.0.1000.169) (check here) for available releases in October 2017.
(**See here for What's new in SQL 2016.)

Introduction

This version of SQL mainly connects to Linux and brings the power of SQL to Linux. In short, now you can install SQL 2017 on Linux (it's a great move), additionally SQL can be used in linux based docker container also. In this build, SQL gives you development language choice, you can develop it on-premise or cloud-based.
In this build, SQL also improves performance, scalability and features, in each of its parts, like Database Engine, Integration Services, Master Data Services, Analysis Services, etc. In this article, we will take a look at them one by one.
Following are some Database Engine new features.

identity_cache

This option helps you to avoid discrepancy in values of identity columns, in the case of server shut down unexpectedly or any failover occur, or even if you switch to some secondary server. This option is used with 'ALTER DATABASE SCOPED CONFIGURATION' statement. This statement is used to enable database configuration settings. The syntax is as follows:
ALTER DATABASE SCOPED CONFIGURATION
{      
     {  [ FOR SECONDARY] SET <set_options>  }  
}
| CLEAR PROCEDURE_CACHE
| SET < set_options >
[;]  

< set_options > ::=  
{
    MAXDOP = { <value> | PRIMARY}  
    | LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY}  
    | PARAMETER_SNIFFING = { ON | OFF | PRIMARY}  
    | QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY}
    | IDENTITY_CACHE = { ON | OFF }
}

Adaptive Query Processing Improvements

Do you want to improve query execution performance? Then this feature will help you. This feature is supported in SQL server and Azure SQL databases.
Do you know normal optimization flow of SQL Query execution? If no, then check the below steps:
  1. Initially Query optimizer calculates all feasible query execution plan for a given query.
  2. Then it gets the lowest cost /fastest plan.
  3. Finally, the lowest estimated plan will be chosen by the optimizer to execute query and then execution started.
Now in this process, there are some drawbacks:
  1. If lowest estimated execution plan may go wrong, then it affects performance.
  2. If insufficient memory gets allocated for processing, then it leads to spills to disk.
  3. If assigned memory is incorrectly sized, then it leads to memory waste.
To overcome all these issues, you can use this SQL 2017 feature.

1. Batch Mode Memory Grant Feedback

This feedback (technique) re-calculate (actually) required memory for the execution plan and grant it from cache

2. Batch Mode Adaptive Joins

To execute plan faster, it has Hash join and nested loop join, after scanning the first input of execution plan, it decides which join to use to produce output in optimal speed.

3. Interleaved Execution

In interleaved execution, it takes a 'pause' in optimization execution plan, if it encountered multi-statement table valued functions. Then, it just calculates perfect cardinality and then resumes optimization.

Automatic Tuning

As the words suggest, this feature checks out the problems in query performance, identifies them and fixes them with the recommended solution. There are a couple of automatic tuning techniques available in this feature:
  1. Automatic Correction (Plan)
  2. Automatic Management (Index)

Automatic Correction (Plan)

This plan is available in SQL 2017 DB, it basically scans problematic plan (performance wise) and then fixes it with the recommended solution

Automatic Management (Index)

This feature is available in SQL 2017 Azure DB, here it basically identifies and corrects the order of indices. The indices which are removed and indices which are added.

Graph DB

The graph database feature is newly introduced in SQL 2017.

What is Graph DB ?

Basically, it is the collection of edge and nodes, where edge is the relationship between nodes and the node is just an entity, so single edge can connect with multiple nodes. A Graph db acts just like a relational database, you can use Graph db in the following cases:
  1. If you have a data in hierarchical format and you want to save multiple parent for a node
  2. If you need to check and analyze interlinked relationship and data
  3. If you have many to many relationships
Here, keyword MATCH is used to query the graph table and sort the data, with the help of only a single query, user can query across the graph and relational data, for more details, checkout [here].

Always Available (Cross Database Access)

With the help this feature, cross database transactions are now possible against different SQL instances (same SQL instance can be connected to different instances). It also supports database distributed transactions. [SQL 2016 also supports cross database access but only for only in same SQL server instance.]

DTA Improvements

In SQL 2017, there is performance improvement in Database tuning advisor (DTA) with additional options for tuning advisor.
** for those who don't know what is Database tuning advisor:
It is a database engine that inspects the query (which is processed) and then put forward in a way by which you can improve its performance, may be by changing database structure (e.g. index, keys). There are couple of ways in which you can use it:
  • using GUI (interface)
  • using command utility
If you want to know more about it, then you can check here.

New String Members (Functions)

SQL 2017 introduces some new string functions like TRANSLATE, CONCAT_WS, STRING_AGG, TRIM. Let's take a a look at them one by one.

TRANSLATE

It basically takes a string as input and then translates the characters with some new characters, see below syntax:
TRANSLATE ( inputString, characters, translations)
in the above syntax length of 'characters' should be same as 'translations', otherwise the above function will return an error.
e.g.
TRANSLATE ('6*{10+10}/[6-4]','[]{}','()()')
output of the above example will be 6*(10+10)/(6-4).
so, we can see curly and square braces translated to round braces.
[This function acts the same as REPLACE but it is simpler to use than REPLACE, if we want the same output using REPLACE, then we need to write it as below, which is difficult to understand:
SELECT REPLACE(REPLACE(REPLACE(REPLACE('6*{10+10}/[6-4]','{','('), '}', ')'), '[', '('), ']', ')');
CONCATE_WS
It simply concats all input arguments with the specified input delimiter. See below syntax:
CONCAT_WS ( separator, argument1, argument1 [, argumentN]… )
It produces the single string by concatenating all arguments with the help of the separators, it needs minimum 2 arguments to produce output, otherwise error will be thrown:
For example:
SELECT CONCAT_WS(',','Count numers', 'one', 'two', 'three', 'four' ) AS counter;
The output of above syntax will be:
one, two, three, four
You can also use database column names instead of hard coded strings.

TRIM

(finally :)) SQL 2017 introduced this new function. It simply works as the C# trim function, remove all spaces from start and end of the string. The syntax is as below:
SELECT TRIM('     trim me    ') AS result;
The output of the above syntax will be:
trim me
This function will not remove spaces that lay in between the string.
STRING_AGG
It concatenate values of string with the help of separators and does not add separator at the end of the string. The input could be VARCHAR, NVARCHAR, optionally you can specify the order of the result with the help of WITHIN GROUP clause.
See the syntax below:
STRING_AGG ( expression, separator ) [ <order_clause> ]

<order_clause> ::=  
    WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
Check out the below example:
SELECT city,
    STRING_AGG (name, ';') WITHIN GROUP (ORDER BY name ASC) AS names
FROM Students GROUP BY city;
In the above example, we have concatenated all names separated by semicolon ( ; ) WITHIN GROUP helps us to make it order by. The output will be displayed as follows:
CityNames
City 1name1;name2;name3;name4;name5;
City 2name1;name2;name3;name4;

What's New in SSRS (Reporting Services) in SQL 2017

  • From now onwards, there is no SSRS setup available with SQL server setup, you need to download it from download center [here].
  • Now, Query designer has the support of DAX, now native DAX queries can be created against SSAS (Analysis services), this comes under the latest release of SQL tools and report builder
  • SSRS now supports RESTful API that support OpenAPI compliant, API can be found here
  • You can now add attachments to your comments.
  • You can add comments to reports.
  • Improvement in reporting services web portal (this feature is already available in SQL 2016)

What New in SSIS (Integration Services) in SQL 2017

  • You can now execute SSIS package on Linux, additionally loading, extracting and transforming of data is now possible on Linux
  • Scale out feature allows complex integration to multiple machine with high performance package execution, even multiple package execution request is possible. Scale out management is carried out all operation with the help of Scale Out Master and Scale Out Workers, for more details, check here
  • Microsoft DAX and CRM online now gets support of ODataConnection manager and oDataSource

What's New in SSAS (Analysis Services) in SQL 2017

  • New Get Data interface is introduced in SQL 2017 which is similar to MS Excel, power BI, it also provides transformation of data and data mashup, you can do it by using query builder and M expressions
  • Tabular mode for SSAS is introduced in SQL 2012, now it become more powerful in SQL 2017
  • SQL 2017 come up with new 'Encoding hints' feature, which is used to optimize large in-memory tabular data
  • Performance improvement for power PIVOT
For more information on SSAS in SQL 2017, switch here.

Machine Learning

We know SQL 2016 is now supporting R services, it has now renamed to SQL Server Machine learning services. With the help of this, you can easily use R or Python scripts using SQL server.
In this new feature, Python can be run in stored procedure, even you can remotely execute it using SQL server, this will really helpful to Python developers.
Currently, this feature is not supported on Linux, we need to wait for the next announcement.
To use machine learning in an intelligent and powerful way, SQL uses the following solutions.
  1. revoscalepy
  2. microsoftml
revoscalepy is the new library to give base to high performance algorithms, computing and remote contexts, it is basically based on RevoScaleR (It is a package of R services).
microsoftml is the Microsoft R server package that supports machine language algorithms, Microsoft developed this library for internal machine learning, but over the years, it has improved and supports fast data streaming, large text transformations, etc.

Linux Support

Basically, the main goal of this build is to release product on Linux, this build of SQL release with the name "SQL 2017 on Linux and Windows", here are few key features of "SQL on Linux":
  • Core database engine capabilities
  • Support to IPV6
  • NFS support
  • AD authentication on linux
  • Support for encryption
  • Installation of SSIS package on Linux is possible
  • configuration tool (command line) available MSSQL-conf
  • Seamless and unattended installation
  • SQL for Visual studio code (VS Code is already available on Linux)
  • Cross platform script generator
To know more about SQL on Linux, switch here.
References
Posted by at 03:33 1 comments
Subscribe to: Posts (Atom)