Перейти к содержимому

Mssql

Все по MSsql

Как исправить ошибку The target principal name is incorrect. Cannot generate SSPI context.

Ошибка

TITLE: Connect to Server
——————————

Cannot connect to DB01.

——————————
ADDITIONAL INFORMATION:

The target principal name is incorrect. Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476

——————————
BUTTONS:

OK
——————————

Исправляется с помощью  https://www.microsoft.com/en-us/download/details.aspx?id=39046

KerberosX64MSI (2)

Запускаем “C:\Program Files\Microsoft\Kerberos Configuration Manager for SQL Server\KerberosConfigMgr.exe”

Как исправить ошибку The target principal name is incorrect. Cannot generate SSPI context.

Как исправить ошибку The target principal name is incorrect. Cannot generate SSPI context.

Как найти в таблице sp_whoisactive по времени выполнения запроса.

SELECT [dd hh:mm:ss.mss]
,[start_time]
 ,[collection_time]
      ,[session_id]
      ,[sql_text]
      ,[sql_command]
      ,[login_name]
      ,[wait_info]
      ,[tran_log_writes]
      ,[CPU]
      ,[tempdb_allocations]
      ,[tempdb_current]
      ,[blocking_session_id]
      ,[reads]
      ,[writes]
      ,[physical_reads]
      ,[query_plan]
      ,[used_memory]
      ,[status]
      ,[tran_start_time]
      ,[open_tran_count]
      ,[percent_complete]
      ,[host_name]
      ,[database_name]
      ,[program_name]
      
      ,[login_time]
      ,[request_id]
      ,[collection_time]
  FROM [DBAtools].[dbo].[WhoIsActive]
  WHERE 
 CONVERT(CHAR(8),COLLECTION_time,8) BETWEEN '00:00:00' AND '23:00:00' AND
       datepart(YEAR,start_time) BETWEEN 2020 AND 2020 and
       datepart(month,start_time) BETWEEN 04 AND 04 and
       datepart(day,start_time) BETWEEN 22 AND 23
     and  [dd hh:mm:ss.mss]  > '00 01:00:00.0000'

 

Как узнать кто больше всего использует tempdb и сколько места занимает запрос в tempdb. полезные скрипты

запрос кто в данный момент использует tempdb

