Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 21 March 2019

Trouble shoot High CPU spike or High CPU Consumption

to troubleshoot "SQL Server consuming High CPU" and few checklist points which will help in isolating/troubleshooting.
Below screenshot summarizes an approach to isolate SQL high CPU issues:
CHECKLIST POINTS:
1. From Windows task manager check the overall CPU utilization. Collect the details of number of logical processors present on the box.

2. From task manager, check the SQL Server process CPU utilization. Is the SQL CPU constantly above 70%?

3. Gather the following details:
  • How do you typically become aware that CPU is the bottleneck?
  • What is the impact of the problem? Are there particular errors that your user application?
  • When did the problem first occur? Are you aware of anything that changed around this time? (Increased workload? Change in table size? App upgrade? SQL upgrade?)
  • Can you make new connections to the server during the problem period?
  • How long did the problem last? Have you been able to do anything that seemed to help resolve the problem?
  • What system-level symptoms have you observed during the problem periods? For example, is the server console slow or unresponsive during the problem periods? Does overall CPU usage increase? If so, what %CPU is observed during the problem? What is the expected %CPU?

4. If the High CPU is causing by process other than SQL Server process (sqlservr.exe) engage the team which takes care of that process.

5. Open Perfmon and add the below counters:
Process (sqlservr):
% Privileged Time
% Processor Time
% User Time
Processor
% Privileged Time
% Processor Time
% User Time

6. If Processor Privileged time is above 25%, engage the Windows team
Processor Time = Privileged Time + User Time.

7.  Confirm that SQL is consuming high CPU on the box by validating the below counters:
Process (sqlservr): % Privileged Time
% Processor Time
% User Time
Divide the value observed with the number of logical processors to get the CPU utilization by SQL Process.
If (Process (sqlservr)% Privileged time/No of Procs) is above 30%, ensure that KB 976700 is applied for Windows 2008 R2

This step, gives an indication of if SQL Server is causing the high privilege time on the server. If SQL privilege time is high, as per the above calculations, engage the Windows team.

8. Check the below configurations from sp_configure and make sure they are as per the best practice recommendations:
Follow KB 2806535 for Max DOP recommendation settings.

9. If you are unable to connect to the SQL instance locally using SSMS, try connecting to SQL instance using Dedicated Admin connection (DAC) using:
ADMIN: Servername

