MS SQL Server- по простому.

Фильтр по тегу: скрипты


Скрипт обновления статистики в базе данных с условием

В отличие от стандартного блока Maintance Plan-а, где статистика обновляется не смотря на дату последнего обновления, ниже приводится скрипт обновления статистики в базе данных с условием последней даты обновления статистики.

Сам скрипт:
 
-- запрос обновления статистики таблицы по дате последнего обновления
---1 получаем список имен статистики для обновления и записываем во временную таблицу

declare @dtas datetime
set @dt= getdate()-01 -- дата, меньше которой будем обновлять


begin try

      select o.name as [objname],s.name as [shname],o.object_id,st.name as     [stname]

      into #t_stat

    from sys.stats st

        inner join  sys.objects o  on st.object_id=o.object_id

        inner join   sys.schemas  s on o.schema_id=s.schema_id

      where o.type='U'

      --and STATS_DATE(st.object_id, st.stats_id)  <@dt    --фильтр по дате

      --and o.name='dbservers'                             --фильтра по обеъкту

end try

begin catch
      print (cast(error_number() as nchar(5))+'_'+ ERROR_MESSAGE())
      drop tab...
читать дальше...
добавлено: 24 ноя 15 просмотры: 1535, комментарии: 0



Скриптование всех пользователей, ролей БД и их прав

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



--1 создание таблицы для логинов
print 'Создание таблиц для хранения учетных записей и их прав'
IF NOT EXISTS (SELECT * FROM tempdb.sys.objects
              WHERE object_id = OBJECT_ID(N'tempdb.[dbo].[logins]') AND type in (N'U'))
begin
      create table tempdb.dbo.logins
      ( a varchar(2048),
       l int)
end
else
begin 
 truncate table tempdb.dbo.Logins
end

IF NOT EXISTS (SELECT * FROM tempdb.sys.objects
              WHERE object_id = OBJECT_ID(N'tempdb.[dbo].[loginsscripts]') AND type in (N'U'))
begin
      create table tempdb.dbo.loginsscripts
      ( a varchar(2048))
end
else
begin 
 truncate table tempdb.dbo.[loginsscripts]
end
print 'Конец Создание таблиц для хранения учетных записей и их прав'

--2 создание процедур скриптования 
print 'Создание процедур для скриптования логинов '
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[USERGRANT]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE P...
читать дальше...
добавлено: 22 ноя 15 просмотры: 1784, комментарии: 0



Скрипт список разрешений пользователей на сервер MS SQL Server


 При миграции с одного MS SQL server на другой сервер, переустановки сервера  необходимо кроме как скопировать  учетные записи, нужно скопировать и их разрешения на сервер MS SQL Server.

Ниже приводится скрипт  получения списка прав на MS SQL Server(скрипт применим для версий MS SQL Server 2005 и старше):

select 
 'sqlcommand'=
   case
     when s.class_desc='SERVER' then  
         case
             when  s.state_desc<>'GRANT_WITH_GRANT_OPTION'then
              s.state_desc+' '+s.permission_name +' TO ['+p.name+']' collate Cyrillic_General_CI_AS
             else 
              'GRANT '+s.permission_name +' TO ['+p.name+'] ' +'WITH GRANT OPTION ' collate Cyrillic_General_CI_AS
         end
    
     when s.class_desc='ENDPOINT' then
        case
             when  s.state_desc<>'GRANT_WITH_GRANT_OPTION'then
              s.state_desc+' '+s.permission...
читать дальше...
добавлено: 16 ноя 15 просмотры: 1402, комментарии: 0



Обслуживание системных баз данных MS SQL Server.

В MS SQL Server есть несколько системных баз данных:

master - В этой базе данных хранятся все данные системного уровня для экземпляра SQL Server.
Model - Используется в качестве шаблона для всех баз данных, создаваемых в экземпляре SQL Server.Изменение размера, параметров сортировки, модели восстановления и других параметров базы данных model приводит к изменению соответствующих параметров всех баз данных, создаваемых после изменения.
Msdb - Используется агентом SQL Server для планирования предупреждений и задач, так же является хранилищем пакетов SSIS, хранилищем информации по резервному копированию.
tempdb - База данных для временных объектов и для промежуточных результирующих наборов.
Resource - База данных только для чтения. Содержит системные объекты, которые входят в состав SQL Server. Системные объекты физически хранятся в базе данных Resource, но логически отображаются в схеме sys любой базы данных.