SELECT
st.dbid AS QueryExecutionContextDBID,
DB_NAME(st.dbid) AS QueryExecContextDBNAME,
st.objectid AS ModuleObjectId,
SUBSTRING(st.TEXT,
dmv_er.statement_start_offset/2 + 1,
(CASE WHEN dmv_er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
ELSE dmv_er.statement_end_offset
END - dmv_er.statement_start_offset)/2) AS Query_Text,
dmv_tsu.session_id ,
dmv_tsu.request_id,
dmv_tsu.exec_context_id,
(dmv_tsu.user_objects_alloc_page_count - dmv_tsu.user_objects_dealloc_page_count) AS OutStanding_user_objects_page_counts,
(dmv_tsu.internal_objects_alloc_page_count - dmv_tsu.internal_objects_dealloc_page_count) AS OutStanding_internal_objects_page_counts,
dmv_er.start_time,
dmv_er.command,
dmv_er.open_transaction_count,
dmv_er.percent_complete,
dmv_er.estimated_completion_time,
dmv_er.cpu_time,
dmv_er.total_elapsed_time,
dmv_er.reads,dmv_er.writes,
dmv_er.logical_reads,
dmv_er.granted_query_memory,
dmv_es.HOST_NAME,
dmv_es.login_name,
dmv_es.program_name
FROM sys.dm_db_task_space_usage dmv_tsu
INNER JOIN sys.dm_exec_requests dmv_er
ON (dmv_tsu.session_id = dmv_er.session_id AND dmv_tsu.request_id = dmv_er.request_id)
INNER JOIN sys.dm_exec_sessions dmv_es
ON (dmv_tsu.session_id = dmv_es.session_id)
CROSS APPLY sys.dm_exec_sql_text(dmv_er.sql_handle) st
WHERE (dmv_tsu.internal_objects_alloc_page_count + dmv_tsu.user_objects_alloc_page_count) > 0
ORDER BY (dmv_tsu.user_objects_alloc_page_count 
Читать далее

Как узнать кто подключился к mssql. С какова ip

SELECT
dc.session_id          AS [SPID]
,dc.client_net_address  AS [IP клиента]
,sp.hostname            AS [Имя PC клиента]
,dc.local_net_address   AS [IP подключения к серверу]
,dc.net_transport       AS [Протокол подключения]
,dc.local_tcp_port      AS [Порт]
,sp.[program_name]      AS [Имя программы]
,sp.loginame
FROM
sys.dm_exec_connections dc
INNER JOIN
MASTER.sys.sysprocesses sp
ON  dc.session_id = sp.spid
WHERE
sp.spid

 

Как просмотреть историю запросов mssql.

/************************************************************
 *  * k.moskvichev ©
 * Time: 27.06.2019 13:13:52
 ************************************************************/

SELECT 		
      creation_time
           ,last_execution_time
           ,execution_count
           ,total_worker_time / 1000      AS CPU
           ,CONVERT(MONEY ,(total_worker_time)) / (execution_count * 1000) AS [AvgCPUTime]
           ,qs.total_elapsed_time / 1000  AS TotDuration
           ,CONVERT(MONEY ,(qs.total_elapsed_time)) / (execution_count * 1000) AS [AvgDur]
           ,total_logical_reads           AS [Reads]
           ,total_logical_writes          AS [Writes]
           ,total_logical_reads + total_logical_writes AS [AggIO]
           ,CONVERT(
                MONEY
               ,(total_logical_reads + total_logical_writes) / (execution_count + 0.0)
            )                             AS [AvgIO]
           ,CASE 
                 WHEN sql_handle IS NULL THEN ' '
                 ELSE (
                          SUBSTRING(
                              st.text
                             ,(qs.statement_start_offset + 2) / 2
                             ,(
                                  CASE 
                                       WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX) ,st.text)) * 2
                                       ELSE qs.statement_end_offset
                                  END - qs.statement_start_offset
                              ) / 2
                          )
                      
Читать далее

Как узнать нагрузку на диски и файловые группы. Ввод-Вывод. Mssql. Где больше всего IO

SELECT 		
			DB_NAME(a.database_id)          AS [Database Name]
           --,a.FILE_ID
           ,i.name
           ,a.io_stall_read_ms
           ,a.num_of_reads
           ,CAST(a.io_stall_read_ms / (1.0 + a.num_of_reads) AS NUMERIC(10 ,1)) AS [avg_read_stall_ms]
           ,a.io_stall_write_ms
           ,a.num_of_writes
           ,CAST(
                a.io_stall_write_ms / (1.0 + a.num_of_writes) AS NUMERIC(10 ,1)
            )                             AS [a.avg_write_stall_ms]
           ,a.io_stall_read_ms + a.io_stall_write_ms AS [io_stalls]
           ,a.num_of_reads + a.num_of_writes  AS [total_io]
           ,CAST(
                (a.io_stall_read_ms + a.io_stall_write_ms) / (1.0 + a.num_of_reads + a.num_of_writes) AS NUMERIC(10 ,1)
            )                             AS [avg_io_stall_ms],
            i.physical_name          
FROM   
			sys.dm_io_virtual_file_stats(NULL ,NULL)a
			INNER JOIN 
	   		sys.master_files i
            ON  a.file_id = i.file_id 
            AND            a.database_id = i.database_id
--WHERE i.database_id in  ('7')
			
ORDER BY
       		avg_io_stall_ms                  DESC;

 

Как создать хранимую процедуру для реиндекса таблиц mssql (reindex)

Задача делать реиндекс только определенных таблиц.

Создаём таблицу

USE [ax_molniya]
GO

