Записки по MSSQL, ORACLE, WINDOWS и LINUX

alert audit backup bashhistory bat centos email hp hyper-v ilo info job jobs linux log mail mssql prtg script shiping sms sql SQL Server Profiler sqript symantec Symantec Backup Exec tar tempdb windows xml архивирование бэкап задание индекс индексы лог размер реиндекс сеть смс таблица трасировка фрагментация хабр хранимая процедура

Июль 2017
Пн	Вт	Ср	Чт	Пт	Сб	Вс
« Июн
	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

Поиск по всем таблицам в базе MSSQL

08.06.2017, Categories Mssql Comments Нет комментариев

set nocount on
declare @name varchar(128), @substr nvarchar(4000), @column varchar(128)
set @substr = '%-549139392%' --фрагмент строки, который будем искать

create table #rslt 
(table_name varchar(128), field_name varchar(128), value ntext)

declare s cursor for select table_name as table_name from information_schema.tables where table_type = 'BASE TABLE' order by table_name
open s
fetch next from s into @name
while @@fetch_status = 0
begin
 declare c cursor for 
	select quotename(column_name) as column_name from information_schema.columns 
	  where data_type in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'char', 'sysname') and table_name  = @name
 set @name = quotename(@name)
 open c
 fetch next from c into @column
 while @@fetch_status = 0
 begin
   print 'Processing table - ' + @name + ', column - ' + @column
   exec('insert into #rslt select ''' + @name + ''' as Table_name, ''' + @column + ''', ' + @column + 
	' from' + @name + ' where ' + @column + ' like ''' + @substr + '''')
   fetch next from c into @column
 end
 close c
 deallocate c
 fetch next from s into @name
end
select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Mathes] from #rslt
group by table_name, field_name
order by table_name, field_name
--Если нужно, можем отобразить все найденные значения
--select * from #rslt order by table_name, field_name
drop table #rslt
close s
deallocate s

set nocount on

declare @name varchar(128), @substr nvarchar(4000), @column varchar(128)

set @substr = '%-549139392%' --фрагмент строки, который будем искать

create table #rslt

(table_name varchar(128), field_name varchar(128), value ntext)

declare s cursor for select table_name as table_name from information_schema.tables where table_type = 'BASE TABLE' order by table_name

open s

fetch next from s into @name

while @@fetch_status = 0

begin

declare c cursor for

select quotename(column_name) as column_name from information_schema.columns

where data_type in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'char', 'sysname') and table_name = @name

set @name = quotename(@name)

open c

fetch next from c into @column

while @@fetch_status = 0

begin

print 'Processing table - ' + @name + ', column - ' + @column

exec('insert into #rslt select ''' + @name + ''' as Table_name, ''' + @column + ''', ' + @column +

' from' + @name + ' where ' + @column + ' like ''' + @substr + '''')

fetch next from c into @column

end

close c

deallocate c

fetch next from s into @name

end

select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Mathes] from #rslt

group by table_name, field_name

order by table_name, field_name

--Если нужно, можем отобразить все найденные значения

--select * from #rslt order by table_name, field_name

drop table #rslt

close s

deallocate s

Как восстановить полный (full) и разностный (diff) бэкап базы MSSQL

06.06.2017, Categories Mssql Comments Нет комментариев

RESTORE DATABASE [ax_molniya] FROM  DISK = N'\\192.168.0.38\Backup\AXAPTA\ax_molniya_backup_201705291300.bak' WITH  FILE = 1,  MOVE N'FG_PRIMARY' TO N'e:\ax_molniya.mdf',  MOVE N'FG_CUSTTRANSACTION' TO N'e:\ax_molniya_1.ndf',  MOVE N'FG_INVENTCLOSING' TO N'e:\ax_molniya_2.ndf',  MOVE N'FG_INVENTREMAINS' TO N'e:\ax_molniya_3.ndf',  MOVE N'FG_INVENTTRANSACTION' TO N'e:\ax_molniya_4.ndf',  MOVE N'FG_VENDTRANSACTION' TO N'e:\ax_molniya_5.ndf',  MOVE N'FG_INDEXES' TO N'e:\ax_molniya_6.ndf',  MOVE N'ax_molniya_Log' TO N'e:\ax_molniya_7.LDF',  NORECOVERY,  NOUNLOAD,  STATS = 10
GO


RESTORE DATABASE ax_molniya
 FROM  DISK = '\\192.168.0.38\Backup\AXAPTA\ax_molniya_backup_201706041500.bak'
 WITH  FILE = 4,  MOVE N'FG_PRIMARY' TO N'e:\ax_molniya.mdf',  MOVE N'FG_CUSTTRANSACTION' TO N'e:\ax_molniya_1.ndf',  MOVE N'FG_INVENTCLOSING' TO N'e:\ax_molniya_2.ndf',  MOVE N'FG_INVENTREMAINS' TO N'e:\ax_molniya_3.ndf',  MOVE N'FG_INVENTTRANSACTION' TO N'e:\ax_molniya_4.ndf',  MOVE N'FG_VENDTRANSACTION' TO N'e:\ax_molniya_5.ndf',  MOVE N'FG_INDEXES' TO N'e:\ax_molniya_6.ndf',  MOVE N'ax_molniya_Log' TO N'e:\ax_molniya_7.LDF',STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ROLLBACK_UNDO_ax_molniyar.BAK',  NOUNLOAD,  STATS = 10
GO

RESTORE DATABASE [ax_molniya] FROM DISK = N'\\192.168.0.38\Backup\AXAPTA\ax_molniya_backup_201705291300.bak' WITH FILE = 1, MOVE N'FG_PRIMARY' TO N'e:\ax_molniya.mdf', MOVE N'FG_CUSTTRANSACTION' TO N'e:\ax_molniya_1.ndf', MOVE N'FG_INVENTCLOSING' TO N'e:\ax_molniya_2.ndf', MOVE N'FG_INVENTREMAINS' TO N'e:\ax_molniya_3.ndf', MOVE N'FG_INVENTTRANSACTION' TO N'e:\ax_molniya_4.ndf', MOVE N'FG_VENDTRANSACTION' TO N'e:\ax_molniya_5.ndf', MOVE N'FG_INDEXES' TO N'e:\ax_molniya_6.ndf', MOVE N'ax_molniya_Log' TO N'e:\ax_molniya_7.LDF', NORECOVERY, NOUNLOAD, STATS = 10

RESTORE DATABASE ax_molniya

FROM DISK = '\\192.168.0.38\Backup\AXAPTA\ax_molniya_backup_201706041500.bak'

WITH FILE = 4, MOVE N'FG_PRIMARY' TO N'e:\ax_molniya.mdf', MOVE N'FG_CUSTTRANSACTION' TO N'e:\ax_molniya_1.ndf', MOVE N'FG_INVENTCLOSING' TO N'e:\ax_molniya_2.ndf', MOVE N'FG_INVENTREMAINS' TO N'e:\ax_molniya_3.ndf', MOVE N'FG_INVENTTRANSACTION' TO N'e:\ax_molniya_4.ndf', MOVE N'FG_VENDTRANSACTION' TO N'e:\ax_molniya_5.ndf', MOVE N'FG_INDEXES' TO N'e:\ax_molniya_6.ndf', MOVE N'ax_molniya_Log' TO N'e:\ax_molniya_7.LDF',STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ROLLBACK_UNDO_ax_molniyar.BAK', NOUNLOAD, STATS = 10

Как удалить репликации у базы MSSQL.

30.05.2017, Categories Mssql, Моё Избарнное Comments Нет комментариев

exec sp_removedbreplication 'ax_molniya'
go
exec sp_dboption 'ax_molniya','published',false
go
exec sp_dboption 'ax_molniya','merge publish',false
go

exec sp_removedbreplication 'ax_molniya'

exec sp_dboption 'ax_molniya','published',false

exec sp_dboption 'ax_molniya','merge publish',false

Как создать хранимую процедуру для мониторинга заданий (job) в MSSQL. Хранимая процедура для PRTG

24.05.2017, Categories Mssql, PRTG Comments Нет комментариев

Была задача создать хранимую процедуру что бы мониторить разные задания(джобы, job) в MSSQL. Это доробатаная процедура посравнению с Сенсор для prtg. Сенсор для задания sql

USE [msdb]
GO

/****** Object:  StoredProcedure [dbo].[alertjob]    Script Date: 05/24/2017 10:24:34 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[alertjob]
@name VARCHAR(max)
AS
BEGIN
SELECT TOP 100
	    
	    b.[message], b.run_date,  b.run_time

into #t	        
--SELECT *
FROM   
 dbo.sysjobs a
       FULL OUTER JOIN dbo.sysjobhistory b
            ON  a.job_id = b.job_id
                AND a.[enabled] = '1'
WHERE
--CONVERT(VARCHAR(20),b.run_date,5)  = (select CONVERT (date, SYSDATETIME())) and
a.name IN ('LSRestore_s0085_ZUP_MOLL') and
run_date=(SELECT MAX(run_date) FROM dbo.sysjobs a

       FULL OUTER JOIN dbo.sysjobhistory b
            ON  a.job_id = b.job_id
                AND a.[enabled] = '1')



--b.run_status  IN ('0')

     ORDER BY
       b.run_date   DESC,
       b.run_time  DESC
     SELECT       
      COUNT(
      	run_date   
      	  	) AS rezult  
      FROM #t 
    WHERE 
          run_time=(SELECT MAX(run_time) FROM #t) AND   [message] LIKE '%failed%' 
END

GO

USE [msdb]

/****** Object: StoredProcedure [dbo].[alertjob] Script Date: 05/24/2017 10:24:34 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[alertjob]

@name VARCHAR(max)

BEGIN

SELECT TOP 100

b.[message], b.run_date, b.run_time

into #t

--SELECT *

FROM

dbo.sysjobs a

FULL OUTER JOIN dbo.sysjobhistory b

ON a.job_id = b.job_id

AND a.[enabled] = '1'

WHERE

--CONVERT(VARCHAR(20),b.run_date,5) = (select CONVERT (date, SYSDATETIME())) and

a.name IN ('LSRestore_s0085_ZUP_MOLL') and

run_date=(SELECT MAX(run_date) FROM dbo.sysjobs a

FULL OUTER JOIN dbo.sysjobhistory b

ON a.job_id = b.job_id

AND a.[enabled] = '1')

--b.run_status IN ('0')

ORDER BY

b.run_date DESC,

b.run_time DESC

SELECT

COUNT(

run_date

) AS rezult

FROM #t

WHERE

run_time=(SELECT MAX(run_time) FROM #t) AND [message] LIKE '%failed%'

END

EXEC dbo.alertjob  @name='имя задания'

1	EXEC dbo.alertjob @name='имя задания'

Сам сенсор в PRTG

Установка диспетчера виртуальных машин System Center 2016 — шаг за шагом (руководство по быстрому запуску)

17.03.2017, Categories Windows Comments Нет комментариев

В следующей статье я расскажу об основной установке System Center Virtual Machine Manager 2016. Вы должны использовать это в качестве шаблона для эксперимента или доказательства концепции (POC). Вы должны изменить развертывание, чтобы удовлетворить потребности клиентов, если вы хотите установить SCVMM в производственной среде.

Немного справочной информации. Я использую 2 сервера 1 x VMM Server (со всеми установленными ролями и 1 x SQL 2014 SP1 Server). На обоих серверах работает сервер 2016.

В AD создайте следующие учетные записи и группы в соответствии с соглашением об именах:

Учетная запись учетной записи службы DOMAIN \ Svc-SCVMM SCVMM
DOMAIN \ Svc-SCVMMAdmin SCVMM Учетная запись RunAs для управления хостами
Учетная запись DOMAIN \ Svc-SQL SQL
DOMAIN \ Svc-SCVMMAdmins Администраторы SCVMM Глобальная группа безопасности

Добавьте учетные записи « Svc-SCVMM » и « Svc-SCVMMAdmin » в глобальную группу « Svc-SCVMMAdmins » и добавьте учетные записи пользователей домена для себя и своей команды в группу Svc-SCVMMAdmins .

На сервере VMM требуются следующие предпосылки:

Набор автоматизированного развертывания Windows (ADK), доступный здесь, со следующими функциями
- Инструменты развертывания
- Windows PE
Утилиты командной строки SQL 2014 доступны здесь

Установка диспетчера виртуальных машин System Center 2016

После установки всех необходимых предварительных условий откройте носитель SCVMM и извлеките его в выбранном месте, а затем запустите файл setup.exe .

Нажмите « Установить» .

Как упоминалось выше, мы будем устанавливать одно боковое решение для VMM, поэтому выбираем сервер управления VMM и консоль VMM.

При регистрации продукта введите ключ продукта для лицензионной версии, иначе Eval будет установлен с тайм-аутом. Нажмите « Далее» .

Примите лицензионное соглашение и нажмите « Далее» .

Нажмите « Далее» .

Настройте использование Microsoft Update или отметьте, нажмите « Далее» .

Примите или измените путь установки по умолчанию и нажмите « Далее».

Если вы получаете какие-либо предпосылки ошибок — устраните их. Если вы получаете какие-либо предупреждения, понимайте их и нажмите « Далее», чтобы продолжить.

На экране « Конфигурация базы данных » введите имя вашего сервера базы данных SQL и оставьте порт пустым . Вы можете оставить поле «использовать следующие учетные данные» пустым, если вы устанавливаете его на локальный SQL-сервер. Вы можете ввести учетные данные здесь, чтобы подключиться к удаленному SQL-серверу, если ваша учетная запись пользователя, на которую вы работаете, не имеет достаточных прав на экземпляр для создания базы данных. В имени экземпляра — щелкните раскрывающееся меню, чтобы выбрать экземпляр, который вы хотите установить. Убедитесь, что «Новая база данных» отмечена, и используйте имя по умолчанию или измените его в соответствии со стандартами именования. Нажмите « Далее», когда это окно будет завершено.

На экране Конфигурация учетной записи введите учетную запись домена для учетной записи службы SCVMM, которую мы создали ранее ( DOMAIN \ Sbv-SCVMM ). Оставьте значение по умолчанию для хранения ключей шифрования локально для этого простого развертывания. Нажмите « Далее» .

На экране конфигурации порта примите значения по умолчанию и нажмите « Далее» .

На экране конфигурации библиотеки измените путь к библиотеке или примите местоположение по умолчанию и нажмите « Далее» .

Нажмите « Установить» .

Программа установки установит все роли и завершит .

Развертывание агентов на хостах Hyper-V

Откройте консоль System Center Virtual Machine Manager 2016.

Подключитесь к серверу SCVMM.

В левой нижней панели консоли выберите « Ткань ».

В списке папок щелкните правой кнопкой мыши « Все узлы » и выберите « Создать группу хостов ».

Назовите вашу пользовательскую группу хостов, я использую « Сервер 2016 », поскольку на моих хостах работает Сервер 2016.

Щелкните правой кнопкой мыши на своей новой группе хостов и выберите « Добавить хосты и кластеры Hyper-V »

На экране «Расположение ресурса» выберите первый маркер надежного компьютера домена AD.

На экране «Учетные данные» нажмите « Обзор» .

Выберите « Создать учетную запись запуска от имени ».

На экране «Общие» введите имя « учетной записи администратора хоста Hyper-V »,

Введите имя домена DOMAIN \ имя учетной записи AD, которая имеет доступ администратора к вашим серверам Hyper-V. Эта учетная запись будет использоваться для администрирования Host и VM гостей. Для целей POC мы будем использовать учетную запись DOMAIN \ Svc-SCVMMAdmin .

После ввода пароля и принятия новой учетной записи мы вернемся к экрану учетных данных с нашей существующей учетной записью RunAs. Нажмите « Готово» > « ОК» > « Далее» .

Введите имена компьютеров серверов Hyper-V, которые вы хотите управлять. Убедитесь, что глобальная группа DOMAIN \ Svc-SCVMMAdmins является членом локальной группы администраторов на всех серверах Hyper-V, чтобы мы могли управлять ими. Убедитесь, что вы НЕ пропускаете проверку AD, так как в будущем это позволит сэкономить много сердечных болей, если учетные данные проверены. Нажмите « Далее» .

Выберите все обнаруженные серверы Hyper-V и нажмите « Далее» .

Назначьте обнаруженные хосты группе « Сервер 2016 ».

Нажмите « Далее», «Готово» .

Будет создано задание на развертывание агента SCVMM на хостах Hyper-V. Если агент не может выполнить развертывание, убедитесь, что на хостах Hyper-V открыты соответствующие порты брандмауэра. См. Эту статью: https://technet.microsoft.com/en-gb/library/gg710871.aspx

Вы можете игнорировать ошибку, связанную с вводом-выводом Multipath.

Щелкните «VMs and Services» и щелкните « Server 2016 », чтобы увидеть добавленные узлы в инфраструктуру VMM и что состояние « ОК ».

Если это так, я кого-то убиваю в Microsoft ..
https://social.technet.microsoft.com/Forums/systemcenter/en-US/004dc94c-4e4b-444c-8c66-3c8106fd5395/install-virtual-machine-manager-server-failed-at-the-step-granting-database-access?forum=virtualmachingmgrsetup

Как узнать кому отправлялись письма из mssql

02.03.2017, Categories Mssql Comments Нет комментариев

msdb.dbo.sysmail_allitems - просмотр всех сообщений;
msdb.dbo.sysmail_sentitems – просмотр только отправленных сообщений;
msdb.dbo.sysmail_unsentitems - просмотр неотправленных сообщений;
msdb.dbo.sysmail_faileditems – просмотр сообщений с ошибками;
msdb.dbo.sysmail_event_log – журнал работы компонента Database Mail.
Например, для просмотра всех отправленных сообщений можно использовать следующий запрос:
SELECT sent_date AS [Дата отправки письма], 
	   send_request_user AS [Кем отправлено письмо],
	   recipients AS [Кому отправлено письмо], 
	   subject AS [Тема письма], 
	   body AS [Текст письма], 
	   file_attachments AS [Отправленные файлы], 
	   query AS [SQL запрос]
 FROM msdb.dbo.sysmail_sentitems

msdb.dbo.sysmail_allitems - просмотр всех сообщений;

msdb.dbo.sysmail_sentitems – просмотр только отправленных сообщений;

msdb.dbo.sysmail_unsentitems - просмотр неотправленных сообщений;

msdb.dbo.sysmail_faileditems – просмотр сообщений с ошибками;

msdb.dbo.sysmail_event_log – журнал работы компонента Database Mail.

Например, для просмотра всех отправленных сообщений можно использовать следующий запрос:

SELECT sent_date AS [Дата отправки письма],

send_request_user AS [Кем отправлено письмо],

recipients AS [Кому отправлено письмо],

subject AS [Тема письма],

body AS [Текст письма],

file_attachments AS [Отправленные файлы],

query AS [SQL запрос]

FROM msdb.dbo.sysmail_sentitems

Хранимая процедура для архивирования журнала при достижении @danger_value.

11.01.2017, Categories Mssql Comments Нет комментариев

Архивирования журнала при достижении @danger_value. Идея в том что лог будет усекаться при достижении 7 гб. Это сделано так как настроен лог шипинг.

eclare @tran_log_space_usage table( 
        database_name sysname
,       log_size_mb float
,       log_space_used float
,       status int
); 
declare @log_space_used float	-- Доля (в процентах) файла журнала, в настоящее время заполненная
declare @danger_value int		-- Значение в Мб, при достижение которого архивируется журнал
declare @job_start_name sysname -- Название задания в Агенте MSSQL 

set @danger_value = 7000
set @job_start_name = N'SSAS_LSBackup_ax_molniya'

insert into @tran_log_space_usage 
exec('DBCC SQLPERF ( LOGSPACE )') ; 

set @log_space_used = (
select (log_space_used * log_size_mb / 100)  from @tran_log_space_usage
where database_name = DB_NAME() 
)

if (@log_space_used > @danger_value)
begin
EXEC msdb.dbo.sp_start_job @job_start_name;

eclare @tran_log_space_usage table(

database_name sysname

, log_size_mb float

, log_space_used float

, status int

);

declare @log_space_used float -- Доля (в процентах) файла журнала, в настоящее время заполненная

declare @danger_value int -- Значение в Мб, при достижение которого архивируется журнал

declare @job_start_name sysname -- Название задания в Агенте MSSQL

set @danger_value = 7000

set @job_start_name = N'SSAS_LSBackup_ax_molniya'

insert into @tran_log_space_usage

exec('DBCC SQLPERF ( LOGSPACE )') ;

set @log_space_used = (

select (log_space_used * log_size_mb / 100) from @tran_log_space_usage

where database_name = DB_NAME()

)

if (@log_space_used > @danger_value)

begin

EXEC msdb.dbo.sp_start_job @job_start_name;

Доработанный скрипт . Теперь письмо при ходит если лог вырос за час между 10 гб и 20 гб. Если больше то отправится письмо и смс:

declare @tran_log_space_usage table( 
        database_name sysname
,       log_size_mb float
,       log_space_used float
,       status int
); 
declare @log_space_used float	-- Доля (в процентах) файла журнала, в настоящее время заполненная
declare @danger_value int		-- Значение в Мб, при достижение которого архивируется журнал
declare @job_start_name sysname -- Название задания в Агенте MSSQL 

set @danger_value = 10
set @job_start_name = N'SSAS_LSBackup_ax_molniya'

insert into @tran_log_space_usage 
exec('DBCC SQLPERF ( LOGSPACE )') ; 

set @log_space_used = (
select (log_space_used * log_size_mb / 100)  from @tran_log_space_usage
where database_name = DB_NAME() 
)
if ( @log_space_used BETWEEN 10000 AND 20000)
begin
EXEC msdb.dbo.sp_start_job @job_start_name;
 EXEC	[master].[dbo].[rkur_send_job_status]
		@job_name = N'backup_log_dv_SSAS_LSBackup_ax_molniya',
		@job_desc = N'SSAS.Архивирование журнала при достижении @danger_value. по простому: Начал сильно расти лог,обрати внимание',
		@job_status = 'OK',
		@dbmail_@recipients = N'event.messages@ctmol.ru';
end
ELSE 
	
	if	
		 ( @log_space_used > 20000 )
		BEGIN 
		 	EXEC msdb.dbo.sp_start_job @job_start_name;
	 EXEC	[master].[dbo].[rkur_send_job_status]
		@job_name = N'backup_log_dv_SSAS_LSBackup_ax_molniya',
		@job_desc = N'SSAS.Архивирование журнала при достижении @danger_value. по простому: Начал сильно расти лог,обрати внимание',
		@job_status = 'OK',
		@dbmail_@recipients = N'event.messages@ctmol.ru';
exec SendSMS 'S0030.SSAS.Архивирование журнала при достижении @danger_value. по простому: Начал сильно расти лог,обрати внимание', '795********';
exec SendSMS 'S0030.SSAS.Архивирование журнала при достижении @danger_value. по простому: Начал сильно расти лог,обрати внимание', '791********';
exec SendSMS 'S0030.SSAS.Архивирование журнала при достижении @danger_value. по простому: Начал сильно расти лог,обрати внимание', '792********';
exec SendSMS 'S0030.SSAS.Архивирование журнала при достижении @danger_value. по простому: Начал сильно расти лог,обрати внимание', '795********';
end

declare @tran_log_space_usage table(

database_name sysname

, log_size_mb float

, log_space_used float

, status int

);

declare @log_space_used float -- Доля (в процентах) файла журнала, в настоящее время заполненная

declare @danger_value int -- Значение в Мб, при достижение которого архивируется журнал

declare @job_start_name sysname -- Название задания в Агенте MSSQL

set @danger_value = 10

set @job_start_name = N'SSAS_LSBackup_ax_molniya'

insert into @tran_log_space_usage

exec('DBCC SQLPERF ( LOGSPACE )') ;

set @log_space_used = (

select (log_space_used * log_size_mb / 100) from @tran_log_space_usage

where database_name = DB_NAME()

)

if ( @log_space_used BETWEEN 10000 AND 20000)

begin

EXEC msdb.dbo.sp_start_job @job_start_name;

EXEC [master].[dbo].[rkur_send_job_status]

@job_name = N'backup_log_dv_SSAS_LSBackup_ax_molniya',

@job_desc = N'SSAS.Архивирование журнала при достижении @danger_value. по простому: Начал сильно расти лог,обрати внимание',

@job_status = 'OK',

@dbmail_@recipients = N'event.messages@ctmol.ru';

end

ELSE

( @log_space_used > 20000 )

BEGIN

EXEC msdb.dbo.sp_start_job @job_start_name;

EXEC [master].[dbo].[rkur_send_job_status]

@job_name = N'backup_log_dv_SSAS_LSBackup_ax_molniya',

@job_status = 'OK',

@dbmail_@recipients = N'event.messages@ctmol.ru';

exec SendSMS 'S0030.SSAS.Архивирование журнала при достижении @danger_value. по простому: Начал сильно расти лог,обрати внимание', '795********';

exec SendSMS 'S0030.SSAS.Архивирование журнала при достижении @danger_value. по простому: Начал сильно расти лог,обрати внимание', '791********';

exec SendSMS 'S0030.SSAS.Архивирование журнала при достижении @danger_value. по простому: Начал сильно расти лог,обрати внимание', '792********';

end

Новая версия с Отправкой размера лога

-- =============================================
-- Author:		Руслан Курьянов
-- Create date: 2014-02-19
-- Version : 0.1
-- Description:	Архивирование журнала при достижении @danger_value
-- =============================================

declare @tran_log_space_usage table( 
        database_name sysname
,       log_size_mb float
,       log_space_used float
,       status int
); 
declare @log_space_used float 	-- Доля (в процентах) файла журнала, в настоящее время заполненная
declare @danger_value int		-- Значение в Мб, при достижение которого архивируется журнал
declare @job_start_name sysname -- Название задания в Агенте MSSQL 
declare @name VARCHAR(MAX)
declare @name2 VARCHAR(MAX)
declare @123 VARCHAR(MAX)

set @danger_value = 10
set @job_start_name = N'SSAS_LSBackup_ax_molniya'

insert into @tran_log_space_usage 
exec('DBCC SQLPERF ( LOGSPACE )') ; 

set @log_space_used = (
select (log_space_used * log_size_mb / 100)  from @tran_log_space_usage
where database_name = DB_NAME() 
)
PRINT @log_space_used
if ( @log_space_used BETWEEN 10000 AND 20000)
begin
EXEC msdb.dbo.sp_start_job @job_start_name;
set @123 =  CAST(@log_space_used AS VARCHAR(256))
set @name = 'SSAS.Архивирование журнала при достижении @danger_value. по простому: Лог вырас больше 10 гб но меньше 20 гб Размер лога ='+@123+' Мб '
 EXEC	[master].[dbo].[rkur_send_job_status]
		@job_name = N'backup_log_dv_SSAS_LSBackup_ax_molniya',
		@job_desc = @name,
		@job_status = 'OK',
		@dbmail_@recipients = N'event.messages@ctmol.ru';
end
ELSE 
	
	if	
		 ( @log_space_used > 20000 )
		BEGIN 
		 	EXEC msdb.dbo.sp_start_job @job_start_name;
set @123 =  CAST(@log_space_used AS VARCHAR(256))		 	
set @name2 =  'S0030.SSAS.Архивирование журнала при достижении @danger_value. по простому: Начал сильно расти лог,обрати внимание. Размер лога ='+@123+'Мб'
	 EXEC	[master].[dbo].[rkur_send_job_status]
		@job_name = N'backup_log_dv_SSAS_LSBackup_ax_molniya',
		@job_desc =  @name2,
		@job_status = 'OK',
		@dbmail_@recipients = N'event.messages@ctmol.ru';
exec SendSMS @name2 , '7950******';
exec SendSMS @name2, '7912******';
exec SendSMS @name2, '792******';
exec SendSMS @name2, '795******';
end

-- =============================================

-- Author: Руслан Курьянов

-- Create date: 2014-02-19

-- Version : 0.1

-- Description: Архивирование журнала при достижении @danger_value

-- =============================================

declare @tran_log_space_usage table(

database_name sysname

, log_size_mb float

, log_space_used float

, status int

);

declare @log_space_used float -- Доля (в процентах) файла журнала, в настоящее время заполненная

declare @danger_value int -- Значение в Мб, при достижение которого архивируется журнал

declare @job_start_name sysname -- Название задания в Агенте MSSQL

declare @name VARCHAR(MAX)

declare @name2 VARCHAR(MAX)

declare @123 VARCHAR(MAX)

set @danger_value = 10

set @job_start_name = N'SSAS_LSBackup_ax_molniya'

insert into @tran_log_space_usage

exec('DBCC SQLPERF ( LOGSPACE )') ;

set @log_space_used = (

select (log_space_used * log_size_mb / 100) from @tran_log_space_usage

where database_name = DB_NAME()

)

PRINT @log_space_used

if ( @log_space_used BETWEEN 10000 AND 20000)

begin

EXEC msdb.dbo.sp_start_job @job_start_name;

set @123 = CAST(@log_space_used AS VARCHAR(256))

set @name = 'SSAS.Архивирование журнала при достижении @danger_value. по простому: Лог вырас больше 10 гб но меньше 20 гб Размер лога ='+@123+' Мб '

EXEC [master].[dbo].[rkur_send_job_status]

@job_name = N'backup_log_dv_SSAS_LSBackup_ax_molniya',

@job_desc = @name,

@job_status = 'OK',

@dbmail_@recipients = N'event.messages@ctmol.ru';

end

ELSE

( @log_space_used > 20000 )

BEGIN

EXEC msdb.dbo.sp_start_job @job_start_name;

set @123 = CAST(@log_space_used AS VARCHAR(256))

set @name2 = 'S0030.SSAS.Архивирование журнала при достижении @danger_value. по простому: Начал сильно расти лог,обрати внимание. Размер лога ='+@123+'Мб'

EXEC [master].[dbo].[rkur_send_job_status]

@job_name = N'backup_log_dv_SSAS_LSBackup_ax_molniya',

@job_desc = @name2,

@job_status = 'OK',

@dbmail_@recipients = N'event.messages@ctmol.ru';

exec SendSMS @name2 , '7950******';

exec SendSMS @name2, '7912******';

exec SendSMS @name2, '792******';

exec SendSMS @name2, '795******';

end

Отправить СМС из MSSQL

11.01.2017, Categories Mssql Comments Нет комментариев

Есть настроенная программа для отправки смс через usb modem, Программа для отправки смс используется http://nhutils.ru/blog/nhsms/download/
http://www.adminbd.ru/prtg-sms-%d0%b0%d0%bb%d0%b5%d1%80%d1%82-%d0%be%d1%82-prtg-%d0%bd%d0%b0-%d1%82%d0%b5%d0%bb%d0%b5%d1%84%d0%be%d0%bd-%d1%87%d0%b5%d1%80%d0%b5%d0%b7-usb-modem/

Хранимая процедура для отправки:

USE [ax_molniya]
GO

/****** Object:  StoredProcedure [dbo].[SendSMS]    Script Date: 01/11/2017 10:33:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[SendSMS] (@smsText varchar(8000), @phoneNumber varchar(50))
as

declare @buildStr varchar(8000), 
		@fileName varchar(255), 
		@folderName varchar(255), 
		@query varchar(8000)


set @query = 'select ''[1]'' union all select ''Phone=' 
			+ @phoneNumber + ''' union all select ''Alert=0'' union all select ''' 
			+ 'Text = ' + replace(replace(@smsText, char(13), ' '), char(10), ' ') 
			+ ''' union all select ''ValidityPeriod=1d'''
print @query

set @fileName = cast(NEWID() as nvarchar(100)) + '.txt'
set @folderName = '\\s0042\Outgoing'



set @buildStr = 'bcp "' + @query + '" queryout "' + @folderName + '\' + @fileName + '" -w -T -t'

exec xp_cmdshell @buildStr

GO

USE [ax_molniya]

/****** Object: StoredProcedure [dbo].[SendSMS] Script Date: 01/11/2017 10:33:15 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE procedure [dbo].[SendSMS] (@smsText varchar(8000), @phoneNumber varchar(50))

declare @buildStr varchar(8000),

@fileName varchar(255),

@folderName varchar(255),

@query varchar(8000)

set @query = 'select ''[1]'' union all select ''Phone='

+ @phoneNumber + ''' union all select ''Alert=0'' union all select '''

+ 'Text = ' + replace(replace(@smsText, char(13), ' '), char(10), ' ')

+ ''' union all select ''ValidityPeriod=1d'''

print @query

set @fileName = cast(NEWID() as nvarchar(100)) + '.txt'

set @folderName = '\\s0042\Outgoing'

set @buildStr = 'bcp "' + @query + '" queryout "' + @folderName + '\' + @fileName + '" -w -T -t'

exec xp_cmdshell @buildStr

Что бы отправить смс

exec SendSMS 'Текст сообщения', '791******'

1	exec SendSMS 'Текст сообщения', '791******'

Как это смотрится в задании:

Как узнать в какой файловой группе больше всего записи и чтения.

06.12.2016, Categories Mssql Comments Нет комментариев

Задача распределить файловые группы на разные диски. Чем больше операций чтения и записи в файловой группе тем быстрее будут выделены диски и RAID.

Есть база весит 1.5 тб. В ней файловые группы:

Нужно подумать как их распределить.

База TempDb у нас находится на ssd в зеркале.Размер диска 370 гб. TempDb разбит на файлы по рекомендации майкрософт

Сначала был выполнен скрипт накопление статистики ожидания из статьи https://habrahabr.ru/post/216309/

WITH [Waits] AS
    (SELECT
        [wait_type],
        [wait_time_ms] / 1000.0 AS [WaitS],
        ([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],
        [signal_wait_time_ms] / 1000.0 AS [SignalS],
        [waiting_tasks_count] AS [WaitCount],
        100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],
        ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]
    FROM sys.dm_os_wait_stats
    WHERE [wait_type] NOT IN (
        N'BROKER_EVENTHANDLER',         N'BROKER_RECEIVE_WAITFOR',
        N'BROKER_TASK_STOP',            N'BROKER_TO_FLUSH',
        N'BROKER_TRANSMITTER',          N'CHECKPOINT_QUEUE',
        N'CHKPT',                       N'CLR_AUTO_EVENT',
        N'CLR_MANUAL_EVENT',            N'CLR_SEMAPHORE',
        N'DBMIRROR_DBM_EVENT',          N'DBMIRROR_EVENTS_QUEUE',
        N'DBMIRROR_WORKER_QUEUE',       N'DBMIRRORING_CMD',
        N'DIRTY_PAGE_POLL',             N'DISPATCHER_QUEUE_SEMAPHORE',
        N'EXECSYNC',                    N'FSAGENT',
        N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',
        N'HADR_CLUSAPI_CALL',           N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
        N'HADR_LOGCAPTURE_WAIT',        N'HADR_NOTIFICATION_DEQUEUE',
        N'HADR_TIMER_TASK',             N'HADR_WORK_QUEUE',
        N'KSOURCE_WAKEUP',              N'LAZYWRITER_SLEEP',
        N'LOGMGR_QUEUE',                N'ONDEMAND_TASK_QUEUE',
        N'PWAIT_ALL_COMPONENTS_INITIALIZED',
        N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',
        N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',
        N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',
        N'SERVER_IDLE_CHECK',           N'SLEEP_BPOOL_FLUSH',
        N'SLEEP_DBSTARTUP',             N'SLEEP_DCOMSTARTUP',
        N'SLEEP_MASTERDBREADY',         N'SLEEP_MASTERMDREADY',
        N'SLEEP_MASTERUPGRADED',        N'SLEEP_MSDBSTARTUP',
        N'SLEEP_SYSTEMTASK',            N'SLEEP_TASK',
        N'SLEEP_TEMPDBSTARTUP',         N'SNI_HTTP_ACCEPT',
        N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',
        N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
        N'SQLTRACE_WAIT_ENTRIES',       N'WAIT_FOR_RESULTS',
        N'WAITFOR',                     N'WAITFOR_TASKSHUTDOWN',
        N'WAIT_XTP_HOST_WAIT',          N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',
        N'WAIT_XTP_CKPT_CLOSE',         N'XE_DISPATCHER_JOIN',
        N'XE_DISPATCHER_WAIT',          N'XE_TIMER_EVENT')
    )
SELECT
    [W1].[wait_type] AS [WaitType],
    CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],
    CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],
    CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],
    [W1].[WaitCount] AS [WaitCount],
    CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],
    CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],
    CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],
    CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]
FROM [Waits] AS [W1]
INNER JOIN [Waits] AS [W2]
    ON [W2].[RowNum] <= [W1].[RowNum]
GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],
    [W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]
HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold
GO

WITH [Waits] AS

(SELECT

[wait_type],

[wait_time_ms] / 1000.0 AS [WaitS],

([wait_time_ms] - [signal_wait_time_ms]) / 1000.0 AS [ResourceS],

[signal_wait_time_ms] / 1000.0 AS [SignalS],

[waiting_tasks_count] AS [WaitCount],

100.0 * [wait_time_ms] / SUM ([wait_time_ms]) OVER() AS [Percentage],

ROW_NUMBER() OVER(ORDER BY [wait_time_ms] DESC) AS [RowNum]

FROM sys.dm_os_wait_stats

WHERE [wait_type] NOT IN (

N'BROKER_EVENTHANDLER', N'BROKER_RECEIVE_WAITFOR',

N'BROKER_TASK_STOP', N'BROKER_TO_FLUSH',

N'BROKER_TRANSMITTER', N'CHECKPOINT_QUEUE',

N'CHKPT', N'CLR_AUTO_EVENT',

N'CLR_MANUAL_EVENT', N'CLR_SEMAPHORE',

N'DBMIRROR_DBM_EVENT', N'DBMIRROR_EVENTS_QUEUE',

N'DBMIRROR_WORKER_QUEUE', N'DBMIRRORING_CMD',

N'DIRTY_PAGE_POLL', N'DISPATCHER_QUEUE_SEMAPHORE',

N'EXECSYNC', N'FSAGENT',

N'FT_IFTS_SCHEDULER_IDLE_WAIT', N'FT_IFTSHC_MUTEX',

N'HADR_CLUSAPI_CALL', N'HADR_FILESTREAM_IOMGR_IOCOMPLETION',

N'HADR_LOGCAPTURE_WAIT', N'HADR_NOTIFICATION_DEQUEUE',

N'HADR_TIMER_TASK', N'HADR_WORK_QUEUE',

N'KSOURCE_WAKEUP', N'LAZYWRITER_SLEEP',

N'LOGMGR_QUEUE', N'ONDEMAND_TASK_QUEUE',

N'PWAIT_ALL_COMPONENTS_INITIALIZED',

N'QDS_PERSIST_TASK_MAIN_LOOP_SLEEP',

N'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP',

N'REQUEST_FOR_DEADLOCK_SEARCH', N'RESOURCE_QUEUE',

N'SERVER_IDLE_CHECK', N'SLEEP_BPOOL_FLUSH',

N'SLEEP_DBSTARTUP', N'SLEEP_DCOMSTARTUP',

N'SLEEP_MASTERDBREADY', N'SLEEP_MASTERMDREADY',

N'SLEEP_MASTERUPGRADED', N'SLEEP_MSDBSTARTUP',

N'SLEEP_SYSTEMTASK', N'SLEEP_TASK',

N'SLEEP_TEMPDBSTARTUP', N'SNI_HTTP_ACCEPT',

N'SP_SERVER_DIAGNOSTICS_SLEEP', N'SQLTRACE_BUFFER_FLUSH',

N'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',

N'SQLTRACE_WAIT_ENTRIES', N'WAIT_FOR_RESULTS',

N'WAITFOR', N'WAITFOR_TASKSHUTDOWN',

N'WAIT_XTP_HOST_WAIT', N'WAIT_XTP_OFFLINE_CKPT_NEW_LOG',

N'WAIT_XTP_CKPT_CLOSE', N'XE_DISPATCHER_JOIN',

N'XE_DISPATCHER_WAIT', N'XE_TIMER_EVENT')

)

SELECT

[W1].[wait_type] AS [WaitType],

CAST ([W1].[WaitS] AS DECIMAL (16, 2)) AS [Wait_S],

CAST ([W1].[ResourceS] AS DECIMAL (16, 2)) AS [Resource_S],

CAST ([W1].[SignalS] AS DECIMAL (16, 2)) AS [Signal_S],

[W1].[WaitCount] AS [WaitCount],

CAST ([W1].[Percentage] AS DECIMAL (5, 2)) AS [Percentage],

CAST (([W1].[WaitS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgWait_S],

CAST (([W1].[ResourceS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgRes_S],

CAST (([W1].[SignalS] / [W1].[WaitCount]) AS DECIMAL (16, 4)) AS [AvgSig_S]

FROM [Waits] AS [W1]

INNER JOIN [Waits] AS [W2]

ON [W2].[RowNum] <= [W1].[RowNum]

GROUP BY [W1].[RowNum], [W1].[wait_type], [W1].[WaitS],

[W1].[ResourceS], [W1].[SignalS], [W1].[WaitCount], [W1].[Percentage]

HAVING SUM ([W2].[Percentage]) - [W1].[Percentage] < 95; -- percentage threshold

Результат:

Расшифровка »

505: CXPACKET
Означает параллелизм, но не обязательно в нем проблема. Поток-координатор в параллельном запросе всегда накапливает эти ожидания. Если параллельные потоки не заняты работой или один из потоков заблокирован, то ожидающие потоки также накапливают ожидание CXPACKET, что приводит к более быстрому накоплению статистики по этому типу — в этом и проблема. Один поток может иметь больше работы, чем остальные, и по этой причине весь запрос блокируется, пока долгий поток не закончит свою работу. Если этот тип ожидания совмещен с большими цифрами ожидания PAGEIOLATCH_XX, то это может быть сканирование больших таблиц по причине некорректных некластерных индексов или из-за плохого плана выполнения запроса. Если это не является причиной, вы можете попробовать применение опции MAXDOP со значениями 4, 2, или 1 для проблемных запросов или для всего экземпляра сервера (устанавливается на сервере параметром «max degree of parallelism»). Если ваша система основана на схеме NUMA, попробуйте установить MAXDOP в значение, равное количеству процессоров в одном узле NUMA для того, чтобы определить, не в этом ли проблема. Вам также нужно определить эффект от установки MAXDOP на системах со смешанной нагрузкой. Если честно, я бы поиграл с параметром «cost threshold for parallelism» (поднял его до 25 для начала), прежде чем снижать значение MAXDOP для всего экземпляра. И не забывайте про регулятор ресурсов (Resource Governor) в Enterprise версии SQL Server 2008, который позволяет установить количество процессоров для конкретной группы соединений с сервером.
304: PAGEIOLATCH_XX
Вот тут SQL Server ждет чтения страницы данных с диска в память. Этот тип ожидания может указывать на проблему в системе ввода/вывода (что является первой реакцией на этот тип ожидания), но почему система ввода/вывода должна обслуживать такое количество чтений? Возможно, давление оказывает буферный пул/память (недостаточно памяти для типичной нагрузки), внезапное изменение в планах выполнения, приводящее к большим параллельным сканированиям вместо поиска, раздувание кэша планов или некоторые другие причины. Не стоит считать, что основная проблема в системе ввода/вывода.
275: ASYNC_NETWORK_IO
Здесь SQL Server ждет, пока клиент закончит получать данные. Причина может быть в том, что клиент запросил слишком большое количество данных или просто получает их ооочень медленно из-за плохого кода — я почти никогда не не видел, чтобы проблема заключалась в сети. Клиенты часто читают по одной строке за раз — так называемый RBAR или «строка за агонизирующей строкой»(Row-By-Agonizing-Row) — вместо того, чтобы закешировать данные на клиенте и уведомить SQL Server об окончании чтения немедленно.
112: WRITELOG
Подсистема управления логом ожидает записи лога на диск. Как правило, означает, что система ввода/ввода не может обеспечить своевременную запись всего объема лога, но на высоконагруженных системах это может быть вызвано общими ограничениями записи лога, что может означать, что вам следует разделить нагрузку между несколькими базами, или даже сделать ваши транзакции чуть более долгими, чтобы уменьшить количество записей лога на диск. Для того, чтобы убедиться, что причина в системе ввода/вывода, используйте DMV sys.dm_io_virtual_file_stats для того, чтобы изучить задержку ввода/вывода для файла лога и увидеть, совпадает ли она с временем задержки WRITELOG. Если WRITELOG длится дольше, вы получили внутреннюю конкуренцию за запись на диск и должны разделить нагрузку. Если нет, выясняйте, почему вы создаете такой большой лог транзакций. Здесь (англ.) и здесь (англ.) можно почерпнуть некоторые идеи.
(прим переводчика: следующий запрос позволяет в простом и удобном виде получить статистику задержек ввода/вывода для каждого файла каждой базы данных на сервере:
109: BROKER_RECEIVE_WAITFOR
Здесь Service Broker ждет новые сообщения. Я бы рекомендовал добавить это ожидание в список исключаемых и заново выполнить запрос со статистикой ожидания.
086: MSQL_XP
Здесь SQL Server ждет выполнения расширенных хранимых процедур. Это может означать наличие проблем в коде ваших расширенных хранимых процедур.
074: OLEDB
Как и предполагается из названия, это ожидание взаимодействия с использованием OLEDB — например, со связанным сервером. Однако, OLEDB также используется в DMV и командой DBCC CHECKDB, так что не думайте, что проблема обязательно в связанных серверах — это может быть внешняя система мониторинга, чрезмерно использующая вызовы DMV. Если это и в самом деле связанный сервер — тогда проведите анализ ожиданий на связанном сервере и определите, в чем проблема с производительностью на нем.
054: BACKUPIO
Показывает, когда вы делаете бэкап напрямую на ленту, что ооочень медленно. Я бы предпочел отфильтровать это ожидание. (прим. переводчика: я встречался с этим типом ожиданий при записи бэкапа на диск, при этом бэкап небольшой базы выполнялся очень долго, не успевая выполниться в технологический перерыв и вызывая проблемы с производительностью у пользователей. Если это ваш случай, возможно дело в системе ввода/вывода, используемой для бэкапирования, необходимо рассмотреть возможность увеличения ее производительности либо пересмотреть план обслуживания (не выполнять полные бэкапы в короткие технологические перерывы, заменив их дифференциальными))
041: LCK_M_XX
Здесь поток просто ждет доступа для наложения блокировки на объект и означает проблемы с блокировками. Это может быть вызвано нежелательной эскалацией блокировок или плохим кодом, но также может быть вызвано тем, что операции ввода/вывода занимают слишком долгое время и держат блокировки дольше, чем обычно. Посмотрите на ресурсы, связанные с блокировками, используя DMV sys.dm_os_waiting_tasks. Не стоит считать, что основная проблема в блокировках.
032: ONDEMAND_TASK_QUEUE
Это нормально и является частью системы фоновых задач (таких как отложенный сброс, очистка в фоне). Я бы добавил это ожидание в список исключаемых и заново выполнил запрос со статистикой ожидания.
031: BACKUPBUFFER
Показывает, когда вы делаете бэкап напрямую на ленту, что ооочень медленно. Я бы предпочел отфильтровать это ожидание.
027: IO_COMPLETION
SQL Server ждет завершения ввода/вывода и этот тип ожидания может быть индикатором проблемы с системой ввода/вывода.
024: SOS_SCHEDULER_YIELD
Чаще всего это код, который не попадает в другие типы ожидания, но иногда это может быть конкуренция в циклической блокировке.
022: DBMIRROR_EVENTS_QUEUE
022: DBMIRRORING_CMD
Эти два типа показывают, что система управления зеркальным отображением (database mirroring) сидит и ждет, чем бы ей заняться. Я бы добавил эти ожидания в список исключаемых и заново выполнил запрос со статистикой ожидания.
018: PAGELATCH_XX
Это конкуренция за доступ к копиям страниц в памяти. Наиболее известные случаи — это конкуренция PFS, SGAM, и GAM, возникающие в базе tempdb при определенных типах нагрузок (англ.). Для того, чтобы выяснить, за какие страницы идет конкуренция, вам нужно использовать DMV sys.dm_os_waiting_tasks для того, чтобы выяснить, из-за каких страниц возникают блокировки. По проблемам с базой tempdb Роберт Дэвис (его блог, твиттер) написал хорошую статью, показывающую, как их решать (англ.) Другая частая причина, которую я видел — часто обновляемый индекс с конкурирующими вставками в индекс, использующий последовательный ключ (IDENTITY).
016: LATCH_XX
Это конкуренция за какие либо не страничные структуры в SQL Server’е — так что это не связано с вводом/выводом и данными вообще. Причину такого типа задержки может быть достаточно сложно понять и вам необходимо использовать DMV sys.dm_os_latch_stats.
013: PREEMPTIVE_OS_PIPEOPS
Здесь SQL Server переключается в режим упреждающего планирования для того, чтобы запросить о чем-то Windows. Этот тип ожидания был добавлен в 2008 версии и еще не был документирован. Самый простой способ выяснить, что он означает — это убрать начальные PREEMPTIVE_OS_ и поискать то, что осталось, в MSDN — это будет название API Windows.
013: THREADPOOL
Такой тип говорит, что недостаточно рабочих потоков в системе для того, чтобы удовлетворить запрос. Обычно причина в большом количестве сильно параллелизованных запросов, пытающихся выполниться. (прим. переводчика: также это может быть намеренно урезанное значение параметра сервера «max worker threads»)
009: BROKER_TRANSMITTER
Здесь Service Broker ждет новых сообщений для отправки. Я бы рекомендовал добавить это ожидание в список исключаемых и заново выполнить запрос со статистикой ожидания.
006: SQLTRACE_WAIT_ENTRIES
Часть слушателя (trace) SQL Server’а. Я бы рекомендовал добавить это ожидание в список исключаемых и заново выполнить запрос со статистикой ожидания.
005: DBMIRROR_DBM_MUTEX
Это один из недокументированных типов и в нем конкуренция возникает за отправку буфера, который делится между сессиями зеркального отображения (database mirroring). Может означать, что у вас слишком много сессий зеркального отображения.
005: RESOURCE_SEMAPHORE
Здесь запрос ждет память для исполнения (память, используемая для обработки операторов запроса — таких, как сортировка). Это может быть недостаток памяти при конкурентной нагрузке.
003: PREEMPTIVE_OS_AUTHENTICATIONOPS
003: PREEMPTIVE_OS_GENERICOPS
Здесь SQL Server переключается в режим упреждающего планирования для того, чтобы запросить о чем-то Windows. Этот тип ожидания был добавлен в 2008 версии и еще не был документирован. Самый простой способ выяснить, что он означает — это убрать начальные PREEMPTIVE_OS_ и поискать то, что осталось, в MSDN — это будет название API Windows.
003: SLEEP_BPOOL_FLUSH
Это ожидание можно часто увидеть и оно означает, что контрольная точка ограничивает себя для того, чтобы избежать перегрузки системы ввода/вывода. Я бы рекомендовал добавить это ожидание в список исключаемых и заново выполнить запрос со статистикой ожидания.
002: MSQL_DQ
Здесь SQL Server ожидает, пока выполнится распределенный запрос. Это может означать проблемы с распределенными запросами или может быть просто нормой.
002: RESOURCE_SEMAPHORE_QUERY_COMPILE
Когда в системе происходит слишком много конкурирующих перекомпиляций запросов, SQL Server ограничивает их выполнение. Я не помню уровня ограничения, но это ожидание может означать излишнюю перекомпиляцию или, возможно, слишком частое использование одноразовых планов.
001: DAC_INIT
Я никогда раньше этого не видел и BOL говорит, что причина в инициализации административного подключения. Я не могу представить, как это может быть преимущественным ожиданием на чьей либо системе…
001: MSSEARCH
Этот тип является нормальным при полнотекстовых операциях. Если это преимущественное ожидание, это может означать, что ваша система тратит больше всего времени на выполнение полнотекстовых запросов. Вы можете рассмотреть возможность добавить этот тип ожидания в список исключаемых.
001: PREEMPTIVE_OS_FILEOPS
001: PREEMPTIVE_OS_LIBRARYOPS
001: PREEMPTIVE_OS_LOOKUPACCOUNTSID
001: PREEMPTIVE_OS_QUERYREGISTRY
Здесь SQL Server переключается в режим упреждающего планирования для того, чтобы запросить о чем-то Windows. Этот тип ожидания был добавлен в 2008 версии и еще не был документирован. Самый простой способ выяснить, что он означает — это убрать начальные PREEMPTIVE_OS_ и поискать то, что осталось, в MSDN — это будет название API Windows.
001: SQLTRACE_LOCK
Часть слушателя (trace) SQL Server’а. Я бы рекомендовал добавить это ожидание в список исключаемых и заново выполнить запрос со статистикой ожидания.

Можно сбросить статистику что бы собрать её заного

DBCC SQLPERF (N'sys.dm_os_wait_stats', CLEAR);
GO

1 2	DBCC SQLPERF (N'sys.dm_os_wait_stats', CLEAR); GO

Определились что у нас узкое место блокировки и файловая система. Так как мучить программеров нет смысла что бы они изменили код. Начали мучить файловую систему. Тоесть переносить на HP MSA 2040:

Дальше решили узнать какая файловая группа самая прожорлевая в плане чтения и записи. Был выполнен скрипт из статьи https://technet.microsoft.com/ru-ru/library/jj643251.aspx

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

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]

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;

Результат:

Результат в таблице:

Конечный результат. Распределение файловых групп по дискам:

ссылка на таблицу https://docs.google.com/spreadsheets/d/1CxU8qVb6zGG9176aIB8PSj7r34EQs7mXttDYawGi7lw/edit?usp=sharing

Как сделать бэкап множества баз mssql на сетевое хранилище с созданием папок под бэкап. И после удалить эти базы.

29.11.2016, Categories Mssql Comments Нет комментариев

Задача сделать бэкапы баз на 50 серверах в одно файловой хранилище в разные папки

DECLARE @name NVARCHAR(500)
DECLARE @cmd NVARCHAR(500)
DECLARE @Cursor CURSOR   
DECLARE @nameser NVARCHAR(500)
SET @nameser = @@SERVERNAME
SET @Cursor =    CURSOR FOR
SELECT NAME
FROM   [master].[sys].[databases]
WHERE  NOT NAME IN ('master'
                   ,'tempdb'
                   ,'model'
                   ,'msdb'
                   ,'SES'
                   ,'SES_ARCHIVE'
                   ,'SES_SERVICE')
       AND NAME LIKE '%2013%'
       OR NAME LIKE '%2014%'
       OR NAME LIKE '%2015%'
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @name
WHILE (@@FETCH_STATUS=0)
BEGIN
    --print @nameser
    SET @cmd = '
EXEC xp_cmdshell ''MD \\192.168.0.38\Backup\cristal_arhiv\'+@nameser+'\''
BACKUP DATABASE ['+@name+'] TO  DISK = N''\\192.168.0.38\Backup\cristal_arhiv\'+@nameser+'\'+@nameser+'_'+
        @name+'.BAK'' WITH NOFORMAT, NOINIT,  NAME = N'''+@name+
        '-Полная База данных Резервное копирование'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10' 
    -- PRINT @cmd
    EXEC (@cmd)
    
    FETCH NEXT FROM @Cursor INTO @name
END

DECLARE @name NVARCHAR(500)

DECLARE @cmd NVARCHAR(500)

DECLARE @Cursor CURSOR

DECLARE @nameser NVARCHAR(500)

SET @nameser = @@SERVERNAME

SET @Cursor = CURSOR FOR

SELECT NAME

FROM [master].[sys].[databases]

WHERE NOT NAME IN ('master'

,'tempdb'

,'model'

,'msdb'

,'SES'

,'SES_ARCHIVE'

,'SES_SERVICE')

AND NAME LIKE '%2013%'

OR NAME LIKE '%2014%'

OR NAME LIKE '%2015%'

OPEN @Cursor

FETCH NEXT FROM @Cursor INTO @name

WHILE (@@FETCH_STATUS=0)

BEGIN

--print @nameser

SET @cmd = '

EXEC xp_cmdshell ''MD \\192.168.0.38\Backup\cristal_arhiv\'+@nameser+'\''

BACKUP DATABASE ['+@name+'] TO DISK = N''\\192.168.0.38\Backup\cristal_arhiv\'+@nameser+'\'+@nameser+'_'+

@name+'.BAK'' WITH NOFORMAT, NOINIT, NAME = N'''+@name+

'-Полная База данных Резервное копирование'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'

-- PRINT @cmd

EXEC (@cmd)

FETCH NEXT FROM @Cursor INTO @name

END

Скрипт я запускал в зарегистрированных серверах.

После того как сделался бэкап удалил эти базы

DECLARE @name NVARCHAR(500)

DECLARE @cmd NVARCHAR(500)

DECLARE @Cursor CURSOR 

SET @Cursor =  CURSOR FOR

SELECT 		
			NAME
FROM   
			[master].[sys].[databases]
WHERE  
			NOT NAME IN ('master' ,'tempdb' ,'model' ,'msdb','SES','SES_ARCHIVE','SES_SERVICE') 
			AND name LIKE '%2013%' 
			or name LIKE '%2014%'
			or name LIKE '%2015%'
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @name
WHILE (@@FETCH_STATUS = 0)
BEGIN
    --print @name
    SET @cmd =
    
   
--'BACKUP DATABASE ['+ @name +'] TO  DISK = N''\\192.168.0.38\Backup\cristal arhiv\'+@name+'.BAK'' WITH NOFORMAT, NOINIT,  NAME = N'''+@name+'----Полная База данных Резервное копирование'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'

'EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'''+@name+'''
GO
USE [master]
GO
ALTER DATABASE ['+ @name +'] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
/****** Object:  Database ['+ @name +']    Script Date: 11/29/2016 08:23:42 ******/
DROP DATABASE ['+ @name +']
GO'

   
    
    
    
   PRINT @cmd
   --    exec (@cmd)
     FETCH NEXT FROM @Cursor INTO @name
END

DECLARE @name NVARCHAR(500)

DECLARE @cmd NVARCHAR(500)

DECLARE @Cursor CURSOR

SET @Cursor = CURSOR FOR

SELECT

NAME

FROM

[master].[sys].[databases]

WHERE

NOT NAME IN ('master' ,'tempdb' ,'model' ,'msdb','SES','SES_ARCHIVE','SES_SERVICE')

AND name LIKE '%2013%'

or name LIKE '%2014%'

or name LIKE '%2015%'

OPEN @Cursor

FETCH NEXT FROM @Cursor INTO @name

WHILE (@@FETCH_STATUS = 0)

BEGIN

--print @name

SET @cmd =

--'BACKUP DATABASE ['+ @name +'] TO DISK = N''\\192.168.0.38\Backup\cristal arhiv\'+@name+'.BAK'' WITH NOFORMAT, NOINIT, NAME = N'''+@name+'----Полная База данных Резервное копирование'', SKIP, NOREWIND, NOUNLOAD, STATS = 10'

'EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'''+@name+'''

USE [master]

ALTER DATABASE ['+ @name +'] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

USE [master]

/****** Object: Database ['+ @name +'] Script Date: 11/29/2016 08:23:42 ******/

DROP DATABASE ['+ @name +']

GO'

PRINT @cmd

-- exec (@cmd)

FETCH NEXT FROM @Cursor INTO @name

END

Результат бэкапа

Как увеличить количество файлов в TempDB

29.11.2016, Categories Mssql Comments Нет комментариев

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 6144000KB , MAXSIZE = 35840000KB )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1', FILENAME = N'S:\tempdb1.mdf' , SIZE = 6144000KB , MAXSIZE = 35840000KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'S:\tempdb2.mdf' , SIZE = 6144000KB , MAXSIZE = 35840000KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'S:\tempdb3.mdf' , SIZE = 6144000KB , MAXSIZE = 35840000KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'S:\tempdb4.mdf' , SIZE = 6144000KB , MAXSIZE = 35840000KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev5', FILENAME = N'S:\tempdb5.mdf' , SIZE = 6144000KB , MAXSIZE = 35840000KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev6', FILENAME = N'S:\tempdb6.mdf' , SIZE = 6144000KB , MAXSIZE = 35840000KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev7', FILENAME = N'S:\tempdb7.mdf' , SIZE = 6144000KB , MAXSIZE = 35840000KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', MAXSIZE = 92160000KB )
GO

USE [master]

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 6144000KB , MAXSIZE = 35840000KB )

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev1', FILENAME = N'S:\tempdb1.mdf' , SIZE = 6144000KB , MAXSIZE = 35840000KB , FILEGROWTH = 10%)

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'S:\tempdb2.mdf' , SIZE = 6144000KB , MAXSIZE = 35840000KB , FILEGROWTH = 10%)

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'S:\tempdb3.mdf' , SIZE = 6144000KB , MAXSIZE = 35840000KB , FILEGROWTH = 10%)

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'S:\tempdb4.mdf' , SIZE = 6144000KB , MAXSIZE = 35840000KB , FILEGROWTH = 10%)

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev5', FILENAME = N'S:\tempdb5.mdf' , SIZE = 6144000KB , MAXSIZE = 35840000KB , FILEGROWTH = 10%)

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev6', FILENAME = N'S:\tempdb6.mdf' , SIZE = 6144000KB , MAXSIZE = 35840000KB , FILEGROWTH = 10%)

ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev7', FILENAME = N'S:\tempdb7.mdf' , SIZE = 6144000KB , MAXSIZE = 35840000KB , FILEGROWTH = 10%)

ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', MAXSIZE = 92160000KB )

Как добавить права чтения или записи на базу, сразу многим пользователям.

15.11.2016, Categories Mssql, Моё Избарнное Comments Нет комментариев

declare @db nvarchar(max)
declare @sql nvarchar(max)
declare cursor_size_srv cursor for
SELECT  --@@SERVERNAME AS Server ,
        name AS DBName 
        --recovery_model_Desc AS RecoveryModel ,
        --Compatibility_level AS CompatiblityLevel ,
        --create_date ,
        --state_desc
FROM sys.server_principals
WHERE NAME LIKE 'HEAD_OFFICE\_Розничная сеть%'
ORDER BY Name; 
 
OPEN cursor_size_srv
FETCH NEXT FROM cursor_size_srv INTO @db
WHILE (@@FETCH_STATUS=0)
BEGIN
set @sql =

'USE [Demo_connect]
GO
CREATE USER ['+@db+'] FOR LOGIN ['+@db+']
GO
USE [Demo_connect]
GO
EXEC sp_addrolemember N''db_datareader'', N'''+@db+'''
GO'


print (@sql);
FETCH NEXT FROM cursor_size_srv INTO @db
END
CLOSE cursor_size_srv
DEALLOCATE cursor_size_srv

declare @db nvarchar(max)

declare @sql nvarchar(max)

declare cursor_size_srv cursor for

SELECT --@@SERVERNAME AS Server ,

name AS DBName

--recovery_model_Desc AS RecoveryModel ,

--Compatibility_level AS CompatiblityLevel ,

--create_date ,

--state_desc

FROM sys.server_principals

WHERE NAME LIKE 'HEAD_OFFICE\_Розничная сеть%'

ORDER BY Name;

OPEN cursor_size_srv

FETCH NEXT FROM cursor_size_srv INTO @db

WHILE (@@FETCH_STATUS=0)

BEGIN

set @sql =

'USE [Demo_connect]

CREATE USER ['+@db+'] FOR LOGIN ['+@db+']

USE [Demo_connect]

EXEC sp_addrolemember N''db_datareader'', N'''+@db+'''

GO'

print (@sql);

FETCH NEXT FROM cursor_size_srv INTO @db

END

CLOSE cursor_size_srv

DEALLOCATE cursor_size_srv

Как найти и удалить дубликаты строк в таблице mssql

11.11.2016, Categories Mssql Comments Нет комментариев

Select id, objectname from [ax_molniya].[dbo].[www_reindex_tablename]  where objectname in (
Select objectname from [ax_molniya].[dbo].[www_reindex_tablename] Group by objectname having Count(*) >1)

1 2	Select id, objectname from [ax_molniya].[dbo].[www_reindex_tablename] where objectname in ( Select objectname from [ax_molniya].[dbo].[www_reindex_tablename] Group by objectname having Count(*) >1)

Delete from t from [ax_molniya].[dbo].[www_reindex_tablename]  t, [ax_molniya].[dbo].[www_reindex_tablename]  v
where t.objectname = v.objectname
and t.id > v.id

Delete from t from [ax_molniya].[dbo].[www_reindex_tablename] t, [ax_molniya].[dbo].[www_reindex_tablename] v

where t.objectname = v.objectname

and t.id > v.id

Как запустить задание (job) удаленна с другова сервере mssql или из другого задания (job).

27.10.2016, Categories Mssql Comments Нет комментариев

Была задача запустить задание из другого задания которое находилось на другом сервере mssql

Создаём линковку (связанный сервер) и в свойствах включаем RPC :

Так же надо убедится что у пользователя под кем создалась линкова есть права на базу msdb:

exec linkserver.msdb.dbo.sp_start_job  @job_name = 'name job'

1	exec linkserver.msdb.dbo.sp_start_job @job_name = 'name job'

Как сформировать скрипт для реиндекса индексов mssql. Необходимо для каждого индекса с генерировать соответствующую ALTER INDEX команду

20.09.2016, Categories Mssql Comments Нет комментариев

DECLARE @SQL NVARCHAR(MAX)

DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR
	SELECT '
	PRINT ''Перестройка  [' + i.name + N'] начато ''  + Cast(GETDATE() as varchar);
	
	ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +
		CASE WHEN s.avg_fragmentation_in_percent > 30
			THEN 'REBUILD WITH (FILLFACTOR = 80, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON)
			
	PRINT ''Перестройка  [' + i.name + N'] завершено '' + Cast(GETDATE() as varchar) '
ELSE 'REORGANIZE'
		END + ';'
		
		


	FROM (
		SELECT 
			  s.[object_id]
			, s.index_id
			, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)
		FROM sys.dm_db_index_physical_stats(DB_ID('ax_molniya'), OBJECT_ID('dbo.INVENTTRANS'), NULL, NULL, 'LIMITED') s
		WHERE s.page_count > 128 -- > 1 MB
			AND s.index_id > 0 -- <> HEAP
			AND s.avg_fragmentation_in_percent > 5
		GROUP BY s.[object_id], s.index_id
	) s
	JOIN sys.indexes i WITH(NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id
	JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = s.[object_id]

OPEN cur

FETCH NEXT FROM cur INTO @SQL

WHILE @@FETCH_STATUS = 0 BEGIN

	print (@SQL);

	FETCH NEXT FROM cur INTO @SQL
	
END 

CLOSE cur 
DEALLOCATE cur

DECLARE @SQL NVARCHAR(MAX)

DECLARE cur CURSOR LOCAL READ_ONLY FORWARD_ONLY FOR

SELECT '

PRINT ''Перестройка [' + i.name + N'] начато '' + Cast(GETDATE() as varchar);

ALTER INDEX [' + i.name + N'] ON [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] ' +

CASE WHEN s.avg_fragmentation_in_percent > 30

THEN 'REBUILD WITH (FILLFACTOR = 80, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON)

PRINT ''Перестройка [' + i.name + N'] завершено '' + Cast(GETDATE() as varchar) '

ELSE 'REORGANIZE'

END + ';'

FROM (

SELECT

s.[object_id]

, s.index_id

, avg_fragmentation_in_percent = MAX(s.avg_fragmentation_in_percent)

FROM sys.dm_db_index_physical_stats(DB_ID('ax_molniya'), OBJECT_ID('dbo.INVENTTRANS'), NULL, NULL, 'LIMITED') s

WHERE s.page_count > 128 -- > 1 MB

AND s.index_id > 0 -- <> HEAP

AND s.avg_fragmentation_in_percent > 5

GROUP BY s.[object_id], s.index_id

) s

JOIN sys.indexes i WITH(NOLOCK) ON s.[object_id] = i.[object_id] AND s.index_id = i.index_id

JOIN sys.objects o WITH(NOLOCK) ON o.[object_id] = s.[object_id]

OPEN cur

FETCH NEXT FROM cur INTO @SQL

WHILE @@FETCH_STATUS = 0 BEGIN

print (@SQL);

FETCH NEXT FROM cur INTO @SQL

END

CLOSE cur

DEALLOCATE cur

Что значит строка (FILLFACTOR = 80, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON) :

[stextbox id=»info» caption=»FILLFACTOR =»]Указывает значение в процентах, показывающее, насколько полным компонент Database Engine должен сделать конечный уровень каждой страницы индекса во время создания или изменения индекса. Аргумент fillfactor должен быть целым числом от 1 до 100. Значение по умолчанию равно 0.[/stextbox]

[stextbox id=»info» caption=»PAD_INDEX «]Определяет разреженность индекса. Значение по умолчанию — OFF.
ON
Процент свободного места, определяемый параметром FILLFACTOR, применяется к страницам индекса промежуточного уровня. Если FILLFACTOR не указан и одновременно PAD_INDEX установлен в состояние ON, то используется значение коэффициента заполнения, хранимое в таблице sys.indexes.
OFF или fillfactor не указан
Страницы промежуточного уровня заполняются почти полностью. При этом остается достаточно места по крайней мере для одной строки максимального размера, которого может достигать индекс, в зависимости от набора ключей в промежуточных страницах.
Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL). [/stextbox]

[stextbox id=»info» caption=»STATISTICS_NORECOMPUTE «]Указывает, выполнялся ли перерасчет статистики распределения. Значение по умолчанию — OFF.
ON
Устаревшие статистики не пересчитываются автоматически.
OFF
Автоматическое обновление статистических данных включено.
Чтобы восстановить автоматическое обновление статистики, следует установить STATISTICS_NORECOMPUTE в значение OFF или выполнить UPDATE STATISTICS без предложения NORECOMPUTE. [/stextbox]

[stextbox id=»info» caption=» ALLOW_ROW_LOCKS «]Когда присвоены значения ALLOW_ROW_LOCKS = ON и ALLOW_PAGE_LOCK = ON, при доступе к индексу допустимы блокировки на уровне строк, уровне страниц и уровне таблиц. Компонент Database Engine выберет соответствующую блокировку и может повысить уровень блокировки с уровня строки или таблицы до уровня страницы.
Если присвоены значения ALLOW_ROW_LOCKS = OFF и ALLOW_PAGE_LOCK = OFF, при доступе к индексу допустима только блокировка на уровне таблиц. Дополнительные сведения о настройке гранулярности блокировки индекса см. в разделе Настройка блокировки индекса. [/stextbox]

[stextbox id=»info» caption=»ALLOW_PAGE_LOCKS»]Указывает, разрешена ли блокировка страниц. Значение по умолчанию — ON.
ON
Блокировки страниц допустимы при доступе к индексу. Необходимость в блокировке страниц определяет компонент Database Engine.
OFF
Блокировки страниц не используются.
ПримечаниеПримечание
Индекс не может быть реорганизован, если ALLOW_PAGE_LOCKS установлен в состояние OFF.[/stextbox]

[stextbox id=»info» caption=»SORT_IN_TEMPDB»]Указывает, следует ли сохранять результаты сортировки в базе данных tempdb. Значение по умолчанию — OFF.
ON
Промежуточные результаты сортировки, которые используются при индексировании, хранятся в базе данных tempdb. Это может сократить время, требуемое для создания индекса, если база данных tempdb размещена на иных дисках, нежели пользовательская база данных. Однако это увеличивает использование места на диске, которое используется при индексировании.
OFF
Промежуточные результаты сортировки хранятся в той же базе данных, где и индекс.
Если выполнение сортировки не требуется или если сортировка может быть выполнена в памяти, параметр SORT_IN_TEMPDB пропускается. [/stextbox]

[stextbox id=»info» caption=»ONLINE «]Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.
Для XML-индекса или пространственного индекса поддерживается только значение ONLINE = OFF; при ONLINE = ON возникает ошибка.
ПримечаниеПримечание
Операции с индексами в сети доступны только в выпусках SQL Server Enterprise, Developer и Evaluation.
ON
Долгосрочные блокировки таблицы не поддерживаются во время операций с индексами. Во время главной фазы операций с индексами только блокировка с намерением совмещаемого доступа (IS) удерживается в исходной таблице. Это позволяет продолжить выполнение запросов или обновлений для базовых таблиц и индексов. В начале операции совмещаемая блокировка (S) исходного объекта поддерживается в течение очень короткого времени. Если создается некластеризованный индекс, то по завершении операции на короткое время создается блокировка типа S (совмещаемая) для источника. Блокировка типа SCH-M (изменения схемы) запрашивается, если кластеризованный индекс создается или удаляется в режиме в сети либо, происходит перестроение кластеризованного или некластеризованного индекса. При создании индекса для временной локальной таблицы параметр ONLINE не может принимать значение ON.
OFF
Блокировки таблиц применяются во время выполнения операций с индексами. Операция с индексами в режиме «вне сети», которая создает, перестраивает или удаляет кластеризованный, пространственный или XML-индекс либо перестраивает или удаляет некластеризованный индекс, получает блокировку изменения схемы (Sch-M) для этой таблицы. Это предотвращает доступ к базовой таблице всех пользователей во время операции. Операция с индексами вне сети, создающая некластеризованный индекс, получает совмещаемую блокировку (S) в таблице. Это запрещает проводить обновления базовой таблицы, но разрешает проводить операции чтения, например инструкции SELECT. [/stextbox]

Следующая страница

AdminBD

Мета

Categories

Свежие записи

Свежие комментарии

Поиск по всем таблицам в базе MSSQL

Как восстановить полный (full) и разностный (diff) бэкап базы MSSQL

Как удалить репликации у базы MSSQL.

Как создать хранимую процедуру для мониторинга заданий (job) в MSSQL. Хранимая процедура для PRTG

Установка диспетчера виртуальных машин System Center 2016 — шаг за шагом (руководство по быстрому запуску)

Как узнать кому отправлялись письма из mssql

Хранимая процедура для архивирования журнала при достижении @danger_value.

Отправить СМС из MSSQL

Как узнать в какой файловой группе больше всего записи и чтения.

Как сделать бэкап множества баз mssql на сетевое хранилище с созданием папок под бэкап. И после удалить эти базы.

Как увеличить количество файлов в TempDB

Как добавить права чтения или записи на базу, сразу многим пользователям.

Как найти и удалить дубликаты строк в таблице mssql

Как запустить задание (job) удаленна с другова сервере mssql или из другого задания (job).

Как сформировать скрипт для реиндекса индексов mssql. Необходимо для каждого индекса с генерировать соответствующую ALTER INDEX команду