Задачей обслуживания данных баз данных является возможность восстановления данных баз данных во время их повреждения, неудачного обновления, выхода из строя оборудования и других возможных проблем.

Типичные задачи обслуживания для системных баз данных (за исключением БД TempDb и resource):
      -          Создание резервной копии баз данных (с глубиной хранения минимум 7 дней)

-          Проверка целостности баз данных инструкцией DBCC CHECKDB

Все эти операции можно завернуть в задание sql agent-а и выполнять ежедневно, благо объем данных баз данных обычно небольшой, то и операции проходят довольно быстро, а спокойствия прибавляет).
Но это еще не все, на базе данных msdb я бы хотел подробней остановиться, к типичным вышеуказанным задачам добавляются специфичные задачи обслуживания msdb.

Как известно, в базе данных msdb хранится история резервных копий по базам данных. Теперь представим сервер, у которого баз данных более 50, каждые 10-15 минут проходит создание резервное копирование файла транзакций, какой объем будет таблиц с данной информацией?
На одном месте работы, когда я только туда пришел, на сервере было более 70 баз данных, серверу было более 2,5 лет и информация по резервному копированию никогда не чистилась, в итоге объем базы данных msdb был более 20 Гб!! А это уже совсем другое время и для создания резервной копии баз данных и для проверки целостности самой базы данных, и лишняя дисковая активность, плюс и время восстановления при аварии, в итоге имеем полно минусов, которые мы можем спокойно решить.

Очистка истории резервного копирования осуществляется через процедуру:
sp_delete_backuphistory [ @oldest_date = ] 'oldest_date'

где
[ @oldest_date = ] 'oldest_date'
Самая ранняя дата, сохраненная в таблицах журнала резервного копирования и восстановления. Аргумент oldest_date имеет тип datetime и не имеет значения по умолчанию
Одну информацию почистили, что еще там хранится?!

Почта. Настроен ли у вас Database Mail  и происходит ли отсылка писем, а если еще с вложениями письма?
Вся история по нему так же хранится в базе данных msdb. Для очистки данной истории тоже есть системные процедуры:

sysmail_delete_mailitems_sp  [ [ @sent_before = ] 'sent_before' ] [ , [ @sent_status = ] 'sent_status' ]
где
[ @sent_before = ] 'sent_before'
Удаляет сообщения электронной почты до даты и времени, указанных аргументом sent_before. Аргумент sent_before имеет тип datetime и не имеет значения по умолчанию. Значение NULL соответствует всем датам.

[ @sent_status = ] 'sent_status'
Удаляет сообщения электронной почты, тип которых указан аргументом sent_status. Аргумент sent_status имеет тип varchar(8) и не имеет значения по умолчанию. Допустимые значения: sent, unsent, retrying и failed. Значение NULL соответствует всем состояниям.

sysmail_delete_log_sp  [ [ @logged_before = ] 'logged_before' ] [, [ @event_type = ] 'event_type' ]
где:

[ @logged_before = ] 'logged_before'
Удаляет записи вплоть до даты и времени, указанных в аргументе logged_before. Аргумент logged_before имеет тип datetime и значение по умолчанию NULL. Значение NULL соответствует всем датам.

[ @event_type = ] 'event_type'
Удаляет журнальные записи определенного типа, заданного аргументом event_type. Аргумент event_type имеет тип varchar(15) и не имеет значения по умолчанию. Допустимые записи: success, warning, error и informational. NULL соответствует всем типам событий.

С почтой мы решили, удалил старую информацию, что еще может быть там?
А есть ли у вас SSIS пакеты и как часто они запускаются? История по их выполнению хранится в таблице [msdb].[dbo].[sysssislog].

Для очистки ее настроена простая инструкция:
delete
  FROM [msdb].[dbo].[sysssislog] where starttime<@dt
Где @dt – дата, записи до которой следует удалить.

 После этого, выше указанные операции:
-  удаление истории резервного копирования
- очистка журнала Database Mail  
- очистка таблицы истории [msdb].[dbo].[sysssislog]

Мы заворачиваем в ms sql agent задание  и запускаем пару раз в месяц, и в итоге имеем наши компактные системные базы данных:).

Часть данных операций реализуются автоматически во время создания Maintenance Plans, но я их редко использую, поэтому у меня всегда есть скрипт задания обслуживания системных баз данных, который просто разворачиваю на новом сервере при настройке.

Возможно что-то пропустил, так что буду рад комментариям.
Будь аккуратны, держите рабочее место в чистоте!:).
добавлено: 02 авг 15 просмотры: 1585, комментарии: 4