|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 |
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' END [ScheduleType] , CASE [freq_type] WHEN 1 THEN 'One Time' WHEN 4 THEN 'Daily' WHEN 8 THEN 'Weekly' WHEN 16 THEN 'Monthly' WHEN 32 THEN 'Monthly - Relative to Frequency Interval' WHEN 64 THEN 'Start automatically when SQL Server Agent starts' WHEN 128 THEN 'Start whenever the CPUs become idle' END [Occurrence] , CASE [freq_type] WHEN 4 THEN 'Occurs every ' + CAST([freq_interval] AS VARCHAR(3)) + ' day(s)' WHEN 8 THEN 'Occurs every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' week(s) on ' + CASE WHEN [freq_interval] & 1 = 1 THEN 'Sunday' ELSE '' END + CASE WHEN [freq_interval] & 2 = 2 THEN ', Monday' ELSE '' END + CASE WHEN [freq_interval] & 4 = 4 THEN ', Tuesday' ELSE '' END + CASE WHEN [freq_interval] & 8 = 8 THEN ', Wednesday' ELSE '' END + CASE WHEN [freq_interval] & 16 = 16 THEN ', Thursday' ELSE '' END + CASE WHEN [freq_interval] & 32 = 32 THEN ', Friday' ELSE '' END + CASE WHEN [freq_interval] & 64 = 64 THEN ', Saturday' ELSE '' END WHEN 16 THEN 'Occurs on Day ' + CAST([freq_interval] AS VARCHAR(3)) + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)' WHEN 32 THEN 'Occurs on ' + CASE [freq_relative_interval] WHEN 1 THEN 'First' WHEN 2 THEN 'Second' WHEN 4 THEN 'Third' WHEN 8 THEN 'Fourth' WHEN 16 THEN 'Last' END + ' ' + CASE [freq_interval] WHEN 1 THEN 'Sunday' WHEN 2 THEN 'Monday' WHEN 3 THEN 'Tuesday' WHEN 4 THEN 'Wednesday' WHEN 5 THEN 'Thursday' WHEN 6 THEN 'Friday' WHEN 7 THEN 'Saturday' WHEN 8 THEN 'Day' WHEN 9 THEN 'Weekday' WHEN 10 THEN 'Weekend day' END + ' of every ' + CAST([freq_recurrence_factor] AS VARCHAR(3)) + ' month(s)' END AS [Recurrence] , CASE [freq_subday_type] WHEN 1 THEN 'Occurs once at ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') WHEN 2 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Second(s) between ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') + ' & ' + STUFF( STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') WHEN 4 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Minute(s) between ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') + ' & ' + STUFF( STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') WHEN 8 THEN 'Occurs every ' + CAST([freq_subday_interval] AS VARCHAR(3)) + ' Hour(s) between ' + STUFF( STUFF(RIGHT('000000' + CAST([active_start_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') + ' & ' + STUFF( STUFF(RIGHT('000000' + CAST([active_end_time] AS VARCHAR(6)), 6) , 3, 0, ':') , 6, 0, ':') END [Frequency] |
|
1 2 3 4 5 6 7 8 |
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) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
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' */ |
|
1 2 3 4 5 6 7 |
declare @dt as datetime set @dt= getdate()-01 -- дата, меньше которой будем обновлять begin try select o.name as [objname],s.name as [shname],o.object_id,st.name as [stname] |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
SELECT DatabaseName = DB_NAME(), TableName = OBJECT_NAME(s.[object_id]), IndexName = i.name, user_updates, system_updates, 'alter index ['+i.name+'] ON [' +OBJECT_SCHEMA_NAME(i.object_id, DB_ID())+ '].['+OBJECT_NAME(s.[object_id])+'] DISABLE' as [Disable], 'exec sp_rename ''['+OBJECT_SCHEMA_NAME(i.object_id, DB_ID())+'].['+OBJECT_NAME(s.[object_id])+'].['+i.name+']'',''disable_'+i.name+''',''INDEX''' as [Rename] FROM sys.dm_db_index_usage_stats s INNER JOIN sys.indexes i ON s.object_id = i.object_id and s.index_id = i.index_id WHERE s.database_id = DB_ID() and OBJECTPROPERTY(s.[object_id], 'IsMsShipped') = 0 and s.user_seeks = 0 and s.user_scans = 0 and s.user_lookups = 0 and i.is_disabled = 0 and i.is_unique = 0 and i.is_primary_key = 0 and i.type_desc <> 'HEAP' order by user_updates + system_updates desc |
|
1 2 3 4 5 6 7 |
-- текущая ситуация на сервере (выполняемые запросы) select session_id, status, wait_type, command, last_wait_type, percent_complete, qt.text, total_elapsed_time/1000 as [total_elapsed_time, сек], wait_time/1000 as [wait_time, сек], (total_elapsed_time - wait_time)/1000 as [work_time, сек] from sys.dm_exec_requests as qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt where session_id >= 50 and session_id <> @@spid order by 1 |
|
1 2 3 4 5 6 7 |
USE MASTER SELECT cast ((1.0 * stats.io_stall_write_ms / stats.num_of_writes) as int) FROM sys.dm_io_virtual_file_stats(2,null) as stats INNER JOIN master.sys.master_files AS files ON stats.database_id = files.database_id AND stats.file_id = files.file_id WHERE files.type_desc = 'ROWS' |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
with igs as ( select * from sys.dm_db_missing_index_group_stats ) , igd as ( select *, isnull(equality_columns,'')+','+isnull(inequality_columns,'') as ix_col from sys.dm_db_missing_index_details |
Хранимая процедура что бы посмотреть в каком состоянии репликации.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE PROCEDURE GetReplicationAgentStatus AS BEGIN set nocount on set transaction isolation level read uncommitted /* Make sure your agents are in the correct category i.e Merge agents under REPL-Merge, Distribution agents under REPL-Distribution and LogReader agent under REPL-LogReader */ |
Подключаем точку к сети и консольным кабелем. Включаем tftp сервер. Внутр папки ложим подготовленный файл конфигурации.
Как исправить ошибку
|
1 |
The OLE DB provider "SQLNCLI" for linked server "s0069" reported a change in schema version between compile time ("185675752805467") and run time ("185684342736281") for table |
возникает при не правильном кеш плане у вьюхи. делаем команду
Если VMWare ESXi ставится на лезвие без проблем, то для установки Windows Server 2012 R2 прямо на железо нужно в БИОСе обязательно включить активность, иначе программа установки Windows Server 2012 R2 сообщит о невозможности использования дисков (из подключенного хранилища): «Установка Windows на данный диск невозможна. Возможно, оборудование данного компьютера не поддерживает загрузку с данного диска. Убедитесь, что контроллер данного диска включен в меню BIOS компьютера.»
Хранимая процедура
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
USE [msdb] GO /****** Object: StoredProcedure [dbo].[alertjob_test] Script Date: 04/25/2018 08:59:24 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[alertjob_test] zakupki NVARCHAR(max) AS BEGIN set nocount ON DECLARE @job_id VARCHAR(50) SET @job_id = (SELECT job_id FROM msdb.dbo.sysjobs WHERE [name] = zakupki) IF (not object_id('tempdb..#x') is null) drop table #x SELECT TOP 2 s.instance_id INTO #x FROM msdb.dbo.sysjobhistory s WHERE s.job_id = @job_id AND s.step_name LIKE '(Job outcome)' ORDER BY s.instance_id DESC IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = zakupki) BEGIN IF (not object_id('tempdb..#z') is null) drop table #z SELECT ja.job_id, j.name as job_name, ja.start_execution_date, isNull(last_executed_step_id,0) + 1 as current_executed_step_id, js.step_name INTO #z FROM msdb.dbo.sysjobactivity ja left join msdb.dbo.sysjobhistory jh on ja.job_history_id = jh.instance_id inner join msdb.dbo.sysjobs j on ja.job_id = j.job_id inner join msdb.dbo.sysjobsteps js on ja.job_id = js.job_id and isNull(ja.last_executed_step_id,0) + 1 = js.step_id WHERE ja.session_id = (select top 1 session_id from msdb.dbo.syssessions order by agent_start_date desc) and start_execution_date is not null and stop_execution_date is NULL IF EXISTS ( select job_name from #z where job_name = zakupki ) BEGIN SELECT 1 END ELSE BEGIN IF EXISTS (SELECT s.run_status FROM msdb.dbo.sysjobhistory s WHERE s.job_id = @job_id AND s.instance_id > (SELECT MIN(instance_id) FROM #x) AND s.run_status = 0) BEGIN SELECT 2 END IF ((SELECT TOP 1 s.run_status FROM msdb.dbo.sysjobhistory s WHERE s.job_id = @job_id AND s.step_name LIKE '(Job outcome)' ORDER BY s.instance_id DESC) = 3) BEGIN SELECT 2 END ELSE BEGIN SELECT 0 END END DROP TABLE #x DROP TABLE #z END ELSE BEGIN SELECT 2 END END |
|
1 2 3 |
Перезапуск служб: FOR /F " tokens=1 " %i in (\Путь до файла с перечнем компов \list.txt) do sc %i stop ИМЯ СЛУЖБЫ FOR /F " tokens=1 " %i in (list.txt) do sc %i start ИМЯ СЛУЖБЫ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT SUM(qs.total_physical_reads) as physical_reads, SUM(qs.total_logical_reads) as logical_reads into T1 FROM ( select top 100000 * from sys.dm_exec_query_stats qs where qs.last_execution_time > (CURRENT_TIMESTAMP - '01:00:00.000') |