/****** Object:  Table [dbo].[www_reindex_tablename]    Script Date: 08/17/2018 08:45:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[www_reindex_tablename](
	[objectname] [nvarchar](128) NULL,
	[group] [nvarchar](1) NULL,
	[id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO


Наполняем её таблицами которые нужно реиндексировать

Как создать хранимую процедуру для реиндекса таблиц mssql (reindex)

Делаем хранимую процедуру

USE [ax_molniya]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[www_reindex_ALTER]
-- Add the parameters for the stored procedure here

-- если readonly = 0 (значение по- умолчанию), тогда выполняется вся работа скрипта)
-- если readonly = 1, тогда предоставится информация о 
Читать далее

Как вывести список заданий (job) в mssql

use msdb
go
SELECT 
    [sJOB].[name] AS [JobName]
    , [sDBP].[name] AS [JobOwner]
    , [sCAT].[name] AS [JobCategory]
  , [sJOB].[description] AS [JobDescription]
    , [sJSTP].[step_id] AS [JobStartStepNo]
    , [sJSTP].[step_name] AS [JobStartStepName]
    , [sJOB].[date_created] AS [JobCreatedOn]
    , [sJOB].[date_modified] AS [JobLastModifiedOn]
  , CASE [sJOB].[enabled]
        WHEN 1 THEN 'Yes'
        WHEN 0 THEN 'No'
      END AS [IsEnabled]
       , CASE
          WHEN [sSCH].[schedule_uid] IS NULL THEN 'No'
          ELSE 'Yes'
          END AS [IsScheduled]
    , CASE 
        WHEN [freq_type] = 64 THEN 'Start automatically when SQL Server Agent starts'
        WHEN [freq_type] = 128 THEN 'Start whenever the CPUs become idle'
        WHEN [freq_type] IN (4,8,16,32) THEN 'Recurring'
        WHEN [freq_type] = 1 THEN 'One Time'
      
Читать далее

Как сделать письмо с ошибками выполнения заданий (job)

IF (SELECT COUNT(*)
FROM dbo.sysjobs AS s 
INNER JOIN sysjobsteps AS s2 (nolock) ON s2.job_id = s.job_id
INNER JOIN dbo.sysjobhistory sh (nolock) ON sh.instance_id=(SELECT MAX(sjh.instance_id) FROM dbo.sysjobhistory sjh (nolock) WHERE sjh.job_id=s.job_id AND sjh.step_id=s2.step_id) 
			  AND sh.run_date>=(SELECT MAX(sjh.run_date) FROM dbo.sysjobhistory sjh (nolock) WHERE sjh.job_id=s.job_id AND sjh.step_id=0)
WHERE s.[enabled]=1 AND sh.run_status=0)>0
BEGIN
DECLARE @tableHTML AS NVARCHAR(max)

SET @tableHTML =
    N'<H1>Ошибки в джобах на sql сервере '+@@SERVERNAME+'</H1>' +
    N'<table border="1">' +
    N'<tr><th>Джоб</th><th>ID шага</th>' +
    N'<th>Имя шага</th><th>команда</th><th>база</th>' +
    N'<th>последний запуск</th><th>ошибка</th></tr>' +
    CAST ( ( SELECT td = s.name,'', td = s2.step_id,'', td = s2.step_name, '',td = cast(s2.command AS VARCHAR(100)),'', td = s2.database_name,'',
       td = s2.last_run_date,'', 
Читать далее

Как посмотреть кэш план

sELECT TOP 20
	qs.last_execution_time AS Last_execution_time,
	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) AS Query_text, 
	qp.query_plan AS Query_plan,
	qs.execution_count AS Execution_count
FROM sys.dm_exec_query_stats AS qs
	CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
	CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
--WHERE 
WHERE
/* datepart(YEAR,qs.last_execution_time) BETWEEN 2018 AND 2018 and
		datepart(month,qs.last_execution_time) BETWEEN 06 AND 06 and
		datepart(day,qs.last_execution_time) BETWEEN 20 AND 22
	
		 --AND		CONVERT(CHAR(8),last_execution_time,8) BETWEEN '09:00:00' and '23:50:00' */


qs.last_execution_time > '2018-05-20 11:30:00.000' /* 1. Date & Time filter */
	--and qt.text like '%ChequeHead%'	/* 2. SQL query text filter */
	and qt.text not like '%Query Finder%' 
Читать далее