--------------Currently user running Query-------
use master
SELECT 'Currently Running Query'as RunningQuery,DATEDIFF(MINUTE, A.last_batch, GETDATE()) RunningTime,
A.spid, A.blocked, A.waittime, db_name(A.dbid) As dbname,B.text as sqlstatement,
A.hostname, A.loginame,A.program_name,A.last_batch,A.nt_username
FROM sys.sysprocesses A CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) B
WHERE A.dbid > 4
---------------------------------Batch requests/sec
DECLARE @BRPS BIGINT
SELECT @BRPS=cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Batch Requests/sec%'
WAITFOR DELAY '000:00:01'
SELECT @@SERVERNAME AS ServerName,(cntr_value-@BRPS)/10.0 AS "Batch Requests/sec"
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Batch Requests/sec%'
-------------------------------------find most consumed objects/statements info------------------------------
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
--ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
ORDER BY qs.total_worker_time DESC -- CPU time
----------------------------------------------find most consumed stored procedure info
SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
,cached_time
,last_execution_time
,execution_count
,total_worker_time / execution_count AS AVG_CPU
,total_elapsed_time / execution_count AS AVG_ELAPSED
,total_logical_reads / execution_count AS AVG_LOGICAL_READS
,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
,total_physical_reads / execution_count AS AVG_PHYSICAL_READS
FROM sys.dm_exec_procedure_stats
where database_id<>32767 and DB_NAME(database_id) not in ('master','msdb')
ORDER BY AVG_LOGICAL_READS DESC
----------------------------------------------------------Table Row count----------
SELECT @@servername as servername,DB_NAME() AS [Database Name],T.name AS [TABLE NAME],
I.row_count AS [ROWCOUNT]
FROM sys.tables AS T
INNER JOIN sys.dm_db_partition_stats AS I
ON T.object_id = I.object_id
AND I.index_id < 2
ORDER BY I.row_count DESC
-------------------------------------------blocking info with dbcc inputbuffer
select distinct top 1 @id=spid from sys.sysprocesses where blocked<>0
set @x=@id
if @x>0
begin
select @x as blockingspid
dbcc inputbuffer(@x)
end
else
select 'nothing blocked' as blockingstatus
---------------------------------------Page life expectancy
SELECT @@SERVERNAME AS ServerName,[object_name],
[counter_name],
[cntr_value] FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'
Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references.
check Page Life Expectancy. If its value is low (below 300 seconds), this is a clear indication of memory pressure.
--------------------------------------Lazy writer------------
SELECT object_name, counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Lazy writes/sec'
The lazy writer is a process that periodically checks the available free space in the buffer cache between two checkpoints and ensures that there is always enough free memory.
When the lazy writer determines free pages are needed in the buffer for better performance, it removes the old pages before the regular checkpoint occurs
If a dirty data page (a page read and/or modified) in the buffer hasn’t been used for a while, the lazy writer flushes it to disk and then marks as free in the buffer cache
The Lazy writes metric is defined as "Number of times per second SQL Server relocates dirty pages from buffer pool (memory) to disk"
The threshold value for Lazy Writes is 20
If the Lazy Writes value is constantly higher than 20, to be sure that the server is under memory pressure, check Page Life Expectancy. If its value is low (below 300 seconds), this is a clear indication of memory pressure.
------------------------------check point--------------------------------------------------------------------------------------------------------------------------------------------------------
A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction
at a check point, which can be automatic (occurs automatically to meet the recovery interval request) , indirect (occurs automatically to meet the database target recovery time),
manual (occurs when the CHECKPOINT command is executed), and internal (occurs along with some server-level operations, such as backup creation)
--------------------------------------------------------Latch--------------------------
a latch is a lightweight synchronization object used by the Storage Engine to protect memory structures used internally by SQL Server.
3 Different types of Latches:
IO Latches
Buffer Latches (BUF)
Non-Buffer Latches (Non-BUF)
Latch Modes
Keep
Shared
Update
Exclusive
Destroy
-------------------------------Free list stalls/sec---------------------------
SELECT 'Free list stalls/sec',
cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%' and counter_name LIKE '%Free list stalls/sec%'
----------------------------------------find number of core is SQL using-----------
select scheduler_id, cpu_id, status, is_online
from sys.dm_os_schedulers
where status = 'VISIBLE ONLINE'
------------------------------------To find out Unused DB info------------
SELECT @@ServerName AS server
,NAME AS dbname
,COUNT(STATUS) AS number_of_connections
,GETDATE() AS queryexecutedtimestamp
FROM sys.databases sd
LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME
SELECT TOP 10000 s.[database_id]
,d.name
,d.create_date
,s.[index_id]
,s.[object_id]
,s.[user_seeks]
,s.[user_scans]
,s.[user_lookups]
,s.[user_updates]
,s.[last_user_seek]
,s.[last_user_scan]
,s.[last_user_lookup]
,s.[last_user_update]
FROM [master].[sys].[dm_db_index_usage_stats] as s right outer join sys.databases d on s.database_id = d.database_id
where d.database_id > 4
order by d.name
select d.name, x1 =
(select X1= max(bb.xx)
from (
select xx = max(last_user_seek)
where max(last_user_seek) is not null
union all
select xx = max(last_user_scan)
where max(last_user_scan) is not null
union all
select xx = max(last_user_lookup)
where max(last_user_lookup) is not null
union all
select xx = max(last_user_update)
where max(last_user_update) is not null) bb)
FROM master.dbo.sysdatabases d
left outer join
sys.dm_db_index_usage_stats s
on d.dbid= s.database_id
where d.dbid > 4
group by d.name
-----------------------DMV query that will tell us high level information of disk latency
SELECT
*
,wait_time_ms/waiting_tasks_count AS 'Avg Wait in ms',(wait_time_ms/waiting_tasks_count)/1000 as 'AVG Wait in Sec',
(wait_time_ms/waiting_tasks_count)/1000/60 as 'AVG Wait in Mint'
FROM
sys.dm_os_wait_stats
WHERE
waiting_tasks_count > 0
ORDER BY
wait_time_ms DESC
----------------------------------------Number of user connections info ---------------
SELECT ConnectionStatus = CASE WHEN dec.most_recent_sql_handle = 0x0
THEN 'Unused'
ELSE 'Used'
END
, CASE WHEN des.status = 'Sleeping'
THEN 'sleeping'
ELSE 'Not Sleeping'
END
, ConnectionCount = COUNT(1)
FROM sys.dm_exec_connections dec
INNER JOIN sys.dm_exec_sessions des ON dec.session_id = des.session_id
GROUP BY CASE WHEN des.status = 'Sleeping'
THEN 'sleeping'
ELSE 'Not Sleeping'
END
, CASE WHEN dec.most_recent_sql_handle = 0x0
THEN 'Unused'
ELSE 'Used'
END;
---------------------------------------------Adding tempdb files based on core/processor-------------------------
Select @@servername as servername,DB_NAME(mf.database_id) database_name
, mf.name logical_name, mf.file_id
, CONVERT (DECIMAL (20,2)
, (CONVERT(DECIMAL, size)/128)) as [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(DECIMAL(20,2)
,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN CONVERT(DECIMAL(20,2)
, (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, physical_name from sys.master_files mf
where database_id =2 and type_desc= 'rows'
SELECT @@servername as servername,cpu_count AS logicalCPUs FROM sys.dm_os_sys_info
--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev2.ndf' , SIZE =8MB , FILEGROWTH = 5MB) --<<--Update the data file location/Size/AutoGrowth
--GO
--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev3',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev3.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO
--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev4',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev4.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO
--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev5',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev5.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO
--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev6',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev6.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO
---ETC, add files as per processors count
------------------------------------------Enable all Agent Jobs-----------------
SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N'''
+ cast(job_id as varchar(40)) + ''', @enabled=1'
FROM msdb..DBA_Agent_jobs_Snapshot
--Run output from above
------------------------------------------Disable all Agent Jobs
Select * into msdb..DBA_Agent_jobs_Snapshot
FROM msdb..sysjobs
WHERE ENABLED = 1
SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N'''
+ cast(job_id as varchar(40)) + ''', @enabled=0'
FROM msdb..DBA_Agent_jobs_Snapshot
--Run output from above
-----------------------------------Kill the unused session on db bulk------------------------------------------
ALTER DATABASE teis_wfl SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE teis_wfl SET MULTI_USER
----------------------------------------------------------------DB Mail for Backup status report---------------------------------
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @SER NVARCHAR(20);
DECLARE @StrSubject VARCHAR(100);
SET @SER=@@SERVERNAME
SELECT @StrSubject = 'Texmo DB Server Backup Status Report ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'
SET @tableHTML =
N'<H1>Database Backup Status Report</H1>' +
N'<table border="1">' +
N'<tr><th>Server_Name</th><th>Database_Name</th><th>DaysSinceLastBackup</th>' +
N'<th>Last FullBackup Date & time</th><th>Backup_Status_Today</th><th>DB_Status</th></tr>' +
CAST ( ( SELECT td = @SER, '',td = B.name, '',
td = ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER'), '',
td =ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER'), '',
td = case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(MST.backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(MST.backup_finish_date)))), 'NEVER')=0 then 'Completed'
else 'Not completed' end,d.state_desc
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
join sys.databases d ON B.name=d.name
where B.name not in ('tempdb')
GROUP BY B.name,B.status,d.state_desc
ORDER BY B.name
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
PRINT @tableHTML
EXEC msdb.dbo.sp_send_dbmail @profile_name='Database Administrator',
@recipients = 'JAR@texmo.net;Shalvansha.Razak@texmocastings.com;RSK@texmo.net',
--@recipients = 'JAR@texmo.net',
@subject = @StrSubject,
@body = @tableHTML,
@body_format = 'HTML' ;
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @SER NVARCHAR(20);
DECLARE @StrSubject VARCHAR(100);
SET @SER=@@SERVERNAME
SELECT @StrSubject = 'Texmo DB Server Backup Status Report ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'
SET @tableHTML =
N'<H1>Database Backup Status Report</H1>' +
N'<table border="1">' +
N'<tr><th>Server_Name</th><th>Database_Name</th><th>DaysSinceLastBackup</th>' +
N'<th>Last FullBackup Date & time</th><th>Backup_Status_Today</th><th>DB_Status</th><th>Backup_type</th><th>Backup Size in MB</th><th>Backup Size in GB</th></tr>' +
CAST ( ( SELECT td = @SER, '',td = LTRIM(RTRIM(B.name)), '',
td = ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER'), '',
td =ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER'), '',
td = case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(MST.backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(MST.backup_finish_date)))), 'NEVER')=0 then 'Completed'
else 'Not completed' end, '',td=d.state_desc, '',
td= CASE MST.type
WHEN 'D' THEN 'Full Database'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Differential'
WHEN 'F' THEN 'File Level'
WHEN 'G' THEN 'File Level Differential'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END, '',
td= LTRIM(RTRIM(convert(varchar,cast(MST.backup_size/1024/1024 as money),10))), '',td= LTRIM(RTRIM(convert(varchar,cast(MST.backup_size/1024/1024 as INT),10)))/1024, ''
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
join sys.databases d ON B.name=d.name
where B.name not in ('tempdb')
and (CONVERT(datetime, MST.backup_start_date, 102) >= GETDATE() - 1)
GROUP BY B.name,B.status,d.state_desc,MST.type,convert(varchar,cast(MST.backup_size/1024/1024 as INT),10),convert(varchar,cast(MST.backup_size/1024/1024 as money),10)
ORDER BY B.name,B.status,d.state_desc,MST.type,convert(varchar,cast(MST.backup_size/1024/1024 as INT),10),convert(varchar,cast(MST.backup_size/1024/1024 as money),10)
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
PRINT @tableHTML
EXEC msdb.dbo.sp_send_dbmail @profile_name='Database Administrator',
--@recipients = 'JAR@texmo.net;Shalvansha.Razak@texmocastings.com;RSK@texmo.net',
@recipients = 'JAR@texmo.net',
@subject = @StrSubject,
@body = @tableHTML,
@body_format = 'HTML' ;
----------------------------------------------------Backup status query----------------------------------------------------
SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER') as 'Last FullBackup Date & time',case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER')=0 then 'Completed'
else 'Not completed' end as BackupStatus
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
where B.name not in ('tempdb')
GROUP BY B.name,B.status
ORDER BY B.name
SELECT @@SERVERNAME AS ServerName,B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER') as 'Last FullBackup Date & time',case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER')=0 then 'Completed'
else 'Not completed' end as Backup_Status_Today,d.state_desc,
CASE MST.type
WHEN 'D' THEN 'Full Database'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Differential'
WHEN 'F' THEN 'File Level'
WHEN 'G' THEN 'File Level Differential'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END AS backup_type,
convert(varchar,cast(MST.backup_size/1024/1024 as money),10) as 'Backup Size in MB',convert(varchar,cast(MST.backup_size/1024/1024 as INT),10) /1024 as 'Backup Size in GB'
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
join sys.databases d ON B.name=d.name
--------------------------------------Databases not backed up within 24 hrs----------
DECLARE
@dblist NVARCHAR(MAX) = N'',
@c CHAR(2) = CHAR(13) + CHAR(10), @t CHAR(1) = CHAR(9),
@s SYSNAME = @@SERVERNAME;
SELECT @dblist += @c + @c + @s + @c + @t + db + @c + @t
+ COALESCE(CONVERT(CHAR(10), d, 120) + ' ' + CONVERT(CHAR(8), d, 108), 'NULL')
+ @c + @t + CONVERT(VARCHAR(11), age)
FROM
(
SELECT
bs.database_name AS db,
MAX(bs.backup_finish_date) AS d,
DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS age
-------------^^^^ please don't use lazy shorthand like hh
FROM msdb.dbo.backupset AS bs
WHERE [type] = 'D'
GROUP BY database_name
HAVING (MAX(backup_finish_date) < DATEADD(HOUR, -24, GETDATE()))
UNION ALL -- why UNION? By definition there are no duplicates to filter.
-- in fact you could re-write this without two separate queries at all.
SELECT
name AS db,
NULL AS d,
9999 AS age
FROM
master.sys.databases AS d
------ don't use sysdatabases - old and deprecated
WHERE name <> N'tempdb' AND NOT EXISTS
(
SELECT 1 FROM msdb.dbo.backupset
WHERE database_name = d.name
)
) AS x
ORDER BY db;
IF @dblist > N''
BEGIN
PRINT @dblist;
--EXEC msdb.dbo.sp_send_dbmail
-- @recipients = 'dba@someemailaddress.com',
-- @subject = N'Databases not backed up within 24 hrs',
-- @body = @dblist;
END
----------------------------------------------Missing index Bulk-------------------------
EXEC sp_MSforeachdb 'USE ? SELECT ''?''dbname,dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
''CREATE INDEX [IX_'' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + ''_''
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''''),'', '',''_''),''['',''''),'']'','''')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN ''_''
ELSE ''''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''''),'', '',''_''),''['',''''),'']'','''')
+ '']''
+ '' ON '' + dm_mid.statement
+ '' ('' + ISNULL (dm_mid.equality_columns,'''')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN '','' ELSE
'''' END
+ ISNULL (dm_mid.inequality_columns, '''')
+ '')''
+ ISNULL ('' INCLUDE ('' + dm_mid.included_columns + '')'', '''') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC '
------------------------------------To send DB offline status report via db mail------------------------------------
if(select count(*) from sys.databases where state_desc<>'Online')>0
Begin
DECLARE @table NVARCHAR(MAX) ;
SET @table =
N'<H1>Offline Databases Report</H1>' +
N'<table border="1">' +
N'<tr><th>Database Name</th><th>Database Status</th></tr>' +
CAST ( ( Select td=name, '',td=state_desc from sys.databases where state_desc<>'Online'
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name='ProfileName', --Change to your Profile Name
@recipients='email@domain.com;email1@domain.com', --Put the email address of those who want to receive the e-mail
@subject = 'Offline Databases Report',
@body = @table,
@body_format = 'HTML' ;
END
Else Print 'All Databases are Online'
-------------------------------------Index fragmentation report-----------------------
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,CASE
WHEN indexstats.avg_fragmentation_in_percent < 10
THEN 'NOTHING'
WHEN indexstats.avg_fragmentation_in_percent >= 10
AND indexstats.avg_fragmentation_in_percent < 30
THEN 'REORGANIZE'
WHEN indexstats.avg_fragmentation_in_percent >= 30
THEN 'REBUILD'
END as fragmentation_comments,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() and indexstats.page_count>=1000 and indexstats.avg_fragmentation_in_percent>=10
ORDER BY indexstats.avg_fragmentation_in_percent desc
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,CASE
WHEN indexstats.avg_fragmentation_in_percent < 10
THEN 'NOTHING'
WHEN indexstats.avg_fragmentation_in_percent >= 10
AND indexstats.avg_fragmentation_in_percent < 30
THEN 'REORGANIZE'
WHEN indexstats.avg_fragmentation_in_percent >= 30
THEN 'REBUILD'
END as fragmentation_comments,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (14, NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = 14 and indexstats.page_count>=1000 and indexstats.avg_fragmentation_in_percent>=10
ORDER BY indexstats.avg_fragmentation_in_percent desc
Fragmentation is less than 10% – no de-fragmentation is required. It is generally accepted that in majority of environments index fragmentation less than 10% in negligible and its performance impact on the SQL Server is minimal.
Fragmentation is between 10-30% – it is suggested to perform index reorganization
Fragmentation is higher than 30% – it is suggested to perform index rebuild
-------------------------------------------------To view statistics status info against Database-------------------------
SELECT Distinct @@servername as Servername,db_name(db_id()) as DB_Name, SCHEMA_NAME(schema_id) SchemaName,t.name TableName, s.[name] StatName, STATS_DATE(t.object_id,s.[stats_id]) LastUpdated ,'['+SCHEMA_NAME(schema_id)+'].['+t.name+']' AS SchemaTable
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'
and convert(varchar(10), STATS_DATE(t.object_id,s.[stats_id]),121)<>'2018-11-15'
SELECT Distinct 'Update Statistics'+ '['+SCHEMA_NAME(schema_id)+'].['+t.name+']' AS SchemaTable
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'
and convert(varchar(10), STATS_DATE(t.object_id,s.[stats_id]),121)<>'2018-11-15'
-------------------------------------------Recovery pending----------------------------
SELECT name, state_desc from sys.databases
where state_desc='RECOVERY_PENDING'
exec sp_resetstatus dbname
ALTER DATABASE dbname SET ONLINE
DBCC CHECKDB('dbname') WITH NO_INFOMSGS
ALTER DATABASE dbname SET SINGLE_USER
DBCC CHECKDB('dbname',REPAIR_REBUILD)
ALTER DATABASE dbname SET MULTI_USER
https://promx.net/en/2016/11/microsoft-sql-server-database-repair-following-recovery-pending-status/
-----------------------------------------Alter Query for move tempdb secondary files-----------------------
Transact-SQL
SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
+ ' FILENAME = ''Z:\MSSQL\DATA\' + f.name
+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END
+ ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb');
------------------------------------------------to check what job is running at a particular time----------------------
SELECT * FROM
(
SELECT JobName, RunStart, DATEADD(second, RunSeconds, RunStart) RunEnd, RunSeconds,RunSeconds/60 Mints,RunSeconds/60/60 hrs
FROM
(
SELECT j.name AS 'JobName',
msdb.dbo.agent_datetime(run_date, run_time) AS 'RunStart',
((jh.run_duration/1000000)*86400)
+ (((jh.run_duration-((jh.run_duration/1000000)*1000000))/10000)*3600)
+ (((jh.run_duration-((jh.run_duration/10000)*10000))/100)*60)
+ (jh.run_duration-(jh.run_duration/100)*100) RunSeconds
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
WHERE jh.step_id=0 --The Summary Step
) AS H
) AS H2
WHERE '2018-10-22 03:30:00' BETWEEN RunStart AND RunEnd
ORDER BY JobName, RunEnd
------------------------------------------------Log reuse monitor---------------------------------------------
dbcc sqlperf(logspace)
Select @@SERVERNAME AS ServerName,name,log_reuse_wait_desc,recovery_model_desc,state_desc,* from sys.databases
where log_reuse_wait_desc<>'NOTHING'
SELECT instance_name
, [Data File(s) Size (KB)] * 1.0 / 1024 [Data File(s) Size (MB)]
, [Log File(s) Size (KB)] * 1.0 / 1024 [Log File(s) Size (MB)]
, [Log File(s) Used Size (KB)] * 1.0 / 1024 [Log File(s) Used Size (MB)]
, [Percent Log Used]
, log_reuse_wait_desc
FROM ( SELECT os.counter_name, os.instance_name, os.cntr_value
, db.log_reuse_wait_desc
FROM sys.dm_os_performance_counters os
JOIN sys.databases db ON os.instance_name = db.name
WHERE os.counter_name IN
(
'Data File(s) Size (KB)'
, 'Log File(s) Size (KB)'
, 'Log File(s) Used Size (KB)'
, 'Percent Log Used'
)
--AND os.instance_name = 'TLogTruncate'
) as SourceTable
PIVOT (
MAX(cntr_value) FOR counter_name IN
([Data File(s) Size (KB)]
, [Log File(s) Size (KB)]
, [Log File(s) Used Size (KB)]
, [Percent Log Used])) as PivotTable
--------------------------------------------To see DB growth for all DB-----------------------------------------------
SELECT 'Database Name' = DB_NAME(database_id)
,'FileName' = NAME
,FILE_ID
,'size' = CONVERT(NVARCHAR(15), CONVERT(BIGINT, size) * 8) + N' KB'
,'maxsize' = (
CASE max_size
WHEN - 1
THEN N'Unlimited'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, max_size) * 8) + N' KB'
END
)
,'growth' = (
CASE is_percent_growth
WHEN 1
THEN CONVERT(NVARCHAR(15), growth) + N'%'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, growth) * 8) + N' KB'
END
)
,'type_desc' = type_desc
FROM sys.master_files
ORDER BY database_id
SELECT @@SERVERNAME as ServerName,*
FROM (SELECT [DBName],YEAR([Dateofobservation]) [Year],
convert(varchar(10),[Dateofobservation],121) [Date],
SUM([OccupiedinMB]) [OccupiedinMB]
FROM [db_admin].[dbo].[DBGrowth]
GROUP BY [DBName],YEAR([Dateofobservation]),
convert(varchar(10),[Dateofobservation],121)) AS MontlySalesData
PIVOT( SUM([OccupiedinMB])
FOR [Date] IN ( [2018-11-29],[2018-11-30],[2018-12-01],[2018-12-02],[2018-12-03],[2018-12-04],[2018-12-05], [2018-12-06],[2018-12-07],[2018-12-08],[2018-12-09],[2018-12-10],[2018-12-11]
,[2018-12-12],[2018-12-13],[2018-12-14],[2018-12-15],[2018-12-16],[2018-12-17],[2018-12-18],[2018-12-19],[2018-12-20],[2018-12-21],[2018-12-22],[2018-12-23],[2018-12-24]
,[2018-12-25],[2018-12-26],[2018-12-27],[2018-12-28],[2018-12-29],[2018-12-30],[2018-12-31]
)) AS MNamePivot
Declare @s varchar(max)=''
Declare @i int
Declare @j int
set @i=1
select @j=right(convert(varchar(7),getdate(),121),2)
select @j as j
set @s='SELECT @@SERVERNAME as ServerName,*'
set @s=@s +'FROM (SELECT [DBName],YEAR([Dateofobservation]) [Year],
convert(varchar(10),[Dateofobservation],121) [Date],
SUM([OccupiedinMB]) [OccupiedinMB]
FROM [db_admin].[dbo].[DBGrowth]
GROUP BY [DBName],YEAR([Dateofobservation]),
convert(varchar(10),[Dateofobservation],121)) AS MontlySalesData'
set @s=@s +' PIVOT( SUM([OccupiedinMB])
FOR [Date] IN ( '
set @s=@s+' [2018-'+cast(@j as varchar)+'-01],[2018-'+cast(@j as varchar)+'-02],[2018-'+cast(@j as varchar)+'-03],[2018-'+cast(@j as varchar)+'-04]
,[2018-'+cast(@j as varchar)+'-05],[2018-'+cast(@j as varchar)+'-06],[2018-'+cast(@j as varchar)+'-07],[2018-'+cast(@j as varchar)+'-08]
,[2018-'+cast(@j as varchar)+'-09],[2018-'+cast(@j as varchar)+'-10],[2018-'+cast(@j as varchar)+'-11],[2018-'+cast(@j as varchar)+'-12]
,[2018-'+cast(@j as varchar)+'-13],[2018-'+cast(@j as varchar)+'-14],[2018-'+cast(@j as varchar)+'-15],[2018-'+cast(@j as varchar)+'-16]
,[2018-'+cast(@j as varchar)+'-17],[2018-'+cast(@j as varchar)+'-18],[2018-'+cast(@j as varchar)+'-19],[2018-'+cast(@j as varchar)+'-20]
,[2018-'+cast(@j as varchar)+'-21],[2018-'+cast(@j as varchar)+'-22],[2018-'+cast(@j as varchar)+'-23],[2018-'+cast(@j as varchar)+'-24]
,[2018-'+cast(@j as varchar)+'-25],[2018-'+cast(@j as varchar)+'-26],[2018-'+cast(@j as varchar)+'-27],[2018-'+cast(@j as varchar)+'-28]
,[2018-'+cast(@j as varchar)+'-29],[2018-'+cast(@j as varchar)+'-30],[2018-'+cast(@j as varchar)+'-31]
) '
set @s =@s+') as MNamePivot'
print @s
Exec(@s)
-----------------------------------------To Create and test linked server connection----------------------------------------------------
EXEC sp_addlinkedserver
@server=N'TPCDB1\TPCSQL', -- Remote Computer Name
@srvproduct=N'', -- Not Needed
@provider=N'SQLNCLI', -- SQL Server Driver
@datasrc=N'TPCDB1\TPCSQL'; -- Server Name And Instance
declare @srvr nvarchar(128), @retval int;
set @srvr = '192.168.231.108';
begin try
exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
set @retval = sign(@@error);
end catch;
if @retval <> 0
raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );
------------------------------------------To see MSDB table size info-----------------------------
USE msdb
GO
SELECT TOP(10)
o.[object_id]
, obj = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
, o.[type]
, i.total_rows
, i.total_size
FROM sys.objects o
JOIN (
SELECT
i.[object_id]
, total_size = CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(18,2))
, total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END)
FROM sys.indexes i
JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
WHERE i.is_disabled = 0
AND i.is_hypothetical = 0
GROUP BY i.[object_id]
) i ON o.[object_id] = i.[object_id]
WHERE o.[type] IN ('V', 'U', 'S')
ORDER BY i.total_size DESC
------------------------------------------------- Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */
SELECT 'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context';
-- Role Members
SELECT 'EXEC sp_addsrvrolemember @rolename =' + SPACE(1) + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1) + QUOTENAME(usr2.name, '''') AS '--Role Memberships'
FROM sys.server_principals AS usr1
INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
ORDER BY rm.role_principal_id ASC;
-- Permissions
SELECT server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']' AS '--Server Level Permissions'
FROM sys.server_permissions AS server_permissions WITH (NOLOCK)
INNER JOIN sys.server_principals AS server_principals WITH (NOLOCK) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE server_principals.type IN ('S', 'U', 'G')
ORDER BY server_principals.name ,
server_permissions.state_desc ,
server_permissions.permission_name;
------------------------------sp_help_revlogin-------
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
--EXEC sp_help_revlogin
-------------------------------------------------------------TSQL query to stop long running SQL Job and start it back---------------------------
--if elapsed time is actually needed, uncomment
DECLARE @elapsed_minutes_max int
SET @elapsed_minutes_max = 240 --4 hours, adjust as needed
IF OBJECT_ID('tempdb.dbo.#jobactivity') IS NOT NULL
DROP TABLE #jobactivity
CREATE TABLE #jobactivity (
session_id int NULL,
job_id uniqueidentifier NULL,
job_name sysname NULL,
run_requested_date datetime NULL,
run_requested_source sysname NULL, --1=via schedule; 2=via alert; 3=via startup; 4=via user; 6=via CPU idle.
queued_date datetime NULL,
start_execution_date datetime NULL,
last_executed_step_id int NULL,
last_executed_step_date datetime NULL,
stop_execution_date datetime NULL,
next_scheduled_run_date datetime NULL,
job_history_id int NULL,
message nvarchar(1024) NULL,
run_status int NULL,
operator_id_emailed int NULL,
operator_id_netsent int NULL,
operator_id_paged int NULL
)
INSERT INTO #jobactivity
EXEC msdb.dbo.sp_help_jobactivity
IF EXISTS(
SELECT *
FROM #jobactivity ja
WHERE
ja.start_execution_date IS NOT NULL AND
ja.stop_execution_date IS NOT NULL AND
DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) >= @elapsed_minutes_max
AND ja.job_name = N'Order Planning2')
BEGIN
EXEC msdb.dbo.sp_stop_job N'Order Planning2'
print 'stop it'
END
ELSE
BEGIN
PRINT 'No JOB FOUND'
END
drop table #jobactivity
---------------------------------------Shrink TEMPDB without restart(last try before restart)--------------------------------------------
CHECKPOINT
GO
DBCC FREEPROCCACHE
GO
DBCC SHRINKFILE (TEMPDEV, 1024)
GO
-----------------------------------------TSQL code to find table with backedup with date----------------------
sp_tables '%_[1-30][1-12][1991-2020]%'
--------------------------------------General Guidelines to improve query performance-------------
Table should have primary key
Table should have minimum of one clustered index
Table should have appropriate amount of non-clustered index
Non-clustered index should be created on columns of table based on query which is running
Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
Do not to use Views or replace views with original source table
Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
Remove any adhoc queries and use Stored Procedure instead
Check if there is atleast 30% HHD is empty – it improves the performance a bit
If possible move the logic of UDF to SP as well
Remove * from SELECT and use columns which are only necessary in code
Remove any unnecessary joins from table
If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)
-------------- Disable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
----------------------- Enable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
---------To find column name of collation
Select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'col_test
----Change Collation of a SQL Server Table Column---------------------
USE databasename
GO
ALTER TABLE [dbo].[col_test]
ALTER COLUMN Text NVARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL
------------------------------Change collation of database----------------------------
USE master;
GO
ALTER DATABASE dbname
COLLATE French_CI_AS ;
GO
Note;
if you change the database collation you need to check whether column of table collation also changed or not. If not you need to change one by one use below script
USE databasename
GO
ALTER TABLE [dbo].[col_test]
ALTER COLUMN Text NVARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL
before change collation of column you need to verify following
You cannot change the collation of a column that is currently referenced by any one of the following:
A computed column
An index
Distribution statistics, either generated automatically or by the CREATE STATISTICS statement
A CHECK constraint
A FOREIGN KEY constraint
When you work with tempdb, the COLLATE clause includes a database_default option to specify that a column in a temporary table uses the collation default of the current user database for the connection instead of the collation of tempdb.
see for more
https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-column-collation?view=sql-server-2017
---------------------Verify the collation setting for database
SELECT name, collation_name
FROM sys.databases
WHERE name = N'dbname';
------------------------------------------------------------------------------------
ALTER DATABASE [msdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [msdb] SET DISABLE_BROKER
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;
ALTER DATABASE [msdb] SET ENABLE_BROKER
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;
ALTER DATABASE [msdb] SET MULTI_USER
EXEC msdb.dbo.sysmail_stop_sp;
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_start_sp;
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
-------------------------table with rowcount and size------------------
SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB ,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
--WHERE
-- t.is_ms_shipped = 0
-- AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
UsedSpaceMB DESC, t.Name
------------------------------How much Memory SQL DB used----------------------------------------------
SELECT TOP 10 DB_NAME(database_id) Database_Name,
COUNT (*) * 8 / 1024 AS MBUsed,COUNT (*) * 8 / 1024 /1024 GBused
FROM sys.dm_os_buffer_descriptors
where DB_NAME(database_id) is not null
GROUP BY database_id
ORDER BY COUNT (*) * 8 / 1024 DESC
---------------------------------------------index fragment based on conditions-----
Depending on fragmentation index level appropriated action will be taken (no action, rebuild or reorganize)
If average fragmentation is less than 10% no action will be taken, if average fragmentation is between 10% and 30% index will be reorganized, finally if index average fragmentation is greater than 30% index will be rebuilt.
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @dbid smallint;
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SET @dbid = DB_ID();
SELECT
[object_id] AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag, page_count
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 -- Allow limited fragmentation
AND index_id > 0 -- Ignore heaps
AND page_count > 25; -- Ignore small tables
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
-----------------------------------------------------------------------------------------------
Phases of recovery(SQL Server)
the recovery algorithm has 3 phases based around the last checkpoint in the transaction log.
Phase 1: Analysis. Starts at the last checkpoint in transaction log. This pass determines and constructs a dirty page table (DPT) consisting of pages that might be dirty at the time SQL Server stopped. An active transaction table is built of the uncommitted transactions at the time of the SQL Server stopped also.
Phase 2: Redo. This phase returns the database to the state at the time the SQL service stopped. Starting point for this forward passbeing the oldest uncommitted transaction. The mininum Log Sequence name (each log record is labelled with an LSN) in the DPT is the first time SQL Server expects to have to redo an operation on a page, redoing the logged operations starting right back at the oldest open transaction so that the neccessary locks can be aquired.
Phase 3: Undo: Here the list of active transaction (uncommitted at the time SQL Server stoopped) which where indentified in Phase 1 are rolled back individually. SQL Server follows the links between entries in the transaction log for each transaction. Any transaction that was not committed at the time SQL Server stopped is undone.
Recovery can be done when you restore the database, but it is also done at the startup of the database (crash recovery).
---------------------------------------------------------VLF and log file Info------------------------------
Understanding the VLF(Virtual Log File)
A database can have one or more log file. In general there will be only one log file as there is no performance improvement by having multiple log file. SQL server uses the transaction log in sequential manner.As the data file divided into pages,log files are divided into virtual log file(VLF).The size of the VLFs in a log file may not be in equal size. SQL server decide the size and number of VLF in a log file based on the size of the log file growth as given below.
Growth upto 64 MB = 4 VLF
From 64 MB to 1 GB = 8 VLF
Larger than 1 GB = 16 VLF
dbcc loginfo--------VLF info
dbcc sqlperf(logspace)-------------
ref:
http://www.sqlservercentral.com/blogs/practicalsqldba/2013/09/30/sql-server-part-1-architecture-of-transaction-log/
--From SQL 2017 To know VLF Count below DMV can be used
SELECT [name], s.database_id,
COUNT(l.database_id) AS 'VLF Count',
SUM(vlf_size_mb) AS 'VLF Size (MB)',
SUM(CAST(vlf_active AS INT)) AS 'Active VLF',
SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',
COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF',
SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name], s.database_id
ORDER BY 'VLF Count' DESC
GO
-----------------------------Change collation of SQL Server---------------
To rebuild system databases for an instance of SQL Server:
Insert the SQL Server 2014 installation media into the disk drive, or, from a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Release.
From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter the brackets. When using a Windows operating system that has User Account Control (UAC) enabled, running Setup requires elevated privileges. The command prompt must be run as Administrator.
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=SHELL2_P2\Administrator /SAPWD= reset$123 /SQLCOLLATION=SQL_Latin1_General_CP850_BIN
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=SHELL2_P1\Administrator /SAPWD= reset$123 /SQLCOLLATION=SQL_Latin1_General_CP850_BIN
https://docs.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-2014
https://www.sanssql.com/2012/10/how-to-change-server-collation.html
https://www.sanssql.com/2013/10/change-sql-server-collation-without.html
-------------------------------------------Permuations and Combinations----------------------------------
--option 1
Create table #pk(col1 varchar(100),col2 varchar(100),primary key(col1,col2))
declare @t1 table (col1 varchar(100))
insert @t1
select 'Data1' UNION
select 'Data2' UNION
select 'Data3' UNION
select 'Data4' UNION
select 'Data5'
Declare @t2 table (col2 varchar(100))
insert @t2
select 'Relation1' UNION
select 'Relation2' UNION
select 'Relation3' UNION
select 'Relation4' UNION
select 'Relation5'UNION
select 'Relation6'
;with cteAllColumns as (
select col1 as col
from @t1
union
select col2 as col
from @t2
)
insert into #pk
select c1.col , c2.col
from cteAllColumns c1
cross join cteAllColumns c2
where c1.col < c2.col
order by c1.col
select * from #pk
--option 2
--Create table #pk(col1 varchar(4000),col2 varchar(4000),primary key(col1,col2))
DECLARE @ConcatString1 VARCHAR(4000)
SELECT top 11 @ConcatString1 = COALESCE(@ConcatString1 + ', ', '') + name
FROM master.dbo.sysdatabases
DECLARE @ConcatString2 VARCHAR(4000)
DECLARE @tags NVARCHAR(4000) = 'Data1,Data2,Data3,Data4,Data5,Data6,Data7,Data8,Data9,Data10,Data11'
SELECT @ConcatString2 = COALESCE(@ConcatString2 + ', ', '') +value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';
Create table #CSV1(Data1 VARCHAR(4000))
Insert into #CSV1
Values(@ConcatString1)
Create table #CSV2(Data2 VARCHAR(4000))
Insert into #CSV2
Values(@ConcatString2)
select * from #CSV1
select * from #CSV2
--drop table #CSV1
--drop table #CSV2
SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
into #C1
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Data1, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM #CSV1) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
into #C2
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Data2, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM #CSV2) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
Select * from #C1
Select * from #C2
declare @t1 table (Info1 varchar(8000),col1 varchar(8000))
insert into @t1
Select * FROM #CSV1
CROSS APPLY STRING_SPLIT(Data1, ',');
declare @t2 table (Info2 varchar(8000),col2 varchar(8000))
insert into @t2
Select * FROM #CSV2
CROSS APPLY STRING_SPLIT(Data2, ',');
Select * from @t1
Select * from @t2
;with cteAllColumns as (
select col1 as col
from @t1
union
select col2 as col
from @t2
)
select c1.col, c2.col
from cteAllColumns c1
cross join cteAllColumns c2
where c1.col < c2.col
order by c1.col
--select * from #pk
drop table #CSV1
drop table #CSV2
--drop table #pk
drop table #C1
drop table #C2
--option 3
DECLARE @ConcatString VARCHAR(4000)
SELECT top 11 @ConcatString = COALESCE(@ConcatString + ', ', '') + name
FROM master.dbo.sysdatabases
DECLARE @ConcatString1 VARCHAR(4000)
SELECT top 11 @ConcatString1 = COALESCE(@ConcatString1 + ', ', '') + name
FROM ts4.master.dbo.sysdatabases
where name not in ('Data1', 'Data2', 'Data3', 'Data4', 'Data5', 'Data6', 'Data7', 'Data8', 'Data9', 'Data10', 'Data11')
Create table #CSV(Data VARCHAR(4000))
Insert into #CSV
Values(@ConcatString)
Insert into #CSV
Values(@ConcatString1)
select * from #CSV
--drop table #CSV
--select parsename(Data,4)as [4],parsename(Data,3)as [3],parsename(Data,2)as [2],parsename(Data,1)as [1] from
--(Select replace(Data,',','.') Data from #CSV) t
SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name1
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name5
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name1
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name5
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Data, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM #CSV) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
drop table #CSV
-super query-------------------
declare @numrows int
declare @numrows2 int
declare @numrows3 int
declare @numrows4 int
set @numrows=1
set @numrows2=4
set @numrows3=2
set @numrows4=4
create table #c1(Id int,Name varchar(300),Value int)
declare @vSQL varchar(1000)
--select @numrows = 5
select @vSQL = 'select top ' + convert(varchar, @numrows) + ' * from t11 Order by Id'
insert into #c1
Execute (@vSQL)
update #c1 set Name=Name+'('+'WK'+')'
create table #c2(Id int,Name varchar(300),Value int)
declare @vSQL2 varchar(1000)
--select @numrows2 = 4
select @vSQL2 = 'select top ' + convert(varchar, @numrows2) + ' * from t12 Order by Id'
insert into #c2
Execute (@vSQL2)
update #c2 set Name=Name+'('+'BT'+')'
create table #c3(Id int,Name varchar(300),Value int)
declare @vSQL3 varchar(1000)
--select @numrows2 = 4
select @vSQL3 = 'select top ' + convert(varchar, @numrows3) + ' * from t13 Order by Id'
insert into #c3
Execute (@vSQL3)
update #c3 set Name=Name+'('+'ALL'+')'
create table #c4(Id int,Name varchar(300),Value int)
declare @vSQL4 varchar(1000)
--select @numrows = 5
select @vSQL4 = 'select top ' + convert(varchar, @numrows4) + ' * from t14 Order by Id'
insert into #c4
Execute (@vSQL4)
update #c4 set Name=Name+'('+'BL'+')'
;with cteAllColumns as (
select Name as col
from #c1
union
select Name as col
from #c2
union
select Name as col
from #c3
union
select Name as col
from #c4
)
select *
into #fmtb4
from cteAllColumns cte1
order by col desc
;with cteAllColumns as (
select Name as col
from #c1
union
select Name as col
from #c2
union
select Name as col
from #c3
union
select Name as col
from #c4
)
select cte1.col col1,cte2.col col2
into #fmtbl
from cteAllColumns cte1
cross join cteAllColumns cte2
where cte1.col < cte2.col
order by cte1.col
Declare @Column1 Nvarchar(300)
Declare @Column2 Nvarchar(300)
Declare @Column1After Nvarchar(300)
Declare @Column2After Nvarchar(300)
DECLARE CC CURSOR FOR
SELECT DISTINCT col1,col2
FROM #fmtbl
OPEN CC
FETCH NEXT FROM CC INTO @Column1,@Column2
WHILE @@FETCH_STATUS = 0
BEGIN
set @Column1After=@Column1+'['+'C'+']'
set @Column2After=@Column2+'['+'VC'+']'
update #fmtbl set col1=@Column1After where col1=@Column1 and col1<>@Column1+'['+'C'+']'
update #fmtbl set col2=@Column2After where col2=@Column2 and col1<>@Column2+'['+'VC'+']'
FETCH NEXT FROM CC INTO @Column1,@Column2
END
--select ''[afterfmtb4],* from #fmtbl
--order by col1,col2
SELECT Distinct top 2
stuff(
(
SELECT ','+ col FROM #fmtb4 FOR XML PATH('')
),1,1,'') Results
into #commatbl
FROM (SELECT DISTINCT top 1 col FROM #fmtb4 ) #fmtb4
CLOSE CC
DEALLOCATE CC
select col1,col2,Results
Into #CP
FROM #fmtbl P CROSS Join
#commatbl C
Select ''BEFORE,* from #CP
--update #CP set Results = replace(Results,substring(Results,charindex(col1,(substring(Results,charindex(col1,(Results)),charindex(',',Results))+'['+'C'+']')),charindex(',',Results)-1),col1)
update #CP set Results = Stuff(Results,charindex(Stuff(col1,charindex('[',col1),len(right(col1,3)),''),(Results)),len(Stuff(col1,charindex('[',col1),len(right(col1,3)),'')),col1)
update #CP set Results = Stuff(Results,charindex(Stuff(col2,charindex('[',col2),len(right(col2,4)),''),(Results)),len(Stuff(col2,charindex('[',col2),len(right(col2,4)),'')),col2)
Select 'AFTER'AFTER,* from #CP
Order by col1,col2
SELECT DISTINCT col1,col2,
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
--into #C1
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Results, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM #CP) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
drop table #c1
drop table #c2
drop table #c3
drop table #c4
drop table #fmtbl
drop table #fmtb4
drop table #CP
drop table #commatbl
use master
SELECT 'Currently Running Query'as RunningQuery,DATEDIFF(MINUTE, A.last_batch, GETDATE()) RunningTime,
A.spid, A.blocked, A.waittime, db_name(A.dbid) As dbname,B.text as sqlstatement,
A.hostname, A.loginame,A.program_name,A.last_batch,A.nt_username
FROM sys.sysprocesses A CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) B
WHERE A.dbid > 4
---------------------------------Batch requests/sec
DECLARE @BRPS BIGINT
SELECT @BRPS=cntr_value
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Batch Requests/sec%'
WAITFOR DELAY '000:00:01'
SELECT @@SERVERNAME AS ServerName,(cntr_value-@BRPS)/10.0 AS "Batch Requests/sec"
FROM sys.dm_os_performance_counters
WHERE counter_name LIKE 'Batch Requests/sec%'
-------------------------------------find most consumed objects/statements info------------------------------
SELECT TOP 10 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
--ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
ORDER BY qs.total_worker_time DESC -- CPU time
----------------------------------------------find most consumed stored procedure info
SELECT CASE WHEN database_id = 32767 then 'Resource' ELSE DB_NAME(database_id)END AS DBName
,OBJECT_SCHEMA_NAME(object_id,database_id) AS [SCHEMA_NAME]
,OBJECT_NAME(object_id,database_id)AS [OBJECT_NAME]
,cached_time
,last_execution_time
,execution_count
,total_worker_time / execution_count AS AVG_CPU
,total_elapsed_time / execution_count AS AVG_ELAPSED
,total_logical_reads / execution_count AS AVG_LOGICAL_READS
,total_logical_writes / execution_count AS AVG_LOGICAL_WRITES
,total_physical_reads / execution_count AS AVG_PHYSICAL_READS
FROM sys.dm_exec_procedure_stats
where database_id<>32767 and DB_NAME(database_id) not in ('master','msdb')
ORDER BY AVG_LOGICAL_READS DESC
----------------------------------------------------------Table Row count----------
SELECT @@servername as servername,DB_NAME() AS [Database Name],T.name AS [TABLE NAME],
I.row_count AS [ROWCOUNT]
FROM sys.tables AS T
INNER JOIN sys.dm_db_partition_stats AS I
ON T.object_id = I.object_id
AND I.index_id < 2
ORDER BY I.row_count DESC
-------------------------------------------blocking info with dbcc inputbuffer
select distinct top 1 @id=spid from sys.sysprocesses where blocked<>0
set @x=@id
if @x>0
begin
select @x as blockingspid
dbcc inputbuffer(@x)
end
else
select 'nothing blocked' as blockingstatus
---------------------------------------Page life expectancy
SELECT @@SERVERNAME AS ServerName,[object_name],
[counter_name],
[cntr_value] FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Manager%'
AND [counter_name] = 'Page life expectancy'
Page Life Expectancy is the number of seconds a page will stay in the buffer pool without references.
check Page Life Expectancy. If its value is low (below 300 seconds), this is a clear indication of memory pressure.
--------------------------------------Lazy writer------------
SELECT object_name, counter_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%'
AND [counter_name] = 'Lazy writes/sec'
The lazy writer is a process that periodically checks the available free space in the buffer cache between two checkpoints and ensures that there is always enough free memory.
When the lazy writer determines free pages are needed in the buffer for better performance, it removes the old pages before the regular checkpoint occurs
If a dirty data page (a page read and/or modified) in the buffer hasn’t been used for a while, the lazy writer flushes it to disk and then marks as free in the buffer cache
The Lazy writes metric is defined as "Number of times per second SQL Server relocates dirty pages from buffer pool (memory) to disk"
The threshold value for Lazy Writes is 20
If the Lazy Writes value is constantly higher than 20, to be sure that the server is under memory pressure, check Page Life Expectancy. If its value is low (below 300 seconds), this is a clear indication of memory pressure.
------------------------------check point--------------------------------------------------------------------------------------------------------------------------------------------------------
A checkpoint writes the current in-memory modified pages (known as dirty pages) and transaction log information from memory to disk and, also, records information about the transaction
at a check point, which can be automatic (occurs automatically to meet the recovery interval request) , indirect (occurs automatically to meet the database target recovery time),
manual (occurs when the CHECKPOINT command is executed), and internal (occurs along with some server-level operations, such as backup creation)
--------------------------------------------------------Latch--------------------------
a latch is a lightweight synchronization object used by the Storage Engine to protect memory structures used internally by SQL Server.
3 Different types of Latches:
IO Latches
Buffer Latches (BUF)
Non-Buffer Latches (Non-BUF)
Latch Modes
Keep
Shared
Update
Exclusive
Destroy
-------------------------------Free list stalls/sec---------------------------
SELECT 'Free list stalls/sec',
cntr_value
FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE '%Buffer Manager%' and counter_name LIKE '%Free list stalls/sec%'
----------------------------------------find number of core is SQL using-----------
select scheduler_id, cpu_id, status, is_online
from sys.dm_os_schedulers
where status = 'VISIBLE ONLINE'
------------------------------------To find out Unused DB info------------
SELECT @@ServerName AS server
,NAME AS dbname
,COUNT(STATUS) AS number_of_connections
,GETDATE() AS queryexecutedtimestamp
FROM sys.databases sd
LEFT JOIN sysprocesses sp ON sd.database_id = sp.dbid
WHERE database_id NOT BETWEEN 1 AND 4
GROUP BY NAME
SELECT TOP 10000 s.[database_id]
,d.name
,d.create_date
,s.[index_id]
,s.[object_id]
,s.[user_seeks]
,s.[user_scans]
,s.[user_lookups]
,s.[user_updates]
,s.[last_user_seek]
,s.[last_user_scan]
,s.[last_user_lookup]
,s.[last_user_update]
FROM [master].[sys].[dm_db_index_usage_stats] as s right outer join sys.databases d on s.database_id = d.database_id
where d.database_id > 4
order by d.name
select d.name, x1 =
(select X1= max(bb.xx)
from (
select xx = max(last_user_seek)
where max(last_user_seek) is not null
union all
select xx = max(last_user_scan)
where max(last_user_scan) is not null
union all
select xx = max(last_user_lookup)
where max(last_user_lookup) is not null
union all
select xx = max(last_user_update)
where max(last_user_update) is not null) bb)
FROM master.dbo.sysdatabases d
left outer join
sys.dm_db_index_usage_stats s
on d.dbid= s.database_id
where d.dbid > 4
group by d.name
-----------------------DMV query that will tell us high level information of disk latency
SELECT
*
,wait_time_ms/waiting_tasks_count AS 'Avg Wait in ms',(wait_time_ms/waiting_tasks_count)/1000 as 'AVG Wait in Sec',
(wait_time_ms/waiting_tasks_count)/1000/60 as 'AVG Wait in Mint'
FROM
sys.dm_os_wait_stats
WHERE
waiting_tasks_count > 0
ORDER BY
wait_time_ms DESC
----------------------------------------Number of user connections info ---------------
SELECT ConnectionStatus = CASE WHEN dec.most_recent_sql_handle = 0x0
THEN 'Unused'
ELSE 'Used'
END
, CASE WHEN des.status = 'Sleeping'
THEN 'sleeping'
ELSE 'Not Sleeping'
END
, ConnectionCount = COUNT(1)
FROM sys.dm_exec_connections dec
INNER JOIN sys.dm_exec_sessions des ON dec.session_id = des.session_id
GROUP BY CASE WHEN des.status = 'Sleeping'
THEN 'sleeping'
ELSE 'Not Sleeping'
END
, CASE WHEN dec.most_recent_sql_handle = 0x0
THEN 'Unused'
ELSE 'Used'
END;
---------------------------------------------Adding tempdb files based on core/processor-------------------------
Select @@servername as servername,DB_NAME(mf.database_id) database_name
, mf.name logical_name, mf.file_id
, CONVERT (DECIMAL (20,2)
, (CONVERT(DECIMAL, size)/128)) as [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(DECIMAL(20,2)
,(((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN CONVERT(DECIMAL(20,2)
, (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, physical_name from sys.master_files mf
where database_id =2 and type_desc= 'rows'
SELECT @@servername as servername,cpu_count AS logicalCPUs FROM sys.dm_os_sys_info
--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev2.ndf' , SIZE =8MB , FILEGROWTH = 5MB) --<<--Update the data file location/Size/AutoGrowth
--GO
--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev3',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev3.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO
--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev4',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev4.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO
--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev5',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev5.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO
--ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev6',
--FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\tempdev6.ndf' , SIZE =8MB , FILEGROWTH = 5MB)--<<--Update the data file location/Size/AutoGrowth
--GO
---ETC, add files as per processors count
------------------------------------------Enable all Agent Jobs-----------------
SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N'''
+ cast(job_id as varchar(40)) + ''', @enabled=1'
FROM msdb..DBA_Agent_jobs_Snapshot
--Run output from above
------------------------------------------Disable all Agent Jobs
Select * into msdb..DBA_Agent_jobs_Snapshot
FROM msdb..sysjobs
WHERE ENABLED = 1
SELECT 'EXEC msdb.dbo.sp_update_job @job_id=N'''
+ cast(job_id as varchar(40)) + ''', @enabled=0'
FROM msdb..DBA_Agent_jobs_Snapshot
--Run output from above
-----------------------------------Kill the unused session on db bulk------------------------------------------
ALTER DATABASE teis_wfl SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE teis_wfl SET MULTI_USER
----------------------------------------------------------------DB Mail for Backup status report---------------------------------
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @SER NVARCHAR(20);
DECLARE @StrSubject VARCHAR(100);
SET @SER=@@SERVERNAME
SELECT @StrSubject = 'Texmo DB Server Backup Status Report ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'
SET @tableHTML =
N'<H1>Database Backup Status Report</H1>' +
N'<table border="1">' +
N'<tr><th>Server_Name</th><th>Database_Name</th><th>DaysSinceLastBackup</th>' +
N'<th>Last FullBackup Date & time</th><th>Backup_Status_Today</th><th>DB_Status</th></tr>' +
CAST ( ( SELECT td = @SER, '',td = B.name, '',
td = ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER'), '',
td =ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER'), '',
td = case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(MST.backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(MST.backup_finish_date)))), 'NEVER')=0 then 'Completed'
else 'Not completed' end,d.state_desc
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
join sys.databases d ON B.name=d.name
where B.name not in ('tempdb')
GROUP BY B.name,B.status,d.state_desc
ORDER BY B.name
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
PRINT @tableHTML
EXEC msdb.dbo.sp_send_dbmail @profile_name='Database Administrator',
@recipients = 'JAR@texmo.net;Shalvansha.Razak@texmocastings.com;RSK@texmo.net',
--@recipients = 'JAR@texmo.net',
@subject = @StrSubject,
@body = @tableHTML,
@body_format = 'HTML' ;
DECLARE @tableHTML NVARCHAR(MAX) ;
DECLARE @SER NVARCHAR(20);
DECLARE @StrSubject VARCHAR(100);
SET @SER=@@SERVERNAME
SELECT @StrSubject = 'Texmo DB Server Backup Status Report ('+ CONVERT(VARCHAR(50), SERVERPROPERTY('servername')) + ')'
SET @tableHTML =
N'<H1>Database Backup Status Report</H1>' +
N'<table border="1">' +
N'<tr><th>Server_Name</th><th>Database_Name</th><th>DaysSinceLastBackup</th>' +
N'<th>Last FullBackup Date & time</th><th>Backup_Status_Today</th><th>DB_Status</th><th>Backup_type</th><th>Backup Size in MB</th><th>Backup Size in GB</th></tr>' +
CAST ( ( SELECT td = @SER, '',td = LTRIM(RTRIM(B.name)), '',
td = ISNULL(STR(ABS(DATEDIFF(day, GetDate(),MAX(backup_finish_date)))), 'NEVER'), '',
td =ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER'), '',
td = case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(MST.backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(MST.backup_finish_date)))), 'NEVER')=0 then 'Completed'
else 'Not completed' end, '',td=d.state_desc, '',
td= CASE MST.type
WHEN 'D' THEN 'Full Database'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Differential'
WHEN 'F' THEN 'File Level'
WHEN 'G' THEN 'File Level Differential'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END, '',
td= LTRIM(RTRIM(convert(varchar,cast(MST.backup_size/1024/1024 as money),10))), '',td= LTRIM(RTRIM(convert(varchar,cast(MST.backup_size/1024/1024 as INT),10)))/1024, ''
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
join sys.databases d ON B.name=d.name
where B.name not in ('tempdb')
and (CONVERT(datetime, MST.backup_start_date, 102) >= GETDATE() - 1)
GROUP BY B.name,B.status,d.state_desc,MST.type,convert(varchar,cast(MST.backup_size/1024/1024 as INT),10),convert(varchar,cast(MST.backup_size/1024/1024 as money),10)
ORDER BY B.name,B.status,d.state_desc,MST.type,convert(varchar,cast(MST.backup_size/1024/1024 as INT),10),convert(varchar,cast(MST.backup_size/1024/1024 as money),10)
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
PRINT @tableHTML
EXEC msdb.dbo.sp_send_dbmail @profile_name='Database Administrator',
--@recipients = 'JAR@texmo.net;Shalvansha.Razak@texmocastings.com;RSK@texmo.net',
@recipients = 'JAR@texmo.net',
@subject = @StrSubject,
@body = @tableHTML,
@body_format = 'HTML' ;
----------------------------------------------------Backup status query----------------------------------------------------
SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER') as 'Last FullBackup Date & time',case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER')=0 then 'Completed'
else 'Not completed' end as BackupStatus
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
where B.name not in ('tempdb')
GROUP BY B.name,B.status
ORDER BY B.name
SELECT @@SERVERNAME AS ServerName,B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(varchar(20), MAX(backup_finish_date), 120), 'NEVER') as 'Last FullBackup Date & time',case when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER')='NEVER' then 'Not Completed'
when ISNULL(STR(ABS(DATEDIFF(day, GetDate(),
MAX(backup_finish_date)))), 'NEVER')=0 then 'Completed'
else 'Not completed' end as Backup_Status_Today,d.state_desc,
CASE MST.type
WHEN 'D' THEN 'Full Database'
WHEN 'L' THEN 'Log'
WHEN 'I' THEN 'Differential'
WHEN 'F' THEN 'File Level'
WHEN 'G' THEN 'File Level Differential'
WHEN 'P' THEN 'Partial'
WHEN 'Q' THEN 'Differential partial'
END AS backup_type,
convert(varchar,cast(MST.backup_size/1024/1024 as money),10) as 'Backup Size in MB',convert(varchar,cast(MST.backup_size/1024/1024 as INT),10) /1024 as 'Backup Size in GB'
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset MST
ON MST.database_name = B.name AND MST.type = 'D'
join sys.databases d ON B.name=d.name
--------------------------------------Databases not backed up within 24 hrs----------
DECLARE
@dblist NVARCHAR(MAX) = N'',
@c CHAR(2) = CHAR(13) + CHAR(10), @t CHAR(1) = CHAR(9),
@s SYSNAME = @@SERVERNAME;
SELECT @dblist += @c + @c + @s + @c + @t + db + @c + @t
+ COALESCE(CONVERT(CHAR(10), d, 120) + ' ' + CONVERT(CHAR(8), d, 108), 'NULL')
+ @c + @t + CONVERT(VARCHAR(11), age)
FROM
(
SELECT
bs.database_name AS db,
MAX(bs.backup_finish_date) AS d,
DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS age
-------------^^^^ please don't use lazy shorthand like hh
FROM msdb.dbo.backupset AS bs
WHERE [type] = 'D'
GROUP BY database_name
HAVING (MAX(backup_finish_date) < DATEADD(HOUR, -24, GETDATE()))
UNION ALL -- why UNION? By definition there are no duplicates to filter.
-- in fact you could re-write this without two separate queries at all.
SELECT
name AS db,
NULL AS d,
9999 AS age
FROM
master.sys.databases AS d
------ don't use sysdatabases - old and deprecated
WHERE name <> N'tempdb' AND NOT EXISTS
(
SELECT 1 FROM msdb.dbo.backupset
WHERE database_name = d.name
)
) AS x
ORDER BY db;
IF @dblist > N''
BEGIN
PRINT @dblist;
--EXEC msdb.dbo.sp_send_dbmail
-- @recipients = 'dba@someemailaddress.com',
-- @subject = N'Databases not backed up within 24 hrs',
-- @body = @dblist;
END
----------------------------------------------Missing index Bulk-------------------------
EXEC sp_MSforeachdb 'USE ? SELECT ''?''dbname,dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
dm_migs.last_user_seek AS Last_User_Seek,
OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
''CREATE INDEX [IX_'' + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + ''_''
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns,''''),'', '',''_''),''['',''''),'']'','''')
+ CASE
WHEN dm_mid.equality_columns IS NOT NULL
AND dm_mid.inequality_columns IS NOT NULL THEN ''_''
ELSE ''''
END
+ REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns,''''),'', '',''_''),''['',''''),'']'','''')
+ '']''
+ '' ON '' + dm_mid.statement
+ '' ('' + ISNULL (dm_mid.equality_columns,'''')
+ CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns
IS NOT NULL THEN '','' ELSE
'''' END
+ ISNULL (dm_mid.inequality_columns, '''')
+ '')''
+ ISNULL ('' INCLUDE ('' + dm_mid.included_columns + '')'', '''') AS Create_Statement
FROM sys.dm_db_missing_index_groups dm_mig
INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
ON dm_migs.group_handle = dm_mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details dm_mid
ON dm_mig.index_handle = dm_mid.index_handle
WHERE dm_mid.database_ID = DB_ID()
ORDER BY Avg_Estimated_Impact DESC '
------------------------------------To send DB offline status report via db mail------------------------------------
if(select count(*) from sys.databases where state_desc<>'Online')>0
Begin
DECLARE @table NVARCHAR(MAX) ;
SET @table =
N'<H1>Offline Databases Report</H1>' +
N'<table border="1">' +
N'<tr><th>Database Name</th><th>Database Status</th></tr>' +
CAST ( ( Select td=name, '',td=state_desc from sys.databases where state_desc<>'Online'
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'</table>' ;
EXEC msdb.dbo.sp_send_dbmail @profile_name='ProfileName', --Change to your Profile Name
@recipients='email@domain.com;email1@domain.com', --Put the email address of those who want to receive the e-mail
@subject = 'Offline Databases Report',
@body = @table,
@body_format = 'HTML' ;
END
Else Print 'All Databases are Online'
-------------------------------------Index fragmentation report-----------------------
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,CASE
WHEN indexstats.avg_fragmentation_in_percent < 10
THEN 'NOTHING'
WHEN indexstats.avg_fragmentation_in_percent >= 10
AND indexstats.avg_fragmentation_in_percent < 30
THEN 'REORGANIZE'
WHEN indexstats.avg_fragmentation_in_percent >= 30
THEN 'REBUILD'
END as fragmentation_comments,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID() and indexstats.page_count>=1000 and indexstats.avg_fragmentation_in_percent>=10
ORDER BY indexstats.avg_fragmentation_in_percent desc
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.avg_fragmentation_in_percent,CASE
WHEN indexstats.avg_fragmentation_in_percent < 10
THEN 'NOTHING'
WHEN indexstats.avg_fragmentation_in_percent >= 10
AND indexstats.avg_fragmentation_in_percent < 30
THEN 'REORGANIZE'
WHEN indexstats.avg_fragmentation_in_percent >= 30
THEN 'REBUILD'
END as fragmentation_comments,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (14, NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = 14 and indexstats.page_count>=1000 and indexstats.avg_fragmentation_in_percent>=10
ORDER BY indexstats.avg_fragmentation_in_percent desc
Fragmentation is less than 10% – no de-fragmentation is required. It is generally accepted that in majority of environments index fragmentation less than 10% in negligible and its performance impact on the SQL Server is minimal.
Fragmentation is between 10-30% – it is suggested to perform index reorganization
Fragmentation is higher than 30% – it is suggested to perform index rebuild
-------------------------------------------------To view statistics status info against Database-------------------------
SELECT Distinct @@servername as Servername,db_name(db_id()) as DB_Name, SCHEMA_NAME(schema_id) SchemaName,t.name TableName, s.[name] StatName, STATS_DATE(t.object_id,s.[stats_id]) LastUpdated ,'['+SCHEMA_NAME(schema_id)+'].['+t.name+']' AS SchemaTable
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'
and convert(varchar(10), STATS_DATE(t.object_id,s.[stats_id]),121)<>'2018-11-15'
SELECT Distinct 'Update Statistics'+ '['+SCHEMA_NAME(schema_id)+'].['+t.name+']' AS SchemaTable
FROM sys.[stats] AS s
JOIN sys.[tables] AS t
ON [s].[object_id] = [t].[object_id]
WHERE t.type = 'u'
and convert(varchar(10), STATS_DATE(t.object_id,s.[stats_id]),121)<>'2018-11-15'
-------------------------------------------Recovery pending----------------------------
SELECT name, state_desc from sys.databases
where state_desc='RECOVERY_PENDING'
exec sp_resetstatus dbname
ALTER DATABASE dbname SET ONLINE
DBCC CHECKDB('dbname') WITH NO_INFOMSGS
ALTER DATABASE dbname SET SINGLE_USER
DBCC CHECKDB('dbname',REPAIR_REBUILD)
ALTER DATABASE dbname SET MULTI_USER
https://promx.net/en/2016/11/microsoft-sql-server-database-repair-following-recovery-pending-status/
-----------------------------------------Alter Query for move tempdb secondary files-----------------------
Transact-SQL
SELECT 'ALTER DATABASE tempdb MODIFY FILE (NAME = [' + f.name + '],'
+ ' FILENAME = ''Z:\MSSQL\DATA\' + f.name
+ CASE WHEN f.type = 1 THEN '.ldf' ELSE '.mdf' END
+ ''');'
FROM sys.master_files f
WHERE f.database_id = DB_ID(N'tempdb');
------------------------------------------------to check what job is running at a particular time----------------------
SELECT * FROM
(
SELECT JobName, RunStart, DATEADD(second, RunSeconds, RunStart) RunEnd, RunSeconds,RunSeconds/60 Mints,RunSeconds/60/60 hrs
FROM
(
SELECT j.name AS 'JobName',
msdb.dbo.agent_datetime(run_date, run_time) AS 'RunStart',
((jh.run_duration/1000000)*86400)
+ (((jh.run_duration-((jh.run_duration/1000000)*1000000))/10000)*3600)
+ (((jh.run_duration-((jh.run_duration/10000)*10000))/100)*60)
+ (jh.run_duration-(jh.run_duration/100)*100) RunSeconds
FROM msdb.dbo.sysjobs j
INNER JOIN msdb.dbo.sysjobhistory jh ON j.job_id = jh.job_id
WHERE jh.step_id=0 --The Summary Step
) AS H
) AS H2
WHERE '2018-10-22 03:30:00' BETWEEN RunStart AND RunEnd
ORDER BY JobName, RunEnd
------------------------------------------------Log reuse monitor---------------------------------------------
dbcc sqlperf(logspace)
Select @@SERVERNAME AS ServerName,name,log_reuse_wait_desc,recovery_model_desc,state_desc,* from sys.databases
where log_reuse_wait_desc<>'NOTHING'
SELECT instance_name
, [Data File(s) Size (KB)] * 1.0 / 1024 [Data File(s) Size (MB)]
, [Log File(s) Size (KB)] * 1.0 / 1024 [Log File(s) Size (MB)]
, [Log File(s) Used Size (KB)] * 1.0 / 1024 [Log File(s) Used Size (MB)]
, [Percent Log Used]
, log_reuse_wait_desc
FROM ( SELECT os.counter_name, os.instance_name, os.cntr_value
, db.log_reuse_wait_desc
FROM sys.dm_os_performance_counters os
JOIN sys.databases db ON os.instance_name = db.name
WHERE os.counter_name IN
(
'Data File(s) Size (KB)'
, 'Log File(s) Size (KB)'
, 'Log File(s) Used Size (KB)'
, 'Percent Log Used'
)
--AND os.instance_name = 'TLogTruncate'
) as SourceTable
PIVOT (
MAX(cntr_value) FOR counter_name IN
([Data File(s) Size (KB)]
, [Log File(s) Size (KB)]
, [Log File(s) Used Size (KB)]
, [Percent Log Used])) as PivotTable
--------------------------------------------To see DB growth for all DB-----------------------------------------------
SELECT 'Database Name' = DB_NAME(database_id)
,'FileName' = NAME
,FILE_ID
,'size' = CONVERT(NVARCHAR(15), CONVERT(BIGINT, size) * 8) + N' KB'
,'maxsize' = (
CASE max_size
WHEN - 1
THEN N'Unlimited'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, max_size) * 8) + N' KB'
END
)
,'growth' = (
CASE is_percent_growth
WHEN 1
THEN CONVERT(NVARCHAR(15), growth) + N'%'
ELSE CONVERT(NVARCHAR(15), CONVERT(BIGINT, growth) * 8) + N' KB'
END
)
,'type_desc' = type_desc
FROM sys.master_files
ORDER BY database_id
SELECT @@SERVERNAME as ServerName,*
FROM (SELECT [DBName],YEAR([Dateofobservation]) [Year],
convert(varchar(10),[Dateofobservation],121) [Date],
SUM([OccupiedinMB]) [OccupiedinMB]
FROM [db_admin].[dbo].[DBGrowth]
GROUP BY [DBName],YEAR([Dateofobservation]),
convert(varchar(10),[Dateofobservation],121)) AS MontlySalesData
PIVOT( SUM([OccupiedinMB])
FOR [Date] IN ( [2018-11-29],[2018-11-30],[2018-12-01],[2018-12-02],[2018-12-03],[2018-12-04],[2018-12-05], [2018-12-06],[2018-12-07],[2018-12-08],[2018-12-09],[2018-12-10],[2018-12-11]
,[2018-12-12],[2018-12-13],[2018-12-14],[2018-12-15],[2018-12-16],[2018-12-17],[2018-12-18],[2018-12-19],[2018-12-20],[2018-12-21],[2018-12-22],[2018-12-23],[2018-12-24]
,[2018-12-25],[2018-12-26],[2018-12-27],[2018-12-28],[2018-12-29],[2018-12-30],[2018-12-31]
)) AS MNamePivot
Declare @s varchar(max)=''
Declare @i int
Declare @j int
set @i=1
select @j=right(convert(varchar(7),getdate(),121),2)
select @j as j
set @s='SELECT @@SERVERNAME as ServerName,*'
set @s=@s +'FROM (SELECT [DBName],YEAR([Dateofobservation]) [Year],
convert(varchar(10),[Dateofobservation],121) [Date],
SUM([OccupiedinMB]) [OccupiedinMB]
FROM [db_admin].[dbo].[DBGrowth]
GROUP BY [DBName],YEAR([Dateofobservation]),
convert(varchar(10),[Dateofobservation],121)) AS MontlySalesData'
set @s=@s +' PIVOT( SUM([OccupiedinMB])
FOR [Date] IN ( '
set @s=@s+' [2018-'+cast(@j as varchar)+'-01],[2018-'+cast(@j as varchar)+'-02],[2018-'+cast(@j as varchar)+'-03],[2018-'+cast(@j as varchar)+'-04]
,[2018-'+cast(@j as varchar)+'-05],[2018-'+cast(@j as varchar)+'-06],[2018-'+cast(@j as varchar)+'-07],[2018-'+cast(@j as varchar)+'-08]
,[2018-'+cast(@j as varchar)+'-09],[2018-'+cast(@j as varchar)+'-10],[2018-'+cast(@j as varchar)+'-11],[2018-'+cast(@j as varchar)+'-12]
,[2018-'+cast(@j as varchar)+'-13],[2018-'+cast(@j as varchar)+'-14],[2018-'+cast(@j as varchar)+'-15],[2018-'+cast(@j as varchar)+'-16]
,[2018-'+cast(@j as varchar)+'-17],[2018-'+cast(@j as varchar)+'-18],[2018-'+cast(@j as varchar)+'-19],[2018-'+cast(@j as varchar)+'-20]
,[2018-'+cast(@j as varchar)+'-21],[2018-'+cast(@j as varchar)+'-22],[2018-'+cast(@j as varchar)+'-23],[2018-'+cast(@j as varchar)+'-24]
,[2018-'+cast(@j as varchar)+'-25],[2018-'+cast(@j as varchar)+'-26],[2018-'+cast(@j as varchar)+'-27],[2018-'+cast(@j as varchar)+'-28]
,[2018-'+cast(@j as varchar)+'-29],[2018-'+cast(@j as varchar)+'-30],[2018-'+cast(@j as varchar)+'-31]
) '
set @s =@s+') as MNamePivot'
print @s
Exec(@s)
-----------------------------------------To Create and test linked server connection----------------------------------------------------
EXEC sp_addlinkedserver
@server=N'TPCDB1\TPCSQL', -- Remote Computer Name
@srvproduct=N'', -- Not Needed
@provider=N'SQLNCLI', -- SQL Server Driver
@datasrc=N'TPCDB1\TPCSQL'; -- Server Name And Instance
declare @srvr nvarchar(128), @retval int;
set @srvr = '192.168.231.108';
begin try
exec @retval = sys.sp_testlinkedserver @srvr;
end try
begin catch
set @retval = sign(@@error);
end catch;
if @retval <> 0
raiserror('Unable to connect to server. This operation will be tried later!', 16, 2 );
------------------------------------------To see MSDB table size info-----------------------------
USE msdb
GO
SELECT TOP(10)
o.[object_id]
, obj = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
, o.[type]
, i.total_rows
, i.total_size
FROM sys.objects o
JOIN (
SELECT
i.[object_id]
, total_size = CAST(SUM(a.total_pages) * 8. / 1024 AS DECIMAL(18,2))
, total_rows = SUM(CASE WHEN i.index_id IN (0, 1) AND a.[type] = 1 THEN p.[rows] END)
FROM sys.indexes i
JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
WHERE i.is_disabled = 0
AND i.is_hypothetical = 0
GROUP BY i.[object_id]
) i ON o.[object_id] = i.[object_id]
WHERE o.[type] IN ('V', 'U', 'S')
ORDER BY i.total_size DESC
------------------------------------------------- Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */
SELECT 'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context';
-- Role Members
SELECT 'EXEC sp_addsrvrolemember @rolename =' + SPACE(1) + QUOTENAME(usr1.name, '''') + ', @loginame =' + SPACE(1) + QUOTENAME(usr2.name, '''') AS '--Role Memberships'
FROM sys.server_principals AS usr1
INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
ORDER BY rm.role_principal_id ASC;
-- Permissions
SELECT server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS + ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' TO [' + server_principals.name COLLATE SQL_Latin1_General_CP1_CI_AS + ']' AS '--Server Level Permissions'
FROM sys.server_permissions AS server_permissions WITH (NOLOCK)
INNER JOIN sys.server_principals AS server_principals WITH (NOLOCK) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE server_principals.type IN ('S', 'U', 'G')
ORDER BY server_principals.name ,
server_permissions.state_desc ,
server_permissions.permission_name;
------------------------------sp_help_revlogin-------
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@binvalue varbinary(256),
@hexvalue varchar (514) OUTPUT
AS
DECLARE @charvalue varchar (514)
DECLARE @i int
DECLARE @length int
DECLARE @hexstring char(16)
SELECT @charvalue = '0x'
SELECT @i = 1
SELECT @length = DATALENGTH (@binvalue)
SELECT @hexstring = '0123456789ABCDEF'
WHILE (@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int, SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint - (@firstint*16)
SELECT @charvalue = @charvalue +
SUBSTRING(@hexstring, @firstint+1, 1) +
SUBSTRING(@hexstring, @secondint+1, 1)
SELECT @i = @i + 1
END
SELECT @hexvalue = @charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @type varchar (1)
DECLARE @hasaccess int
DECLARE @denylogin int
DECLARE @is_disabled int
DECLARE @PWD_varbinary varbinary (256)
DECLARE @PWD_string varchar (514)
DECLARE @SID_varbinary varbinary (85)
DECLARE @SID_string varchar (514)
DECLARE @tmpstr varchar (1024)
DECLARE @is_policy_checked varchar (3)
DECLARE @is_expiration_checked varchar (3)
DECLARE @defaultdb sysname
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT p.sid, p.name, p.type, p.is_disabled, p.default_database_name, l.hasaccess, l.denylogin FROM
sys.server_principals p LEFT JOIN sys.syslogins l
ON ( l.name = p.name ) WHERE p.type IN ( 'S', 'G', 'U' ) AND p.name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin script '
PRINT @tmpstr
SET @tmpstr = '** Generated ' + CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@type IN ( 'G', 'U'))
BEGIN -- NT authenticated account/group
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' FROM WINDOWS WITH DEFAULT_DATABASE = [' + @defaultdb + ']'
END
ELSE BEGIN -- SQL Server authentication
-- obtain password and sid
SET @PWD_varbinary = CAST( LOGINPROPERTY( @name, 'PasswordHash' ) AS varbinary (256) )
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string OUT
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
-- obtain password policy state
SELECT @is_policy_checked = CASE is_policy_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SELECT @is_expiration_checked = CASE is_expiration_checked WHEN 1 THEN 'ON' WHEN 0 THEN 'OFF' ELSE NULL END FROM sys.sql_logins WHERE name = @name
SET @tmpstr = 'CREATE LOGIN ' + QUOTENAME( @name ) + ' WITH PASSWORD = ' + @PWD_string + ' HASHED, SID = ' + @SID_string + ', DEFAULT_DATABASE = [' + @defaultdb + ']'
IF ( @is_policy_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_POLICY = ' + @is_policy_checked
END
IF ( @is_expiration_checked IS NOT NULL )
BEGIN
SET @tmpstr = @tmpstr + ', CHECK_EXPIRATION = ' + @is_expiration_checked
END
END
IF (@denylogin = 1)
BEGIN -- login is denied access
SET @tmpstr = @tmpstr + '; DENY CONNECT SQL TO ' + QUOTENAME( @name )
END
ELSE IF (@hasaccess = 0)
BEGIN -- login exists but does not have access
SET @tmpstr = @tmpstr + '; REVOKE CONNECT SQL TO ' + QUOTENAME( @name )
END
IF (@is_disabled = 1)
BEGIN -- login is disabled
SET @tmpstr = @tmpstr + '; ALTER LOGIN ' + QUOTENAME( @name ) + ' DISABLE'
END
PRINT @tmpstr
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @type, @is_disabled, @defaultdb, @hasaccess, @denylogin
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
--EXEC sp_help_revlogin
-------------------------------------------------------------TSQL query to stop long running SQL Job and start it back---------------------------
--if elapsed time is actually needed, uncomment
DECLARE @elapsed_minutes_max int
SET @elapsed_minutes_max = 240 --4 hours, adjust as needed
IF OBJECT_ID('tempdb.dbo.#jobactivity') IS NOT NULL
DROP TABLE #jobactivity
CREATE TABLE #jobactivity (
session_id int NULL,
job_id uniqueidentifier NULL,
job_name sysname NULL,
run_requested_date datetime NULL,
run_requested_source sysname NULL, --1=via schedule; 2=via alert; 3=via startup; 4=via user; 6=via CPU idle.
queued_date datetime NULL,
start_execution_date datetime NULL,
last_executed_step_id int NULL,
last_executed_step_date datetime NULL,
stop_execution_date datetime NULL,
next_scheduled_run_date datetime NULL,
job_history_id int NULL,
message nvarchar(1024) NULL,
run_status int NULL,
operator_id_emailed int NULL,
operator_id_netsent int NULL,
operator_id_paged int NULL
)
INSERT INTO #jobactivity
EXEC msdb.dbo.sp_help_jobactivity
IF EXISTS(
SELECT *
FROM #jobactivity ja
WHERE
ja.start_execution_date IS NOT NULL AND
ja.stop_execution_date IS NOT NULL AND
DATEDIFF(MINUTE, ja.start_execution_date, GETDATE()) >= @elapsed_minutes_max
AND ja.job_name = N'Order Planning2')
BEGIN
EXEC msdb.dbo.sp_stop_job N'Order Planning2'
print 'stop it'
END
ELSE
BEGIN
PRINT 'No JOB FOUND'
END
drop table #jobactivity
---------------------------------------Shrink TEMPDB without restart(last try before restart)--------------------------------------------
CHECKPOINT
GO
DBCC FREEPROCCACHE
GO
DBCC SHRINKFILE (TEMPDEV, 1024)
GO
-----------------------------------------TSQL code to find table with backedup with date----------------------
sp_tables '%_[1-30][1-12][1991-2020]%'
--------------------------------------General Guidelines to improve query performance-------------
Table should have primary key
Table should have minimum of one clustered index
Table should have appropriate amount of non-clustered index
Non-clustered index should be created on columns of table based on query which is running
Following priority order should be followed when any index is created a) WHERE clause, b) JOIN clause, c) ORDER BY clause, d) SELECT clause
Do not to use Views or replace views with original source table
Triggers should not be used if possible, incorporate the logic of trigger in stored procedure
Remove any adhoc queries and use Stored Procedure instead
Check if there is atleast 30% HHD is empty – it improves the performance a bit
If possible move the logic of UDF to SP as well
Remove * from SELECT and use columns which are only necessary in code
Remove any unnecessary joins from table
If there is cursor used in query, see if there is any other way to avoid the usage of this (either by SELECT … INTO or INSERT … INTO, etc)
-------------- Disable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
----------------------- Enable all constraints for database
EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
---------To find column name of collation
Select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME = 'col_test
----Change Collation of a SQL Server Table Column---------------------
USE databasename
GO
ALTER TABLE [dbo].[col_test]
ALTER COLUMN Text NVARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL
------------------------------Change collation of database----------------------------
USE master;
GO
ALTER DATABASE dbname
COLLATE French_CI_AS ;
GO
Note;
if you change the database collation you need to check whether column of table collation also changed or not. If not you need to change one by one use below script
USE databasename
GO
ALTER TABLE [dbo].[col_test]
ALTER COLUMN Text NVARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CS_AS NULL
before change collation of column you need to verify following
You cannot change the collation of a column that is currently referenced by any one of the following:
A computed column
An index
Distribution statistics, either generated automatically or by the CREATE STATISTICS statement
A CHECK constraint
A FOREIGN KEY constraint
When you work with tempdb, the COLLATE clause includes a database_default option to specify that a column in a temporary table uses the collation default of the current user database for the connection instead of the collation of tempdb.
see for more
https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-column-collation?view=sql-server-2017
---------------------Verify the collation setting for database
SELECT name, collation_name
FROM sys.databases
WHERE name = N'dbname';
------------------------------------------------------------------------------------
ALTER DATABASE [msdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [msdb] SET DISABLE_BROKER
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;
ALTER DATABASE [msdb] SET ENABLE_BROKER
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb' ;
ALTER DATABASE [msdb] SET MULTI_USER
EXEC msdb.dbo.sysmail_stop_sp;
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_start_sp;
EXEC msdb.dbo.sysmail_help_status_sp;
EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'mail';
-------------------------table with rowcount and size------------------
SELECT
t.NAME AS TableName,
p.rows AS RowCounts,
SUM(a.total_pages) * 8 / 1024 AS TotalSpaceMB,
SUM(a.used_pages) * 8 / 1024 AS UsedSpaceMB ,
(SUM(a.total_pages) - SUM(a.used_pages)) * 8 / 1024 AS UnusedSpaceMB
FROM
sys.tables t
INNER JOIN
sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
sys.schemas s ON t.schema_id = s.schema_id
--WHERE
-- t.is_ms_shipped = 0
-- AND i.OBJECT_ID > 255
GROUP BY
t.Name, s.Name, p.Rows
ORDER BY
UsedSpaceMB DESC, t.Name
------------------------------How much Memory SQL DB used----------------------------------------------
SELECT TOP 10 DB_NAME(database_id) Database_Name,
COUNT (*) * 8 / 1024 AS MBUsed,COUNT (*) * 8 / 1024 /1024 GBused
FROM sys.dm_os_buffer_descriptors
where DB_NAME(database_id) is not null
GROUP BY database_id
ORDER BY COUNT (*) * 8 / 1024 DESC
---------------------------------------------index fragment based on conditions-----
Depending on fragmentation index level appropriated action will be taken (no action, rebuild or reorganize)
If average fragmentation is less than 10% no action will be taken, if average fragmentation is between 10% and 30% index will be reorganized, finally if index average fragmentation is greater than 30% index will be rebuilt.
-- Ensure a USE <databasename> statement has been executed first.
SET NOCOUNT ON;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;
DECLARE @schemaname nvarchar(130);
DECLARE @objectname nvarchar(130);
DECLARE @indexname nvarchar(130);
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @frag float;
DECLARE @command nvarchar(4000);
DECLARE @dbid smallint;
-- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function
-- and convert object and index IDs to names.
SET @dbid = DB_ID();
SELECT
[object_id] AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag, page_count
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (@dbid, NULL, NULL , NULL, N'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 -- Allow limited fragmentation
AND index_id > 0 -- Ignore heaps
AND page_count > 25; -- Ignore small tables
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT objectid,indexid, partitionnum,frag FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN
FETCH NEXT
FROM partitions
INTO @objectid, @indexid, @partitionnum, @frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @objectid;
SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @objectid AND index_id = @indexid;
SELECT @partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @objectid AND index_id = @indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @frag < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @frag >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partitioncount > 1
SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
EXEC (@command);
PRINT N'Executed: ' + @command;
END
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
GO
-----------------------------------------------------------------------------------------------
Phases of recovery(SQL Server)
the recovery algorithm has 3 phases based around the last checkpoint in the transaction log.
Phase 1: Analysis. Starts at the last checkpoint in transaction log. This pass determines and constructs a dirty page table (DPT) consisting of pages that might be dirty at the time SQL Server stopped. An active transaction table is built of the uncommitted transactions at the time of the SQL Server stopped also.
Phase 2: Redo. This phase returns the database to the state at the time the SQL service stopped. Starting point for this forward passbeing the oldest uncommitted transaction. The mininum Log Sequence name (each log record is labelled with an LSN) in the DPT is the first time SQL Server expects to have to redo an operation on a page, redoing the logged operations starting right back at the oldest open transaction so that the neccessary locks can be aquired.
Phase 3: Undo: Here the list of active transaction (uncommitted at the time SQL Server stoopped) which where indentified in Phase 1 are rolled back individually. SQL Server follows the links between entries in the transaction log for each transaction. Any transaction that was not committed at the time SQL Server stopped is undone.
Recovery can be done when you restore the database, but it is also done at the startup of the database (crash recovery).
---------------------------------------------------------VLF and log file Info------------------------------
Understanding the VLF(Virtual Log File)
A database can have one or more log file. In general there will be only one log file as there is no performance improvement by having multiple log file. SQL server uses the transaction log in sequential manner.As the data file divided into pages,log files are divided into virtual log file(VLF).The size of the VLFs in a log file may not be in equal size. SQL server decide the size and number of VLF in a log file based on the size of the log file growth as given below.
Growth upto 64 MB = 4 VLF
From 64 MB to 1 GB = 8 VLF
Larger than 1 GB = 16 VLF
dbcc loginfo--------VLF info
dbcc sqlperf(logspace)-------------
ref:
http://www.sqlservercentral.com/blogs/practicalsqldba/2013/09/30/sql-server-part-1-architecture-of-transaction-log/
--From SQL 2017 To know VLF Count below DMV can be used
SELECT [name], s.database_id,
COUNT(l.database_id) AS 'VLF Count',
SUM(vlf_size_mb) AS 'VLF Size (MB)',
SUM(CAST(vlf_active AS INT)) AS 'Active VLF',
SUM(vlf_active*vlf_size_mb) AS 'Active VLF Size (MB)',
COUNT(l.database_id)-SUM(CAST(vlf_active AS INT)) AS 'In-active VLF',
SUM(vlf_size_mb)-SUM(vlf_active*vlf_size_mb) AS 'In-active VLF Size (MB)'
FROM sys.databases s
CROSS APPLY sys.dm_db_log_info(s.database_id) l
GROUP BY [name], s.database_id
ORDER BY 'VLF Count' DESC
GO
-----------------------------Change collation of SQL Server---------------
To rebuild system databases for an instance of SQL Server:
Insert the SQL Server 2014 installation media into the disk drive, or, from a command prompt, change directories to the location of the setup.exe file on the local server. The default location on the server is C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Release.
From a command prompt window, enter the following command. Square brackets are used to indicate optional parameters. Do not enter the brackets. When using a Windows operating system that has User Account Control (UAC) enabled, running Setup requires elevated privileges. The command prompt must be run as Administrator.
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts [ /SAPWD= StrongPassword ] [ /SQLCOLLATION=CollationName]
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=SHELL2_P2\Administrator /SAPWD= reset$123 /SQLCOLLATION=SQL_Latin1_General_CP850_BIN
Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=SHELL2_P1\Administrator /SAPWD= reset$123 /SQLCOLLATION=SQL_Latin1_General_CP850_BIN
https://docs.microsoft.com/en-us/sql/relational-databases/databases/rebuild-system-databases?view=sql-server-2014
https://www.sanssql.com/2012/10/how-to-change-server-collation.html
https://www.sanssql.com/2013/10/change-sql-server-collation-without.html
-------------------------------------------Permuations and Combinations----------------------------------
--option 1
Create table #pk(col1 varchar(100),col2 varchar(100),primary key(col1,col2))
declare @t1 table (col1 varchar(100))
insert @t1
select 'Data1' UNION
select 'Data2' UNION
select 'Data3' UNION
select 'Data4' UNION
select 'Data5'
Declare @t2 table (col2 varchar(100))
insert @t2
select 'Relation1' UNION
select 'Relation2' UNION
select 'Relation3' UNION
select 'Relation4' UNION
select 'Relation5'UNION
select 'Relation6'
;with cteAllColumns as (
select col1 as col
from @t1
union
select col2 as col
from @t2
)
insert into #pk
select c1.col , c2.col
from cteAllColumns c1
cross join cteAllColumns c2
where c1.col < c2.col
order by c1.col
select * from #pk
--option 2
--Create table #pk(col1 varchar(4000),col2 varchar(4000),primary key(col1,col2))
DECLARE @ConcatString1 VARCHAR(4000)
SELECT top 11 @ConcatString1 = COALESCE(@ConcatString1 + ', ', '') + name
FROM master.dbo.sysdatabases
DECLARE @ConcatString2 VARCHAR(4000)
DECLARE @tags NVARCHAR(4000) = 'Data1,Data2,Data3,Data4,Data5,Data6,Data7,Data8,Data9,Data10,Data11'
SELECT @ConcatString2 = COALESCE(@ConcatString2 + ', ', '') +value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';
Create table #CSV1(Data1 VARCHAR(4000))
Insert into #CSV1
Values(@ConcatString1)
Create table #CSV2(Data2 VARCHAR(4000))
Insert into #CSV2
Values(@ConcatString2)
select * from #CSV1
select * from #CSV2
--drop table #CSV1
--drop table #CSV2
SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
into #C1
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Data1, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM #CSV1) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
into #C2
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Data2, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM #CSV2) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
Select * from #C1
Select * from #C2
declare @t1 table (Info1 varchar(8000),col1 varchar(8000))
insert into @t1
Select * FROM #CSV1
CROSS APPLY STRING_SPLIT(Data1, ',');
declare @t2 table (Info2 varchar(8000),col2 varchar(8000))
insert into @t2
Select * FROM #CSV2
CROSS APPLY STRING_SPLIT(Data2, ',');
Select * from @t1
Select * from @t2
;with cteAllColumns as (
select col1 as col
from @t1
union
select col2 as col
from @t2
)
select c1.col, c2.col
from cteAllColumns c1
cross join cteAllColumns c2
where c1.col < c2.col
order by c1.col
--select * from #pk
drop table #CSV1
drop table #CSV2
--drop table #pk
drop table #C1
drop table #C2
--option 3
DECLARE @ConcatString VARCHAR(4000)
SELECT top 11 @ConcatString = COALESCE(@ConcatString + ', ', '') + name
FROM master.dbo.sysdatabases
DECLARE @ConcatString1 VARCHAR(4000)
SELECT top 11 @ConcatString1 = COALESCE(@ConcatString1 + ', ', '') + name
FROM ts4.master.dbo.sysdatabases
where name not in ('Data1', 'Data2', 'Data3', 'Data4', 'Data5', 'Data6', 'Data7', 'Data8', 'Data9', 'Data10', 'Data11')
Create table #CSV(Data VARCHAR(4000))
Insert into #CSV
Values(@ConcatString)
Insert into #CSV
Values(@ConcatString1)
select * from #CSV
--drop table #CSV
--select parsename(Data,4)as [4],parsename(Data,3)as [3],parsename(Data,2)as [2],parsename(Data,1)as [1] from
--(Select replace(Data,',','.') Data from #CSV) t
SELECT DISTINCT
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name1
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name5
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name1
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name5
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Data, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM #CSV) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
drop table #CSV
-super query-------------------
declare @numrows int
declare @numrows2 int
declare @numrows3 int
declare @numrows4 int
set @numrows=1
set @numrows2=4
set @numrows3=2
set @numrows4=4
create table #c1(Id int,Name varchar(300),Value int)
declare @vSQL varchar(1000)
--select @numrows = 5
select @vSQL = 'select top ' + convert(varchar, @numrows) + ' * from t11 Order by Id'
insert into #c1
Execute (@vSQL)
update #c1 set Name=Name+'('+'WK'+')'
create table #c2(Id int,Name varchar(300),Value int)
declare @vSQL2 varchar(1000)
--select @numrows2 = 4
select @vSQL2 = 'select top ' + convert(varchar, @numrows2) + ' * from t12 Order by Id'
insert into #c2
Execute (@vSQL2)
update #c2 set Name=Name+'('+'BT'+')'
create table #c3(Id int,Name varchar(300),Value int)
declare @vSQL3 varchar(1000)
--select @numrows2 = 4
select @vSQL3 = 'select top ' + convert(varchar, @numrows3) + ' * from t13 Order by Id'
insert into #c3
Execute (@vSQL3)
update #c3 set Name=Name+'('+'ALL'+')'
create table #c4(Id int,Name varchar(300),Value int)
declare @vSQL4 varchar(1000)
--select @numrows = 5
select @vSQL4 = 'select top ' + convert(varchar, @numrows4) + ' * from t14 Order by Id'
insert into #c4
Execute (@vSQL4)
update #c4 set Name=Name+'('+'BL'+')'
;with cteAllColumns as (
select Name as col
from #c1
union
select Name as col
from #c2
union
select Name as col
from #c3
union
select Name as col
from #c4
)
select *
into #fmtb4
from cteAllColumns cte1
order by col desc
;with cteAllColumns as (
select Name as col
from #c1
union
select Name as col
from #c2
union
select Name as col
from #c3
union
select Name as col
from #c4
)
select cte1.col col1,cte2.col col2
into #fmtbl
from cteAllColumns cte1
cross join cteAllColumns cte2
where cte1.col < cte2.col
order by cte1.col
Declare @Column1 Nvarchar(300)
Declare @Column2 Nvarchar(300)
Declare @Column1After Nvarchar(300)
Declare @Column2After Nvarchar(300)
DECLARE CC CURSOR FOR
SELECT DISTINCT col1,col2
FROM #fmtbl
OPEN CC
FETCH NEXT FROM CC INTO @Column1,@Column2
WHILE @@FETCH_STATUS = 0
BEGIN
set @Column1After=@Column1+'['+'C'+']'
set @Column2After=@Column2+'['+'VC'+']'
update #fmtbl set col1=@Column1After where col1=@Column1 and col1<>@Column1+'['+'C'+']'
update #fmtbl set col2=@Column2After where col2=@Column2 and col1<>@Column2+'['+'VC'+']'
FETCH NEXT FROM CC INTO @Column1,@Column2
END
--select ''[afterfmtb4],* from #fmtbl
--order by col1,col2
SELECT Distinct top 2
stuff(
(
SELECT ','+ col FROM #fmtb4 FOR XML PATH('')
),1,1,'') Results
into #commatbl
FROM (SELECT DISTINCT top 1 col FROM #fmtb4 ) #fmtb4
CLOSE CC
DEALLOCATE CC
select col1,col2,Results
Into #CP
FROM #fmtbl P CROSS Join
#commatbl C
Select ''BEFORE,* from #CP
--update #CP set Results = replace(Results,substring(Results,charindex(col1,(substring(Results,charindex(col1,(Results)),charindex(',',Results))+'['+'C'+']')),charindex(',',Results)-1),col1)
update #CP set Results = Stuff(Results,charindex(Stuff(col1,charindex('[',col1),len(right(col1,3)),''),(Results)),len(Stuff(col1,charindex('[',col1),len(right(col1,3)),'')),col1)
update #CP set Results = Stuff(Results,charindex(Stuff(col2,charindex('[',col2),len(right(col2,4)),''),(Results)),len(Stuff(col2,charindex('[',col2),len(right(col2,4)),'')),col2)
Select 'AFTER'AFTER,* from #CP
Order by col1,col2
SELECT DISTINCT col1,col2,
S.a.value('(/H/r)[1]', 'VARCHAR(100)') AS Name1
,S.a.value('(/H/r)[2]', 'VARCHAR(100)') AS Name2
, S.a.value('(/H/r)[3]', 'VARCHAR(100)') AS Name3
, S.a.value('(/H/r)[4]', 'VARCHAR(100)') AS Name4
, S.a.value('(/H/r)[5]', 'VARCHAR(100)') AS Name5
, S.a.value('(/H/r)[6]', 'VARCHAR(100)') AS Name6
,S.a.value('(/H/r)[7]', 'VARCHAR(100)') AS Name7
, S.a.value('(/H/r)[8]', 'VARCHAR(100)') AS Name8
, S.a.value('(/H/r)[9]', 'VARCHAR(100)') AS Name9
, S.a.value('(/H/r)[10]', 'VARCHAR(100)') AS Name10
, S.a.value('(/H/r)[11]', 'VARCHAR(100)') AS Name11
--into #C1
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(Results, ',', '</r><r>') + '</r></H>' AS XML) AS [vals]
FROM #CP) d
CROSS APPLY d.[vals].nodes('/H/r') S(a)
drop table #c1
drop table #c2
drop table #c3
drop table #c4
drop table #fmtbl
drop table #fmtb4
drop table #CP
drop table #commatbl