10. Get the top 10 queries consuming High CPU using below query:
SELECT s.session_id,
r.status,
r.blocking_session_id 'Blk by',
r.wait_type,
wait_resource,
r.wait_time / (1000 * 60) 'Wait M',
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000 * 60) 'Elaps M',
Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s
JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id
CROSS APPLY sys.Dm_exec_sql_text(r.sql_handleAS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc

11.  Check the wait type of the queries returned from the above output. If CPU is the major bottleneck, most of the sessions will have the below waits:
SOS_SCHEDULER_YIELD
CXPACKET
THREADPOOL
If most of the queries are waiting on CXPACKET, revisit sp_configure setting for “Max degree of parallelism” and “Cost degree of parallelism” and check if they are set as per best practice recommendations.

12.  Run the SQL Standard report to get the list of Top CPU queries:
Right Click on the instance, go to reports> Standard reports
Check the Top CPU queries obtained in the report. Compare the report with the Top CPU consuming queries obtained from above step.

13.  Once the top CPU queries are identified, get the list of all the SQL tables involved using statement_text and command_text column output obtained from step 10.
Check the following:
Index Fragmentation on the top CPU driving tables
Last Statistics updated information
If the Index fragmentation is above >30 %, rebuild the index. If the statistics are not updated on the table, update the statistics.
From the Top CPU queries, if there are only few set of tables which are responsible for high CPU, share the tables list with the application team and share the statistics report and fragmentation report.
Check if there are any select queries which are causing high CPU, check with application team if they can be stopped temporarily on high OLTP servers.


14.  Once the database maintenance activity is performed (like Index rebuild and Stats update), if SQL is still using high CPU, execute the query mentioned in Step 10.
Check if the Top CPU query has changed. If the query has changed, then follow the action mentioned in Step 13. If the query is still the same, then go to next step.

15. Collect the estimated execution plan of the top CPU consuming queries involved using:
Query 1:  Get the Top CPU consuming session ID’s from the output of query mentioned in step 10.
Collect the Plan handle and SQL handle information from below query:
select sql_handle,plan_handle from sys.dm_exec_requests where session_id=<session_id>
Get the text of the query:
--replace the SQL Handle with the value obtained from above query.
select * from sys.dm_exec_sql_text (sql_handle)
Get the estimated execution plan of the query:
--replace the Plan handle with the value obtained from above query.
select * from sys.dm_exec_query_plan (plan_handle)
Query 2: The below query captures the Total CPU time spend by a query along with the plan handle. Plan handle of the query is needed to get the estimated execution of the query.
select
highest_cpu_queries.plan_handle,
highest_cpu_queries.total_worker_time,
q.dbid,
q.objectid,
q.number,
q.encrypted,
q.
from
(select top 50
qs.plan_handle,
qs.total_worker_time
from
sys.dm_exec_query_stats qs
order by qs.total_worker_time descas highest_cpu_queries
cross apply sys.dm_exec_sql_text(plan_handle) as q
order by highest_cpu_queries.total_worker_time desc

16.  Share the estimated execution plan obtained with the application team.
Check for the operator which has high Cost.
Check the Indexes used for the operator and number of rows estimated.
Revisit the statistics and Indexes on the table reported for the operator which has high cost and make sure that there are no stale statistics.
Check if the estimated execution plan is recommending any new index to be created. If the plan reports an index recommendation, share the missing index details with the Application team.

17.  “Convert Implicit” function in execution plan can result in High CPU utilization of SQL Server as well.
Review the execution plans of the High CPU consuming queries and review the Operator with High Cost and check if there are any Convert_Implicit function is called.
In the above screenshot, CONVERT_IMPLICIT function is implicitly converting the column “NationalIDNumber” to integer whereas in the table definition its defined as nvarchar (15). So, share the report with application team and ensure that the data type passed and stored in database are having the same data type.

18.  Run the missing index query on the database which has reported high CPU and check if there are any missing indexes recommendations. Share the Index recommendation report with the Application team

19.  Tune the Top CPU consuming queries with the Database Engine Tuning Adviser to see whether database engine recommends index recommendation/statistics creation.

20.  Check for Compilations/Re-Compilations in SQL Server:
From perfmon, capture the below counters:
SQL Server: SQL Statistics: Batch Requests/sec
SQL Server: SQL Statistics: SQL Compilations/sec
SQL Server: SQL Statistics: SQL Recompilations/sec
Batch Requests/sec: Number of SQL batch requests received by server.
SQL Compilations/sec: Number of SQL compilations per second.
SQL Recompilations/sec: Number of SQL re-compiles per second.

If the recompilation count is high, check for below:
Any Schema changes
Statistics changes
SET option changes in the batch
Temporary table changes
Stored procedure creation with the RECOMPILE query hint or the OPTION (RECOMPILE) query hint
From SQL profiler, add the following events and check the stored procedures which are getting recompiled frequently.
21. Check if SQL System threads are consuming high CPU:
select * from sys.sysprocesses where cmd like 'LAZY WRITER' or cmd like '%Ghost%' or cmd like 'RESOURCE MONITOR'
Ghost cleanup thread >>>> Check if the user deleted large number of rows
Lazy Writer thread >>>>>>> Check if any memory pressure on the server
Resource Monitor thread >> Check if any memory pressure on the server

22.  If the Top CPU consuming queries has the wait type: SQLTRACE_LOCK, check there are any traces running on the server using:
select * from sys.traces

23. Collect the PSSDIAG during the Top CPU issue time. Refer KB 830232. Load and Analyze the data in SQL Nexus tool.

24. Even after implementing above action plans, if the SQL CPU utilization is high, then increase the CPU on the server.

script:

--to check 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

--To check who is active and logs

use master

select getdate() 'Today Date and Time',@@servername as servername


Exec sp_WhoIsActive



USE master

DECLARE @T table(LogDate datetime,Processinfo char(10),Text Nvarchar(Max))

Insert into @T
EXEC xp_readerrorlog

Select    @@SERVERNAME AS ServerName,* from @T
where Processinfo<>'Backup' and Processinfo<>'Logon'
Order by LogDate desc




Thursday, 14 March 2019

To Check SQL memory

-- Get size of SQL Server Page in bytes
DECLARE @pg_size INT, @Instancename varchar(50)
SELECT @pg_size = low from master..spt_values where number = 1 and type = 'E'

-- Extract perfmon counters to a temporary table
IF OBJECT_ID('tempdb..#perfmon_counters') is not null DROP TABLE #perfmon_counters
SELECT * INTO #perfmon_counters FROM sys.dm_os_performance_counters;

-- Get SQL Server instance name as it require for capturing Buffer Cache hit Ratio
SELECT  @Instancename = LEFT([object_name], (CHARINDEX(':',[object_name])))
FROM    #perfmon_counters
WHERE   counter_name = 'Buffer cache hit ratio';


SELECT * FROM (
SELECT  'Total Server Memory (GB)' as Cntr,
        (cntr_value/1048576.0) AS Value ,'Total Server Memory is almost same as Target Server Memory: Good Health & Total Server Memory is much smaller than Target Server Memory:  There is a Memory Pressure or Max Server Memory is set to too low.' Descriptions
FROM    #perfmon_counters
WHERE   counter_name = 'Total Server Memory (KB)'
UNION ALL
SELECT  'Target Server Memory (GB)',
        (cntr_value/1048576.0) ,'Total Server Memory is almost same as Target Server Memory: Good Health & Total Server Memory is much smaller than Target Server Memory:  There is a Memory Pressure or Max Server Memory is set to too low.' Descriptions
FROM    #perfmon_counters
WHERE   counter_name = 'Target Server Memory (KB)'
UNION ALL
SELECT  'Connection Memory (MB)',
        (cntr_value/1024.0) ,'When high, check the number of user connections and make sure it’s under expected value as per your business' Descriptions
FROM    #perfmon_counters
WHERE   counter_name = 'Connection Memory (KB)'
UNION ALL
SELECT  'Lock Memory (MB)',
        (cntr_value/1024.0) ,'Shows the total amount of memory the server is using for locks' Descriptions
FROM    #perfmon_counters
WHERE   counter_name = 'Lock Memory (KB)'
UNION ALL
SELECT  'SQL Cache Memory (MB)',
        (cntr_value/1024.0) ,'Total memory reserved for dynamic SQL statements.' Descriptions
FROM    #perfmon_counters
WHERE   counter_name = 'SQL Cache Memory (KB)'
UNION ALL
SELECT  'Optimizer Memory (MB)',
        (cntr_value/1024.0) ,'Ideally, the value should remain relatively static. If this isn’t the case you might be using dynamic SQL execution excessively.' Descriptions
FROM    #perfmon_counters
WHERE   counter_name = 'Optimizer Memory (KB) '
UNION ALL
SELECT  'Granted Workspace Memory (MB)',
        (cntr_value/1024.0) ,'Total amount of memory currently granted to executing processes such as hash, sort, bulk copy, and index creation operations.' Descriptions
FROM    #perfmon_counters
WHERE   counter_name = 'Granted Workspace Memory (KB) '
UNION ALL
SELECT  'Cursor memory usage (MB)',
        (cntr_value/1024.0) ,'Memory using for cursors' Descriptions
FROM    #perfmon_counters
WHERE   counter_name = 'Cursor memory usage' and instance_name = '_Total'
UNION ALL
SELECT  'Total pages Size (MB)',
        (cntr_value*@pg_size)/1048576.0 ,'' Descriptions
FROM    #perfmon_counters
WHERE   object_name= @Instancename+'Buffer Manager'
        and counter_name = 'Total pages'
UNION ALL
SELECT  'Database pages (MB)',
        (cntr_value*@pg_size)/1048576.0 ,'Monitors the number of SQL Server database pages.' Descriptions
FROM    #perfmon_counters
WHERE   object_name = @Instancename+'Buffer Manager' and counter_name = 'Database pages'
UNION ALL
SELECT  'Free pages (MB)',
        (cntr_value*@pg_size)/1048576.0 ,'Amount of free space in pages which are commited but not currently using by SQL Server' Descriptions
FROM    #perfmon_counters
WHERE   object_name = @Instancename+'Buffer Manager'
        and counter_name = 'Free pages'
UNION ALL
SELECT  'Reserved pages (MB)',
        (cntr_value*@pg_size)/1048576.0 ,'' Descriptions
FROM    #perfmon_counters
WHERE   object_name=@Instancename+'Buffer Manager'
        and counter_name = 'Reserved pages'
UNION ALL
SELECT  'Stolen pages (MB)',
        (cntr_value*@pg_size)/1048576.0 ,'Higher the value for Stolen Pages: Find the costly queries / procs and tune them' Descriptions
FROM    #perfmon_counters
WHERE   object_name=@Instancename+'Buffer Manager'
        and counter_name = 'Stolen pages'
UNION ALL
SELECT  'Cache Pages (MB)',
        (cntr_value*@pg_size)/1048576.0 ,'Number of 8KB pages in cache.' Descriptions
FROM    #perfmon_counters
WHERE   object_name=@Instancename+'Plan Cache'
        and counter_name = 'Cache Pages' and instance_name = '_Total'
UNION ALL
SELECT  'Page Life Expectency in seconds',
        cntr_value ,'Usually 300 to 400 sec for each 4 GB of memory. Lesser the value means memory pressure' Descriptions
FROM    #perfmon_counters
WHERE   object_name=@Instancename+'Buffer Manager'
        and counter_name = 'Page life expectancy'
UNION ALL
SELECT  'Free list stalls/sec',
        cntr_value ,'High value indicates that the server could use additional memory.' Descriptions
FROM    #perfmon_counters
WHERE   object_name=@Instancename+'Buffer Manager'
        and counter_name = 'Free list stalls/sec'
UNION ALL
SELECT  'Checkpoint pages/sec',
        cntr_value ,'Checkpoint Pages/sec shows the number of pages that are moved from buffer to disk per second during a checkpoint process' Descriptions
FROM    #perfmon_counters
WHERE   object_name=@Instancename+'Buffer Manager'
        and counter_name = 'Checkpoint pages/sec'
UNION ALL
SELECT  'Lazy writes/sec',
        cntr_value ,'How many times per second lazy writer has to flush dirty pages out of the buffer cache instead of waiting on a checkpoint.' Descriptions
FROM    #perfmon_counters
WHERE   object_name=@Instancename+'Buffer Manager'
        and counter_name = 'Lazy writes/sec'
UNION ALL
SELECT  'Memory Grants Pending',
        cntr_value ,'Higher value indicates SQL Server need more memory' Descriptions
FROM    #perfmon_counters
WHERE   object_name=@Instancename+'Memory Manager'
        and counter_name = 'Memory Grants Pending'
UNION ALL
SELECT  'Memory Grants Outstanding',
        cntr_value ,'Higher value indicates peak user activity' Descriptions
FROM    #perfmon_counters
WHERE   object_name=@Instancename+'Memory Manager'
        and counter_name = 'Memory Grants Outstanding'
UNION ALL
SELECT  'process_physical_memory_low',
        process_physical_memory_low ,'process_physical_memory_low & process_virtual_memory_low Both equals to 0 means no internal memory pressure' Descriptions
FROM    sys.dm_os_process_memory WITH (NOLOCK)
UNION ALL
SELECT  'process_virtual_memory_low',
        process_virtual_memory_low ,'process_physical_memory_low & process_virtual_memory_low Both equals to 0 means no internal memory pressure' Descriptions
FROM    sys.dm_os_process_memory WITH (NOLOCK)
UNION ALL
SELECT  'Max_Server_Memory (MB)' ,
        [value_in_use],'If it is default to 2147483647, change the value with the correct amount of memory that you can allow SQL Server to utilize.' Descriptions
FROM    sys.configurations
WHERE   [name] = 'max server memory (MB)'
UNION ALL
SELECT  'Min_Server_Memory (MB)' ,
        [value_in_use],'If it is 0 means default value didnt get changed, it’ll always be better to have a minimum amount of memory allocated to SQL Server' Descriptions
FROM    sys.configurations
WHERE   [name] = 'min server memory (MB)'
UNION ALL
SELECT  'BufferCacheHitRatio',
        (a.cntr_value * 1.0 / b.cntr_value) * 100.0,'This ratio should be in between 95 and 100. Lesser value indicates memory pressure' Descriptions
FROM    sys.dm_os_performance_counters a
        JOIN (SELECT cntr_value,OBJECT_NAME FROM sys.dm_os_performance_counters
              WHERE counter_name = 'Buffer cache hit ratio base' AND
                    OBJECT_NAME = @Instancename+'Buffer Manager') b ON
                    a.OBJECT_NAME = b.OBJECT_NAME WHERE a.counter_name = 'Buffer cache hit ratio'
                    AND a.OBJECT_NAME = @Instancename+'Buffer Manager'

) AS D;

Monday, 18 February 2019

The OLE DB provider SQLNCLI for linked server does not contain the table dbname.dbo.tablename. The table either does not exist or the current user does not have permissions on that table. [SQLSTATE 42000] (Error 7314) or Executed as user: NT SERVICE\SQLSERVERAGENT. Login failed for user ''. The user is not associated with a trusted SQL Server connection. [SQLSTATE 28000] (Error 18452).

Executed as user: domain\administrator. The OLE DB provider SQLNCLI for linked server  does not contain the table dbname.dbo.tablename. The table either does not exist or the current user does not have permissions on that table. [SQLSTATE 42000] (Error 7314). The step failed.

(OR)

Message
Executed as user: NT SERVICE\SQLSERVERAGENT. Login failed for user ''. The user is not associated with a trusted SQL Server connection. [SQLSTATE 28000] (Error 18452).  The step failed.

Solution:

1) First check job is running account have exists on both source and destination
2) job running account have access to the linked server whether test is succeeded or not
3) kindly go to Linked server properties then security tab then be made using login context as sysadmin login with password the SQL job will run sysadmin login otherwise linked server using job will run SQL Agent service(Windows Authentication account)

Right click Linked server and then choose properties

choose security tab

kindly give job running account(SQL Authentication) with password in (be made using login context) radio button

then save the job

Thursday, 14 February 2019

scripts

--------------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 );

EXEC sp_addremotelogin 'ACCOUNTS';

To connect linked sever

We need to check default instance/named instance through SQL configuration manager

1)we need to enable TCP/IP and Named Pipes from SQL configuration manager
2)We need to start SQL Browse service from SQL configuration manager
3)we need to allow 1433 from windows firewall advanced security tab for both Inbound and Outbound rule
3) need to add IPADDRESS With machine name on C/windows32/drivers/etc/hosts file
4)SQL authentication only preferred while create connection of linked server
5)Create and Access SQL login should exists on both source and destination login list
6)Sometimes in SQL2005 Express edition and named instances we need to create [IP\instancename,port] in datasource for linked server like below


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

but original datasource is MATRIXSERVER\SQLEXPRESS

7)you need to change linked server properties then security tab then be made using login context as sysadmin login with password the sql job will run sysadmin login otherwise linked server using job will run SQL Agent service

------------------------------------------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---------------

you need to login to Domain\administrator account on RDP or machine

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=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]

Default instance:

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_CP1_CI_AS


Named Instance:

some time we will get error The Windows account admin-pc\administrator does not exist and cannot be provisioned as a SQL Server system administrator we need to use "" on account as below

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=SQLEXPRESS /SQLSYSADMINACCOUNTS="admin-pc\administrator" /SAPWD= texmo$123 /SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

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

Named instance:

https://dba.stackexchange.com/questions/143246/error-when-trying-to-install-sql-server-2012-ent-in-to-a-server-core-2008-vm


Note:

Need to login to server via Domainname\administrator and need to know sa account password

Before do we need take system dbs and users db backup with logins and linked servers

as user dbs will be removed after complete rebuild of systemdbs for server collation changes

-------------------------------------------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



------------------------------------Stored procedure used jobs-------------------------------


SELECT
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sJSTP].[step_uid] AS [StepID]
    , [sJSTP].[step_id] AS [StepNo]
    , [sJSTP].[step_name] AS [StepName]
    , CASE [sJSTP].[subsystem]
        WHEN 'ActiveScripting' THEN 'ActiveX Script'
        WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
        WHEN 'PowerShell' THEN 'PowerShell'
        WHEN 'Distribution' THEN 'Replication Distributor'
        WHEN 'Merge' THEN 'Replication Merge'
        WHEN 'QueueReader' THEN 'Replication Queue Reader'
        WHEN 'Snapshot' THEN 'Replication Snapshot'
        WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
        WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
        WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
        WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
        WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
        ELSE sJSTP.subsystem
      END AS [StepType]
    , [sPROX].[name] AS [RunAs]
    , [sJSTP].[database_name] AS [Database]
    , [sJSTP].[command] AS [ExecutableCommand]
    , CASE [sJSTP].[on_success_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: '
                    + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))
                    + ' '
                    + [sOSSTP].[step_name]
      END AS [OnSuccessAction]
    , [sJSTP].[retry_attempts] AS [RetryAttempts]
    , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
    , CASE [sJSTP].[on_fail_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: '
                    + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))
                    + ' '
                    + [sOFSTP].[step_name]
      END AS [OnFailureAction]
FROM
    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
        ON [sJSTP].[job_id] = [sJOB].[job_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
        ON [sJSTP].[job_id] = [sOSSTP].[job_id]
        AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
        ON [sJSTP].[job_id] = [sOFSTP].[job_id]
        AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
        ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
WHERE [sJSTP].[command] LIKE '%Pr_PreEmploymentAct%'
ORDER BY [JobName], [StepNo]

--------------------------------------------------------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

------------------------------------------Nth occurence position---------------------------------------------

create table #test
( t varchar(50) );

insert into #test values
( 'abc_1_2_3_4.gif'),
('zzz_12_3_3_45.gif');

select t,
  charindex( '_', t ) as Occurence_first,
  charindex( '_', t, charindex( '_', t ) + 1 ) as Occurence_second from   #test

  drop table #test

https://stackoverflow.com/questions/8726111/sql-server-find-nth-occurrence-in-a-string

------------------------------------------------To know db size and free space info----------------------------------------

Select DB_NAME() AS [DatabaseName], Name, file_id, physical_name,
    (size * 8.0/1024) as Size,
    ((size * 8.0/1024) - (FILEPROPERTY(name, 'SpaceUsed') * 8.0/1024)) As FreeSpace
    From sys.database_files

------------------------------------------------To know total transactions per second or minute---------------------------------------------

Create table #monitor(servername varchar(30),Totaltransactions int,AvgTransactionPerSecond int)

---First PASS
DECLARE @First BIGINT
DECLARE @Second BIGINT
SELECT @First = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE '%Databases%'
AND counter_name like '%Transactions/sec%'
AND instance_name like '%_Total%';
-- Following is the delay
WAITFOR DELAY '00:01:00'
--Second PASS
SELECT @Second = cntr_value
FROM sys.dm_os_performance_counters
WHERE OBJECT_NAME LIKE '%Databases%'
AND counter_name like '%Transactions/sec%'
AND instance_name like '%_Total%';

Insert into #monitor
SELECT @@SERVERNAME AS SERVERNAME,(@Second - @First) 'Total Transactions', ((@Second - @First)/60) 'Avg Transactions Per Second'

Select * from #monitor

drop table #monitor


-------------------------------------Comma with sum------------------------

Declare @t table(Sno int identity(1,1),Name nvarchar(200),Score int,servers varchar(100))

insert into @t
Select 'master',15,'TS3'
union
Select 'master',85,'TS4'
union
Select 'master',0,'TPCDB'
union
Select 'master',31,'SHELL1P1'
union
Select 'tempdb',10,'TS3'
union
Select 'tempdb',0,'TS4'
union
Select 'tempdb',3,Null
union
Select 'tempdb',4,'SHELL1P1'

select * from @t

SELECT Name,
stuff(
(
    SELECT ','+ CAST(Score AS varchar) + '('+ISNULL(servers,'DNP')+')'
FROM @t WHERE Name = t.Name
order by Sno desc
FOR XML PATH('')
),1,1,'')  resultswithservers
Into #comma
FROM (SELECT DISTINCT Name FROM @t ) t
Group by Name



SELECT Name,
stuff(
(
    SELECT ','+ CAST(Score AS varchar)
FROM @t WHERE Name = t.Name
order by Sno desc
FOR XML PATH('')
),1,1,'')  results
into #ct
FROM (SELECT DISTINCT Name FROM @t ) t
Group by Name

;WITH ValList AS(
        SELECT  Name,
                CAST(LEFT(results,PATINDEX('%,%', results) - 1) AS INT) Val,
                RIGHT(results,LEN(results) - PATINDEX('%,%', results)) Remainder,results
        FROM    #ct
        UNION ALL
        SELECT  Name,
                CAST(LEFT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN LEN(Remainder) ELSE PATINDEX('%,%', Remainder) - 1 END) AS INT) Val,
                RIGHT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN 0 ELSE LEN(Remainder) - PATINDEX('%,%', Remainder) END) Remainder,results
        FROM    ValList
        WHERE   LEN(Remainder) > 0

)
SELECT  V.Name,results,resultswithservers,
        SUM(Val)[Sumofcomma],AVG(Val) [AVG]
FROM    ValList V
join #comma C On V.Name=C.Name
GROUP BY V.Name,results,resultswithservers


--CREATE FUNCTION ufn_sum_csv(@string varchar(100))
--RETURNS @result
--Declare @SQL NVARCHAR(500)= N'SELECT @result_out = ' + REPLACE(@string, ',', '+')
--EXECUTE sp_executesql @SQL, N'@result_out FLOAT OUTPUT', @result_out = @result OUTPUT
--SELECT @result
--RETURN


drop table #ct
drop table #comma


http://www.kodyaz.com/t-sql/create-date-time-intervals-table-in-sql-server.aspx




--------------------------Change job owner name-----------------------------------------------------

DECLARE @name_holder VARCHAR(1000)
DECLARE My_Cursor CURSOR
FOR
SELECT  sj.name
FROM    msdb.dbo.sysjobs sj
        INNER JOIN msdb.dbo.syscategories ON  sj.category_id = msdb.dbo.syscategories.category_id
left join master.sys.syslogins l on sj.owner_sid = l.sid
WHERE   syscategories.name <> 'Report Server'
and l.name not in ('sa','Administrator')
OPEN My_Cursor
FETCH NEXT FROM My_Cursor INTO @name_holder
WHILE (@@FETCH_STATUS <> -1)
BEGIN
exec msdb..sp_update_job
        @job_name = @name_holder,
        @owner_login_name = 'sa'
FETCH NEXT FROM My_Cursor INTO @name_holder
END
CLOSE My_Cursor
DEALLOCATE My_Cursor

-------------------------------------------------------Job Name with Owner name-------------------------------------


SELECT  @@servername as servername,sj.name 'Job Name',
        syscategories.name 'Category',
        CASE [description]
          WHEN 'No Description available.' THEN ''
          ELSE [description]
        END AS 'Description'
,case when sj.enabled=1 then 'Yes' when sj.enabled=0 then 'No' end as Job_Enabled_status,owner_sid,l.name Job_OwnerName,sj.date_created,sj.date_modified
FROM    msdb.dbo.sysjobs sj
        INNER JOIN msdb.dbo.syscategories ON  sj.category_id = msdb.dbo.syscategories.category_id
left join master.sys.syslogins l on sj.owner_sid = l.sid
WHERE   syscategories.name <> 'Report Server'
and l.name not in ('sa','TEXMO\Administrator','rep','dbadmin')
ORDER BY sj.name


--select owner_sid,* from msdb.dbo.sysjobs
--WHERE enabled= 1



--------------------------------------------To know sysadmin of logins---------------------------------------------

USE master
GO

SELECT  @@servername as ServerName,p.name AS [loginname] ,
        p.type ,
        p.type_desc ,
        p.is_disabled,
        CONVERT(VARCHAR(10),p.create_date ,101) AS [created],
        CONVERT(VARCHAR(10),p.modify_date , 101) AS [update]
FROM    sys.server_principals p
        JOIN sys.syslogins s ON p.sid = s.sid
WHERE   p.type_desc IN ('SQL_LOGIN', 'WINDOWS_LOGIN', 'WINDOWS_GROUP')
        -- Logins that are not process logins
        AND p.name NOT LIKE '##%'
        -- Logins that are sysadmins
        AND s.sysadmin = 1
order by loginname


------------------------------------------------To take Backup of remote server via linked server-------------

DECLARE @DynamicSQL NVARCHAR(4000)

SET @DynamicSQL = 'BACKUP DATABASE Teis_scs TO DISK = ''c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\Teis_scs.bak'''

EXECUTE [P1SYS].master.dbo.sp_executesql @DynamicSQL

------------------------------------------------------------------To take Backup of remote server via linked server and remove 3 days retentions--------------

DECLARE @DeleteDate DATETIME =DATEADD(dd,-3,GETDATE()); -- Cutoff date

select @DeleteDate

EXEC [P1SYS].master.sys.xp_delete_file 0,'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\','BAK',@DeleteDate,0;

DECLARE @DynamicSQL NVARCHAR(4000)
DECLARE @name VARCHAR(50)
DECLARE @fileName VARCHAR(500)
DECLARE @fileDate VARCHAR(20)

select @name='Teis_scs'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)


SET @fileName = @name + '_' + @fileDate + '.bak';

select @fileName

SET @DynamicSQL = 'BACKUP DATABASE Teis_scs TO DISK = ''c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\'+@fileName+''''

select @DynamicSQL

EXECUTE [P1SYS].master.dbo.sp_executesql @DynamicSQL



--------------------------------------------------With replace option to take Backup of remote server via linked server-------------------------------------------------------------------


DECLARE @DeleteDate DATETIME =DATEADD(dd,-1,GETDATE()); -- Cutoff date

select @DeleteDate

EXEC [P1SYS].master.sys.xp_delete_file 0,'c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\','BAK',@DeleteDate,0;

DECLARE @DynamicSQL NVARCHAR(4000)
DECLARE @name VARCHAR(50)
DECLARE @fileName VARCHAR(500)
DECLARE @fileDate VARCHAR(20)

select @name='Teis_scs'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)


SET @fileName = @name + '_' + @fileDate + '.bak';

select @fileName

SET @DynamicSQL = 'BACKUP DATABASE Teis_scs TO DISK = ''c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\'+@fileName+''''+space(1)+' WITH INIT'

select @DynamicSQL

EXECUTE [P1SYS].master.dbo.sp_executesql @DynamicSQL
-------------------------------------------------------Insert via linked server when SQL Agent is not available like SQL Express 2012---------------------------------

Insert into [LinkedServerName].[dbname].dbo.DBGrowth
EXEC [LinkedServerName].dbname.sys.sp_executesql N'SELECT DB_NAME(database_id),
sum(size/128.0) [Size in MB],sum(size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0)[Free Space in MB],getdate() Dateofobservation
FROM master.sys.master_files
where DB_NAME(database_id)=db_name()
group by DB_NAME(database_id)'


------------------------------SQL job used objects list-----------------------------------------------

SELECT
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName]
    , [sJSTP].[step_uid] AS [StepID]
    , [sJSTP].[step_id] AS [StepNo]
    , [sJSTP].[step_name] AS [StepName]
    , CASE [sJSTP].[subsystem]
        WHEN 'ActiveScripting' THEN 'ActiveX Script'
        WHEN 'CmdExec' THEN 'Operating system (CmdExec)'
        WHEN 'PowerShell' THEN 'PowerShell'
        WHEN 'Distribution' THEN 'Replication Distributor'
        WHEN 'Merge' THEN 'Replication Merge'
        WHEN 'QueueReader' THEN 'Replication Queue Reader'
        WHEN 'Snapshot' THEN 'Replication Snapshot'
        WHEN 'LogReader' THEN 'Replication Transaction-Log Reader'
        WHEN 'ANALYSISCOMMAND' THEN 'SQL Server Analysis Services Command'
        WHEN 'ANALYSISQUERY' THEN 'SQL Server Analysis Services Query'
        WHEN 'SSIS' THEN 'SQL Server Integration Services Package'
        WHEN 'TSQL' THEN 'Transact-SQL script (T-SQL)'
        ELSE sJSTP.subsystem
      END AS [StepType]
    , [sPROX].[name] AS [RunAs]
    , [sJSTP].[database_name] AS [Database]
    , [sJSTP].[command] AS [ExecutableCommand]
    , CASE [sJSTP].[on_success_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: '
                    + QUOTENAME(CAST([sJSTP].[on_success_step_id] AS VARCHAR(3)))
                    + ' '
                    + [sOSSTP].[step_name]
      END AS [OnSuccessAction]
    , [sJSTP].[retry_attempts] AS [RetryAttempts]
    , [sJSTP].[retry_interval] AS [RetryInterval (Minutes)]
    , CASE [sJSTP].[on_fail_action]
        WHEN 1 THEN 'Quit the job reporting success'
        WHEN 2 THEN 'Quit the job reporting failure'
        WHEN 3 THEN 'Go to the next step'
        WHEN 4 THEN 'Go to Step: '
                    + QUOTENAME(CAST([sJSTP].[on_fail_step_id] AS VARCHAR(3)))
                    + ' '
                    + [sOFSTP].[step_name]
      END AS [OnFailureAction]
FROM
    [msdb].[dbo].[sysjobsteps] AS [sJSTP]
    INNER JOIN [msdb].[dbo].[sysjobs] AS [sJOB]
        ON [sJSTP].[job_id] = [sJOB].[job_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOSSTP]
        ON [sJSTP].[job_id] = [sOSSTP].[job_id]
        AND [sJSTP].[on_success_step_id] = [sOSSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysjobsteps] AS [sOFSTP]
        ON [sJSTP].[job_id] = [sOFSTP].[job_id]
        AND [sJSTP].[on_fail_step_id] = [sOFSTP].[step_id]
    LEFT JOIN [msdb].[dbo].[sysproxies] AS [sPROX]
        ON [sJSTP].[proxy_id] = [sPROX].[proxy_id]
WHERE [sJSTP].[command] LIKE '%Pr_Monitor_Health_Status_of_all_servers%'
ORDER BY [JobName], [StepNo]

---------------------------------DB growth------------------------------------



IF OBJECT_ID('tempdb..#T','U') IS NOT NULL
DROP TABLE #T
GO

CREATE TABLE #T (DBName nvarchar(500),SizeinMB float,FreeSpaceinMB float,Dateofobservation datetime)
GO

INSERT INTO #T
EXEC sp_MSforeachdb 'USE ? SELECT ''?'' DBName,
sum(size/128.0) [Size in MB],sum(size/128.0 - CAST(FILEPROPERTY(name, ''SpaceUsed'') AS INT)/128.0)[Free Space in MB],getdate() Dateofobservation
FROM master.sys.master_files
where DB_NAME(database_id)=db_name()
group by DB_NAME(database_id)'
GO

Insert into [db_admin].dbo.DBGrowth
SELECT DBName,SizeinMB TotalSizeinMB,FreeSpaceinMB,(SizeinMB -FreeSpaceinMB) [OccupiedinMB],  Round((SizeinMB -FreeSpaceinMB)/1024,2) [OccupiedinGB],Dateofobservation
FROM #T
where DBName not in ('dbname')
ORDER BY DBName



----------------------------------------------------object find-----------





--exec prfind 't','olap'

             

CREATE proc [dbo].[prfind] (@ptname varchar(1),@fbname varchar(50) = '')           

as                               

begin   

/*---------------HELP------------------------------------------------------------------------------------------------------------------------           

--Use : To find the T - Tables, V-  Views , P - Procedures , F - Functions , S - Search All texts , J - Search in JOBs , C - Column Names         

--Author : BABU SRINIVASAN.P - PBN                       

--Creation Date on 04/05/06 

--Parameter 1. @ptname - for type of object wise search           

--Parameter 2. @fbname - search for given word or letter 

--Parameter @ptname  t- for Tables, p - for Procedures , v for Views  , f for Functions,c for column name,s search the word in all objects,l(L) search and locate the old and new word         

--Parameter @fbname any part of the search object name           

---------------------------------------------------------------------------------------------------------------------------------------------*/       

if upper(@ptname)='T'  and  upper(@fbname)<>''                               

begin                               

select name from sys.tables where upper(name) like '%'+upper(@fbname)+'%'  order by create_date desc                                 

end                 

else if upper(@ptname)='T' and  upper(@fbname)=''                               

begin                               

select name from sys.tables   order by create_date desc                               

end                               

else if upper(@ptname)='P' and  upper(@fbname)<>''                               

begin                               

select name from sys.procedures where upper(name) like '%'+upper(@fbname)+'%'  order by create_date desc                                 

end                 

else if upper(@ptname)='P' and  upper(@fbname)=''               

begin                 

select name from sys.procedures  order by create_date desc             

end                     

else if upper(@ptname)='V'  and  upper(@fbname)<>''                                 

begin                               

select name from sys.views where upper(name) like '%'+upper(@fbname)+'%' order by create_date desc                                 

end                 

else if upper(@ptname)='V'  and  upper(@fbname)=''                               

begin                               

select name from sys.views order by create_date desc               

end                 

else if upper(@ptname)='F'  and  upper(@fbname)<>''                                 

begin                               

select name from sys.sysobjects where upper(xtype) in('TF','FN') and  upper(name) like '%'+upper(@fbname)+'%'   order by crdate desc                             

end                 

else if upper(@ptname)='F'  and  upper(@fbname)=''                               

begin                               

select name from sys.sysobjects where upper(xtype) in('TF','FN')  order by crdate desc               

end

else if upper(@ptname)='S'                             

begin                               

select b.type_desc,name=OBJECT_NAME(a.object_id) from sys.sql_modules a inner join sys.objects  b on a.object_id=b.object_id

where UPPER(definition) like '%'+ upper(@fbname) +'%'

union

select distinct type='SQL_JOBS',b.name from msdb.dbo.sysjobsteps a inner join msdb.dbo.sysjobs b on a.job_id=b.job_id

where UPPER(command) like '%'+ upper(@fbname) +'%'

end

else if upper(@ptname)='J'                             

begin                               

select distinct b.name from msdb.dbo.sysjobsteps a inner join msdb.dbo.sysjobs b on a.job_id=b.job_id

where UPPER(b.name) like '%'+ upper(@fbname) +'%'

end

else if upper(@ptname)='C'

begin

select distinct Object_Type=b.type_desc,Object_Name=b.name,Column_Name=a.name  from sys.columns a inner join sys.objects b on a.object_id=b.object_id

where upper(a.name) like '%'+ upper(@fbname) +'%'

order by b.name

end

else if upper(@ptname)='L'

begin

declare @s1 varchar(50),@s2 varchar(50)



set @s1=teis_mms.dbo.getstring(@fbname,0,',')

set @s2=teis_mms.dbo.getstring(@fbname,1,',')



declare  @oldloc table (cdesc varchar(500),cname varchar(500))

insert into @oldloc

select b.type_desc,name=OBJECT_NAME(a.object_id) from sys.sql_modules a inner join sys.objects  b on a.object_id=b.object_id

where UPPER(definition) like '%'+ upper(@s1) +'%'

union

select distinct type='SQL_JOBS',b.name from msdb.dbo.sysjobsteps a inner join msdb.dbo.sysjobs b on a.job_id=b.job_id

where UPPER(command) like '%'+ upper(@s1) +'%'



declare  @newloc table (cdesc varchar(500),cname varchar(500))



if @s2<>''

begin

insert into @newloc

select b.type_desc,name=OBJECT_NAME(a.object_id) from sys.sql_modules a inner join sys.objects  b on a.object_id=b.object_id

where UPPER(definition) like '%'+ upper(@s2) +'%'

union

select distinct type='SQL_JOBS',b.name from msdb.dbo.sysjobsteps a inner join msdb.dbo.sysjobs b on a.job_id=b.job_id

where UPPER(command) like '%'+ upper(@s2) +'%'

end



select distinct coldlocation_type_desc=a.cdesc,coldlocation_name=a.cname,cnewlocation_type_desc=b.cdesc,cnewlocation_name=b.cname from @oldloc a left join @newloc b on a.cname=b.cname



end



end


---------------------To check table object used objects in all db ----------------------------------------------


EXEC sp_MSforeachdb 'USE ? SELECT ''?'' DBName SELECT Distinct o.name,c.text
  FROM syscomments c
  JOIN sysobjects o
    ON c.id = o.id
  JOIN INFORMATION_SCHEMA.TABLES t
    ON  c.text LIKE ''%tablename%'''

----------------------------------The operating system returned error 21(The device is not ready.) -------------------

When you access database or issue sp_helpdb dbname
you will get below error

Msg 823, Level 24, State 2, Procedure sysfiles, Line 2
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000000016f0000 in file 'D:\Data\dbname_data.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Even when you tried to run dbcc checkdb you got below error

Msg 5901, Level 16, State 1, Line 1
One or more recovery units belonging to database 'dbname' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.
Msg 1823, Level 16, State 2, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 8, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details.
Msg 823, Level 24, State 2, Line 1
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x00000000116000 in file 'D:\Data\dbname.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.


solution

you need to issue below statement

use dbname

checkpoint

then
alter database dbname set online

now
you can issue sp_helpdb will works fine

sp_helpdb dbname

or

apply latest service pack in SQL 2012

https://www.sqlskills.com/blogs/paul/20122014-bug-that-can-cause-database-or-server-to-go-offline/

you can see error log as below

2019-08-21 06:33:59.950 spid62    Setting database option ONLINE to ON for database 'dbname'.

2018-08-21 09:32:23.720 spid33s    0 transactions rolled back in database 'dbname' (5:0). This is an informational message only. No user action is required.
2018-08-21 09:32:23.720 spid33s    Recovery is writing a checkpoint in database 'dbname' (5). This is an informational message only. No user action is required.
2018-08-21 09:32:23.690 spid33s    1 transactions rolled forward in database 'dbname' (5:0). This is an informational message only. No user action is required.
2018-08-21 09:32:23.150 spid33s    Starting up database 'dbname'.
2018-08-21 09:32:23.070 spid33s    Error: 17053, Severity: 16, State: 1.
2018-08-21 09:32:23.070 spid33s    fcb::close-flush: Operating system error (null) encountered.
2018-08-21 06:32:23.070 spid33s    Error: 17053, Severity: 16, State: 1.
2018-08-21 06:32:23.070 spid33s    fcb::close-flush: Operating system error (null) encountered.
2018-08-21 06:32:23.000 spid53    Error: 3314, Severity: 21, State: 5.
2018-08-21 06:32:23.000 spid53    During undoing of a logged operation in database 'dbname', an error occurred at log record ID (18985:421:2). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
2018-08-21 06:32:22.950 spid53    Database dbname was shutdown due to error 3314 in routine 'XdesRMReadWrite::RollbackToLsn'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.
2018-08-21 06:32:22.890 spid53    Error: 3314, Severity: 16, State: 3.
2018-08-21 06:32:22.890 spid53    During undoing of a logged operation in database 'dbname', an error occurred at log record ID (18985:421:4). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
2018-08-21 06:32:22.870 spid53    Error: 9001, Severity: 16, State: 5.
2018-08-21 06:32:22.870 spid53    The log for database 'dbname' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
2018-08-21 06:32:22.850 spid53    Error: 9001, Severity: 21, State: 4.
2018-08-21 06:32:22.850 spid53    The log for database 'dbname' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
2018-08-21 06:32:22.840 spid1s    Write error during log flush.
2018-08-21 06:32:22.830 spid1s    Error: 17053, Severity: 16, State: 1.
2018-08-21 06:32:22.830 spid1s    SQLServerLogMgr::LogWriter: Operating system error 21(The device is not ready.) encountered.
2019-08-21 06:32:20.100 spid53    Error: 823, Severity: 24, State: 2.
2019-08-21 06:32:20.100 spid53    The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000000016f0000 in file 'D:\Data\dbname.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2018-08-21 06:32:16.910 spid53    Error: 823, Severity: 24, State: 2.
2018-08-21 06:32:16.910 spid53    The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000000016f0000 in file 'D:\Data\dbname.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

----------------------------SP/Function/Views code via TSQL-------------------------------------------------------------------

SELECT
DB_NAME() DB_Name,
Name,
ISNULL(smsp.definition, ssmsp.definition) AS [Definition],create_date,modify_date--,*
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE  ISNULL(smsp.definition, ssmsp.definition) is not null and name not like '%sp_%' and name not like '%dm_%' and name not like '%xp_%' and name not like '%fn_%' and  name not like '%sys%' and
name not like '%xml_%' and ssmsp.definition is null and
(sp.type = N'P' OR sp.type = N'V' OR sp.type='TF')




EXEC sp_MSforeachdb 'USE ? SELECT ''?'' DBName SELECT Name,
ISNULL(smsp.definition, ssmsp.definition) AS [Definition],create_date,modify_date--,*
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = sp.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = sp.object_id
WHERE  ISNULL(smsp.definition, ssmsp.definition) is not null and name not like ''%sp_%'' and name not like ''%dm_%'' and name not like ''%xp_%'' and name not like ''%fn_%'' and  name not like ''%sys%'' and
name not like ''%xml_%'' and ssmsp.definition is null and
(sp.type = N''P'' OR sp.type = N''V'' OR sp.type=''TF'')'

----------------------------------------------Combinations check-------------------


WITH CTE as
( SELECT id,num,id as Grp,0 as parent,
         num as CSum,
         1 as cnt,
         CAST(id as Varchar(MAX)) as path
     from T where num<=75
and id not in (5,6,7)

   UNION all
  SELECT T.id,T.num,
         CTE.Grp as Grp,
         CTE.id as parent,
         T.num+CTE.CSum as CSum,
         CTE.cnt+1 as cnt,
         CTE.path+','+CAST(T.id as Varchar(MAX)) as path
    from T
  JOIN CTE on T.num+CTE.CSum<=75
             and CTE.id<T.id
where T.id not in (5,6,7)

),
BACK_CTE as
(select CTE.id,CTE.num,CTE.Grp,
         CTE.path ,CTE.cnt as cnt,
         CTE.parent,CSum
    from CTE where CTE.CSum=75
  union all
  select CTE.id,CTE.num,CTE.Grp,
         BACK_CTE.path,BACK_CTE.cnt,
         CTE.parent,CTE.CSum
   from CTE
 
   JOIN BACK_CTE on CTE.id=BACK_CTE.parent
              and CTE.Grp=BACK_CTE.Grp
              and BACK_CTE.CSum-BACK_CTE.num=CTE.CSum
  WHERE CTE.id not in (5,6,7)

)
select id,num,path, cnt as ItemsCount
into #Temp
  from BACK_CTE
where id not in (5,6,7)
and cnt=8



order by cnt,path,id


select *
--into T3
from
(select ROW_NUMBER()over(partition by id,num,path order by id ) row
, * from #Temp
)as a
where row=1



;with cte as
(
  select c.splitid, T.id,c.path,T.category
  from T
  inner join
  (
    select  i.items splitid,T3.id,T3.path
    from T3
    cross apply dbo.Split(T3.path, ',') i
  ) c
    on T.id = c.splitid
)
select path,category,count(distinct cast(splitid as int))[countofcategory] from cte
group by path,category
order by path,category

select *  from T order by id



--select *  from T3 order by id

--SELECT path, len(path) - len(replace(path, ',', '')) +1 IndividualCount
--FROM #comma

--select sum(num) s from T where id in (1,2,3,10,11,12,15,16)


--CREATE FUNCTION [dbo].[Split](@String varchar(MAX), @Delimiter char(1))     
--returns @temptable TABLE (items varchar(MAX))     
--as     
--begin   
--    declare @idx int     
--    declare @slice varchar(8000)     

--    select @idx = 1     
--        if len(@String)<1 or @String is null  return     

--    while @idx!= 0     
--    begin     
--        set @idx = charindex(@Delimiter,@String)     
--        if @idx!=0     
--            set @slice = left(@String,@idx - 1)     
--        else     
--            set @slice = @String     

--        if(len(@slice)>0)
--            insert into @temptable(items) values(@slice)     

--        set @String = right(@String,len(@String) - @idx)     
--        if len(@String) = 0 break     
--    end 
--return
--end;

  select  Distinct A.path,T3.ItemsCount,T.category,Sum(T.num)[Sumofpathnum],Count(T.id)[Countofcategoryid)]
from (
select  i.items splitid,T3.id,T3.path
    from T3
    cross apply dbo.Split(T3.path, ',') i
)as A
join T on A.splitid=T.id
join T3 on T3.path=A.path and T3.id=A.id and T3.id=T.id
Group by A.path,T3.ItemsCount,T.category

Select * from T3
--------------------------------------------------------------------------------------------------
























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


create table #c1(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
create table #cc1(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
INSERT INTO #c1
Select 'MS Dhoni',8.5,'Wicket Keeper'
union all
Select 'Alex Carey',8.0,'Wicket Keeper'

declare @vSQL varchar(1000)
--select @numrows = 5
select @vSQL = 'select top ' + convert(varchar, @numrows) + ' Name,Value,Description from #c1 Order by NEWID()'
insert into #cc1
Execute (@vSQL)
update #cc1 set Name=Name+'('+'WK'+')'

create table #c2(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
create table #cc2(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
declare @vSQL2 varchar(1000)
select @vSQL2 = 'select top ' + convert(varchar, @numrows2) + ' Name,Value,Description from #c2 Order by NEWID()'
INSERT INTO #c2
Select 'Virat Kohli',10.5,'BatsMan'
union all
Select 'Rohit Sharma',10.0,'BatsMan'
union all
Select 'Aaron Finch',9.5,'BatsMan'
union all
Select 'Shihar Dhawan',9.5,'BatsMan'
union all
Select 'D Short',8.5,'BatsMan'
union all
Select 'Rishat Pant',8.5,'BatsMan'
union all
Select 'U Khawaja',8.0,'BatsMan'
union all
select 'Peter Handscomb',8.0,'BatsMan'

insert into #cc2
Execute (@vSQL2)
update #cc2 set Name=Name+'('+'BT'+')'

create table #c3(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
create table #cc3(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
declare @vSQL3 varchar(1000)
INSERT INTO #c3

Select 'Marcus Stoinis',9.5,'All Rounder'
union
Select 'Glenn Maxwell',9.0,'All Rounder'
union
Select 'Krunal Pandya',8.5,'All Rounder'
union
Select 'Vijay Shankar',8.0,'All Rounder'


select @vSQL3 = 'select top ' + convert(varchar, @numrows3) + ' Name,Value,Description from #c3 Order by NEWID() '

INSERT INTO #cc3
Execute (@vSQL3)
update #cc3 set Name=Name+'('+'ALL'+')'


create table #c4(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
create table #cc4(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
declare @vSQL4 varchar(1000)
--select @numrows = 5
select @vSQL4 = 'select top ' + convert(varchar, @numrows4) + ' Name,Value,Description from #c4 Order by NEWID()'
insert into #c4
Select 'J Bumrah',9.5,'Bowler'
union
Select 'P Cummins',9.0,'Bowler'
union
Select 'Y Chahal',9.0,'Bowler'
union
Select 'K Richardson',8.5,'Bowler'
union
Select 'N Coulternile',8.5,'Bowler'
union
Select 'A Zampa',8.5,'Bowler'
union
Select 'Umesh Yadav',8.5,'Bowler'


insert into #cc4
Execute (@vSQL4)
update #cc4 set Name=Name+'('+'BL'+')'

Declare @tot float

Select @tot=sum([Sumvalue]) from (
select  sum(Value)[Sumvalue] from #cc1
union
select sum(Value)[Sumvalue] from #cc2
union
select sum(Value)[Sumvalue] from #cc3
 union
select sum(Value)[Sumvalue] from #cc4
)as B



select A.Id,A.Name,Description,sum(distinct A.Sumvalue) [Sum],@tot Total,100-@tot [Remaining]
from
(
select  Id,Name,sum(Value)[Sumvalue],Description from #cc1
group by Id,Name,Description
union
select Id,Name,sum(Value)[Sumvalue],Description from #cc2
group by Id,Name,Description
union
select Id,Name,sum(Value)[Sumvalue],Description from #cc3
group by Id,Name,Description
union
select Id,Name,sum(Value)[Sumvalue],Description from #cc4
group by Id,Name,Description
)as A
group by A.Id,A.Name,A.Description
order by A.Description DESC,A.Id,A.Name DESC

;with cteAllColumns as (
    select Name as col
        from #cc1
    union
    select Name as col
        from #cc2
   union
    select Name as col
        from #cc3
  union
    select Name as col
        from #cc4


)
select  *
into #fmtb4
  from cteAllColumns cte1
order by col desc

 

    ;with cteAllColumns as (
    select Name as col
        from #cc1
    union
    select Name as col
        from #cc2
   union
    select Name as col
        from #cc3
  union
    select Name as col
        from #cc4

)
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

 ;with cteAllColumns as (
    select Name as col
        from #cc1
    union
    select Name as col
        from #cc2
   union
    select Name as col
        from #cc3
  union
    select Name as col
        from #cc4

)
 insert into #fmtbl
select  cte2.col col2,cte1.col col1
  from cteAllColumns cte1
        cross join cteAllColumns cte2
      where cte1.col < cte2.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 Captain,col2 VicCaptain,
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 #cc1
drop table #cc2
drop table #cc3
drop table #cc4
drop table #fmtbl
drop table #fmtb4
drop table #CP
drop table #commatbl















sum with comma

-------------------------------------Comma with sum------------------------

Declare @t table(Sno int identity(1,1),Name nvarchar(200),Score int,servers varchar(100))

insert into @t
Select 'master',15,'TS3'
union
Select 'master',85,'TS4'
union
Select 'master',0,'TPCDB'
union
Select 'master',31,'SHELL1P1'
union
Select 'tempdb',10,'TS3'
union
Select 'tempdb',0,'TS4'
union
Select 'tempdb',3,Null
union
Select 'tempdb',4,'SHELL1P1'

select * from @t

SELECT Name,
stuff(
(
    SELECT ','+ CAST(Score AS varchar) + '('+ISNULL(servers,'DNP')+')'
 FROM @t WHERE Name = t.Name
 order by Sno desc
 FOR XML PATH('')
),1,1,'')  resultswithservers
Into #comma
FROM (SELECT DISTINCT Name FROM @t ) t
Group by Name



SELECT Name,
stuff(
(
    SELECT ','+ CAST(Score AS varchar)
 FROM @t WHERE Name = t.Name
 order by Sno desc
 FOR XML PATH('')
),1,1,'')  results
into #ct
FROM (SELECT DISTINCT Name FROM @t ) t
Group by Name

;WITH ValList AS(
        SELECT  Name,
                CAST(LEFT(results,PATINDEX('%,%', results) - 1) AS INT) Val,
                RIGHT(results,LEN(results) - PATINDEX('%,%', results)) Remainder,results
        FROM    #ct
        UNION ALL
        SELECT  Name,
                CAST(LEFT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN LEN(Remainder) ELSE PATINDEX('%,%', Remainder) - 1 END) AS INT) Val,
                RIGHT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN 0 ELSE LEN(Remainder) - PATINDEX('%,%', Remainder) END) Remainder,results
        FROM    ValList
        WHERE   LEN(Remainder) > 0

)
SELECT  V.Name,results,resultswithservers,
        SUM(Val)[Sumofcomma],AVG(Val) [AVG]
FROM    ValList V
join #comma C On V.Name=C.Name
GROUP BY V.Name,results,resultswithservers


--CREATE FUNCTION ufn_sum_csv(@string varchar(100))
--RETURNS @result
--Declare @SQL NVARCHAR(500)= N'SELECT @result_out = ' + REPLACE(@string, ',', '+')
--EXECUTE sp_executesql @SQL, N'@result_out FLOAT OUTPUT', @result_out = @result OUTPUT
--SELECT @result
--RETURN


drop table #ct
drop table #comma


--------------------------------SMTP fix

if (select  count(*) from msdb.dbo.sysmail_faileditems sf

join msdb.dbo.sysmail_event_log  sl on sf.mailitem_id=sl.mailitem_id
where log_date between  dateadd(minute, -10, getdate()) and dateadd(minute, 0, getdate())
and event_type='error' and subject='Daily Overall Planning'
)>0

Begin

SELECT 'SMTP Error'

end

else

Begin

Select 'No Error in SMTP Connection'

end

---------------------TRYING------------



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


create table #c1(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
create table #cc1(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
INSERT INTO #c1
Select 'TS3',8.5,'A'
union all
Select 'TS4',8.0,'B'

declare @vSQL varchar(1000)
--select @numrows = 5
select @vSQL = 'select top ' + convert(varchar, @numrows) + ' Name,Value,Description from #c1 Order by NEWID()'
insert into #cc1
Execute (@vSQL)
update #cc1 set Name=Name+'('+'WK'+')'

create table #c2(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
create table #cc2(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
declare @vSQL2 varchar(1000)
select @vSQL2 = 'select top ' + convert(varchar, @numrows2) + ' Name,Value,Description from #c2 Order by NEWID()'
INSERT INTO #c2
Select 'SHELL1P21',10.5,'A'
union all
Select 'SHELL1P2',10.0,'B'
union all
Select 'SHELL2P1',9.5,'A'
union all
Select 'SHELL2P2',9.5,'B'
union all
Select 'TEKPC2',8.5,'A'
union all
Select 'TMS',8.5,'B'
union all
Select 'WIP',8.0,'A'
union all
select 'MATRIX',8.0,'B'

insert into #cc2
Execute (@vSQL2)
update #cc2 set Name=Name+'('+'BT'+')'

create table #c3(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
create table #cc3(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
declare @vSQL3 varchar(1000)
INSERT INTO #c3

Select 'AR1',9.5,'A'
union
Select 'AR2',9.0,'B'
union
Select 'AR3',8.5,'A'
union
Select 'AR4',8.0,'B'


select @vSQL3 = 'select top ' + convert(varchar, @numrows3) + ' Name,Value,Description from #c3 Order by NEWID() '

INSERT INTO #cc3
Execute (@vSQL3)
update #cc3 set Name=Name+'('+'ALL'+')'


create table #c4(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
create table #cc4(Id int identity(1,1),Name varchar(300),Value float,Description Varchar(100))
declare @vSQL4 varchar(1000)
--select @numrows = 5
select @vSQL4 = 'select top ' + convert(varchar, @numrows4) + ' Name,Value,Description from #c4 Order by NEWID()'
insert into #c4
Select 'B1',9.5,'A'
union
Select 'B2',9.0,'B'
union
Select 'B3 ',9.0,'A'
union
Select 'B4',8.5,'B'
union
Select 'B5',8.5,'A'
union
Select 'B6',8.5,'B'
union
Select 'B7',8.5,'A'


insert into #cc4
Execute (@vSQL4)
update #cc4 set Name=Name+'('+'BL'+')'

Declare @tot float

Select @tot=sum([Sumvalue]) from (
select  sum(Value)[Sumvalue] from #cc1
union
select sum(Value)[Sumvalue] from #cc2
union
select sum(Value)[Sumvalue] from #cc3
 union
select sum(Value)[Sumvalue] from #cc4
)as B



select A.Id,A.Name,Description,sum(distinct A.Sumvalue) [Sum],@tot Total,100-@tot [Remaining]
from
(
select  Id,Name,sum(Value)[Sumvalue],Description from #cc1
group by Id,Name,Description
union
select Id,Name,sum(Value)[Sumvalue],Description from #cc2
group by Id,Name,Description
union
select Id,Name,sum(Value)[Sumvalue],Description from #cc3
group by Id,Name,Description
union
select Id,Name,sum(Value)[Sumvalue],Description from #cc4
group by Id,Name,Description
)as A
group by A.Id,A.Name,A.Description
order by A.Description DESC,A.Id,A.Name DESC

;with cteAllColumns as (
    select Name as col,Description,Value
        from #cc1
    union
    select Name as col,Description,Value
        from #cc2
   union
    select Name as col,Description,Value
        from #cc3
  union
    select Name as col,Description,Value
        from #cc4


)
select  *
into #fmtb4
  from cteAllColumns cte1
order by col desc

 

    ;with cteAllColumns as (
    select Name as col,Description,Value
        from #cc1
    union
    select Name as col,Description,Value
        from #cc2
   union
    select Name as col,Description,Value
        from #cc3
  union
    select Name as col,Description,Value
        from #cc4

)
select  cte1.col col1,cte2.col col2,cte1.Description Description1,cte2.Description as Description2,cte1.Value Value1,cte2.Value Value2
into #fmtbl
  from cteAllColumns cte1
        cross join cteAllColumns cte2
      where cte1.col < cte2.col
 order by cte1.col

 ;with cteAllColumns as (
    select Name as col,Description,Value
        from #cc1
    union
    select Name as col,Description,Value
        from #cc2
   union
    select Name as col,Description,Value
        from #cc3
  union
    select Name as col,Description,Value
        from #cc4

)
 insert into #fmtbl
select  cte2.col col2,cte1.col col1,cte2.Description Description2,cte1.Description Description1,cte1.Value Value1,cte2.Value Value2
  from cteAllColumns cte1
        cross join cteAllColumns cte2
      where cte1.col < cte2.col

       --select * from #fmtbl


  Declare @Column1 Nvarchar(300)
  Declare @Column2 Nvarchar(300)
  Declare @Column1After Nvarchar(300)
  Declare @Column2After Nvarchar(300)
    Declare @Val1 Nvarchar(300)
Declare @Val2 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 col2<>@Column2+'['+'VC'+']'




 FETCH NEXT FROM CC INTO  @Column1,@Column2



  END


  select 'ft',* from #fmtbl

   select 'ftb4',* from #fmtb4


   update #fmtb4 set col=col+cast(Value as varchar)

      select 'After cursor update ftb4',* from #fmtb4


--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

  SELECT Distinct top 2
stuff(
(
    SELECT ','+ cast(Value as varchar) FROM #fmtb4  FOR XML PATH('')
),1,1,'')  Results
into #commatblval
FROM (SELECT DISTINCT top 1 col FROM #fmtb4  ) #fmtb4

CLOSE CC
DEALLOCATE CC

Select '#commatbl',* from #commatbl


Select '#commatblval',* from #commatblval

select col1,col2,Results,Description1,Description2,Value1,Value2
Into #CP
 FROM #fmtbl P CROSS Join
#commatbl C

select col1,col2,Results,Description1,Description2,Value1,Value2
Into #CPval
 FROM #fmtbl P1 CROSS Join
#commatblval C1

Select '#CP',* from #CP

Select '#CPval',* from #CPval



--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
--where Results is  null
Order by col1,col2



Select 'AFTER Merge'AfterMerge,cpt.col1,cpt.col2,cpt.Value1,cpt.Value2,cpt.Description1,cpt.Description2,cpt.Results,cptval.Results RCSV

into #finalb4

 from #CP cpt
join #CPval cptval on cpt.col1=cptval.col1 and cpt.col2=cptval.col2



Select * from #finalb4


;WITH ValList AS(
        SELECT  col1,col2,
                CAST(LEFT(RCSV,PATINDEX('%,%', RCSV) - 1) AS FLOAT) Val,
                RIGHT(RCSV,LEN(RCSV) - PATINDEX('%,%', RCSV)) Remainder,RCSV
        FROM    #finalb4
        UNION ALL
        SELECT  col1,col2,
                CAST(LEFT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN LEN(Remainder) ELSE PATINDEX('%,%', Remainder) - 1 END) AS FLOAT) Val,
                RIGHT(Remainder,CASE WHEN PATINDEX('%,%', Remainder) = 0 THEN 0 ELSE LEN(Remainder) - PATINDEX('%,%', Remainder) END) Remainder,RCSV
        FROM    ValList
        WHERE   LEN(Remainder) > 0

)
SELECT V.col1,V.col2,C.RCSV,Results,
        SUM(CAST(Val as float))[Sumofcomma],AVG(Val) [AVG]
FROM    ValList V
join #finalb4 C On V.col1=C.col1 and V.col2=C.col2
GROUP BY V.col1,V.col2,C.RCSV,Results


--SELECT DISTINCT col1 Captain,col2 VicCaptain,
--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 #cc1
drop table #cc2
drop table #cc3
drop table #cc4
drop table #fmtbl
drop table #fmtb4
drop table #CP
drop table #commatbl
drop table #commatblval
drop table #CPval
drop table #finalb4