OneScript на страже порядка на сервере тестовых баз данных

Любите ли вы OneScript так, как люблю его я? Кажется, с помощью OneScript и небольшой смекалки можно сделать вообще все, что угодно.

Стало казаться, что на сервере MS SQL для тестовых целей слишком много развелось баз. Какие-то делали для подрядчиков, какие-то для своих разработчиков, поднимали копии для пользователей. И вроде бы хозяев на сервере раз-два, и базы при их заведении назывались по шаблону, чтобы не было сомнений, для кого и чего она предназначена, но уровень бардака повысился, это прямо чувствуется. А бардак я не люблю. Да и потребовалось место для новых баз.

Для того, чтобы осуществить круговорот тестовых баз в природе (чтобы добавить нужные, сначала удалить ненужные), надо обладать достоверными знаниями о том, что можно удалить.

И вот в "пустоту" (общий список рассылки айтишников компании) уходит письмо: "Уважаемые коллеги, признавайтесь чьи базы и нужны ли они...". Скудные ответы не дают ясности. Что же, придется поработать самому.

К делу!

Вспоминаем, как пишутся запросы в MS SQL

Поработаем в консоли сервера MS SQL, попробуем нащупать пути решения...

Я-ж программист, для начала выведу список баз на сервере и пространство, которое они занимают. В MS SQL у нас есть хранимая процедура sp_databases, она предоставляет список баз данных, которые находятся в экземпляре SQL Server. Отлично, есть имя базы и ее размер в килобайтах (тут размер всех файлов, относящихся к базе данных - и сама БД и логи).

exec sp_databases

В список баз попадают все базы - и 1С и системные master, model, tempdb. Но меня сейчас интересуют только базы 1С. Как же понять, что эта база используется сервером 1С? Можно посмотреть зарегистрированные базы на кластерах серверов 1С, но их у меня несколько, да и чувствую, что можно ответить на этот вопрос проще. В любой 1С-ной базе есть служебные таблицы, одна из них - config, где хранятся бинарные данные описания конфигурации. Это идеальная таблица для наших целей, ведь в ней есть столбец Modified, если взять максимальную дату из него - это будет дата последнего обновления конфигурации. Выполним запрос.

USE base1c
SELECT MAX(DATEADD(YEAR , -2000 , Modified)) Modified 
FROM dbo.Config

Здесь мы еще учли стандартное смещение дат, которое использует сервер 1С. И вуаля - этот запрос для базы 1С (base1c) в примере) вернет дату и время. А вот запрос для tempdb не выполнится - у базы данных tempdb нет таблицы config и колонки Modified в ней. Так одним запросом мы убьем двух зайцев - поймем, что это база 1С (если запрос выполнится) и узнаем время изменения конфигурации базы данных.

Отлично, выполним этот запрос для каждой из баз данных, полученных на предыдущем этапе, и поймем, 1С это или нет и время последнего изменения конфигурации.

 А когда создали базу? Посмотрим системное представление sys.databases - выводит свойства каждой базы на сервере. Нам оттуда нужно только поле create_date

SELECT name DATABASE_NAME, CREATE_DATE
FROM sys.databases

Какая еще информация поможет мне разобраться со списком баз? Думаю, дата разворачивания из бекапа пригодится. Например, конфигурацию базы меняли год назад, но саму базу развернули из бекапа недавно - чтобы разобраться с каким-то изменением данных. В базе данных msdb есть таблица restorehistory, она содержит по одной строке для каждой операции восстановления базы. Сгруппируем данные по имени базы:

SELECT 
destination_database_name DATABASE_NAME, max(restore_date) LAST_RESTORE_DATE
FROM msdb..restorehistory
group by destination_database_name

И самое главное - как понять, что базой вообще пользуются? Когда пользователь заходит в базу, сервер 1С пишет какие-то данные в служебные и основные таблицы. А что происходит при записи в базу данных? Правильно, обновляются индексы. Обратимся к системному динамическому представлению sys.dm_db_index_usage_stats. Это представление возвращает количество различных операций с индексами и время, которое было затрачено на последнее выполнение операции каждого типа. Единственный недостаток - при перезапуске SQL сервера эти данные теряются. Кстати, по дате создания таблицы tempdb мы можем определить дату перезапуска сервера SQL, возьмем ее, когда будем анализировать данные таблиц.

SELECT MAX(last_user_update) As last_user_update, DB_NAME(database_id) AS DATABASE_NAME
FROM sys.dm_db_index_usage_stats
GROUP BY DB_NAME(database_id)

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

Ох, как интересно, есть базы, в которые не заходили больше двух месяцев, а то и больше.

Что же, у нас есть все данные. Можно сделать мега-запрос и получать всю информацию из консоли сервера MS SQL, но лично мне тяжело поддерживать такие запросы. Я напишу программу на православном OneScript.

Напишем программу, которая будет делать все это сама

Нам надо научиться выполнять запросы к серверу MS SQL из OneScript. Впрочем учиться не надо, ведь в библиотеке пакетов для OneScript обязательно найдется что-то. Посмотрим на подходящие пакеты:

Признаюсь, я работал с каждым из этих приложений-пакетов. cpdb - крутая штука, в основном годится для копирования баз данных (огромный респект Артему Кузнецову за функцию отключения от хранилища). Несмотря на то, что там предусмотрена возможность выполнять произвольные запросы, мне не очень удобно ее использовать для целей этой статьи. 

Пакет sql - мой выбор.

Посмотрите как легко выполнить запрос на сервере MS SQL:

#Использовать sql

Функция ВыполнитьЗапросНаСУБД(ТекстЗапроса, Таймаут)

	СоединениеБД = Новый Соединение();

	СоединениеБД.ТипСУБД = СоединениеБД.ТипыСУБД.MSSQLServer;
	СоединениеБД.Сервер = Сервер.Адрес;
	СоединениеБД.ИмяПользователя = Сервер.Логин;
	СоединениеБД.Пароль = Сервер.Пароль;

	СоединениеБД.Открыть();
    
        ЗапросСУБД = Новый Запрос();
	ЗапросСУБД.УстановитьСоединение(СоединениеБД);
	ЗапросСУБД.Текст = ТекстЗапроса;
	ЗапросСУБД.Таймаут = Таймаут;
	
	РезультатЗапроса = ЗапросСУБД.Выполнить().Выгрузить();

	СоединениеБД.Закрыть();
	
	Возврат ЗначениеДляВозврата;

КонецФункции

Почти как в 1С. Указываем текст запроса, таймаут, из переменной сервер берем данные подключения и вот у нас результат запроса.

Ну собственно, самое главное у нас есть. Сделаем мини-приложение на OneScript  со следующей структурой:

Мы изолируем функции работы с SQL в модуле КомандыSQL.os, модуль ДанныеСерверов.os будет содержать в себе параметры доступа к серверам - адрес сервера, имя пользователя, пароль и так далее. Основная работа будет проходить в скрипте base_list.os, подключим к нему вышеописанные модули инструкцией

#Использовать "."

В модуле КомандыSQL разместим функцию ВыполнитьЗапросНаСУБД и напишем функции получения каждой из таблиц, которые мы научились формировать ранее.

// Список баз на сервере - Получает список баз на сервере
//
// Параметры:
//  ПараметрыСервера - Структура - см. ДанныеСерверов.Сервера()[ИдентификаторСервера]      
// Возвращаемое значение:
//   ТаблицаЗначений:
//     * DATABASE_NAME - Строка - Имя базы данных 
//     * DATABASE_SIZE - Число - Размер базы данных (общий)
//
Функция СписокБазНаСервере(ПараметрыСервера) Экспорт
	
	Сервер = ПараметрыСервера;

	ТекстЗапроса = "
	|sp_databases
	|";
	
	Возврат ВыполнитьЗапросНаСУБД(ТекстЗапроса, 10);
		
КонецФункции

Аналогично сделаем и другие функции получения данных. По сути, эти функции содержат только одно изменение - текст запроса. Они получились лаконичными и легкими для понимания.

Теперь напишем основной скрипт.

Получаем данные доступа к серверу по его имени. Создаем таблицу значений с нужным нам составом колонок. Получаем список баз на сервере. Получаем остальные данные и обогащаем ими таблицу.

Сервер  = ДанныеСерверов.Сервера()[ИдентификаторСервера];

ИнформацияОБазах = СоздатьТаблицуИнформацияОБазах();

БазыСервера = КомандыSQL.СписокБазНаСервере(Сервер);

ТаблицаСДаннымиБекапа = КомандыSQL.ПоследниеРазворачиванияИзБекапа(Сервер);
ТаблицаСПоследнимиИзменениямиДанных = КомандыSQL.ПоследнееИзменениеДанных(Сервер);
ТаблицаСДатойСозданияБаз = КомандыSQL.ДатыСозданияБаз(Сервер);

Для Каждого СтрокаБазы Из БазыСервера Цикл
	
	ИмяБазы = СтрокаБазы.DATABASE_NAME;
	ДатаИзмененияКонфигурации = КомандыSQL.ВремяПоследнегоИзмененияКонфига(ИмяБазы, Сервер);
	
	НоваяСтрока = ИнформацияОБазах.Добавить();
	НоваяСтрока.DATABASE_NAME = ИмяБазы;
	НоваяСтрока.LAST_CONFIG_DATE = ДатаИзмененияКонфигурации;
	Если ЗначениеЗаполнено(ДатаИзмененияКонфигурации) Тогда
		НоваяСтрока.BASE_1C = Истина;
	Иначе
		НоваяСтрока.BASE_1C = Ложь;
	КонецЕсли;
	НоваяСтрока.DATABASE_SIZE = СтрокаБазы.DATABASE_SIZE;

	НайденнаяСтрока = ТаблицаСДаннымиБекапа.Найти(ИмяБазы, "DATABASE_NAME");
	Если НайденнаяСтрока <> Неопределено Тогда
		НоваяСтрока.BACKUP_RESTORE_DATE = НайденнаяСтрока.last_restore_date;
	КонецЕсли;
	
	НайденнаяСтрока = ТаблицаСПоследнимиИзменениямиДанных.Найти(ИмяБазы, "DATABASE_NAME");
	Если НайденнаяСтрока <> Неопределено Тогда
		НоваяСтрока.LAST_UPDATE_DATE = НайденнаяСтрока.last_user_update;
	КонецЕсли;
	
	НайденнаяСтрока = ТаблицаСДатойСозданияБаз.Найти(ИмяБазы, "DATABASE_NAME");
	Если НайденнаяСтрока <> Неопределено Тогда
		НоваяСтрока.CREATE_DATE = НайденнаяСтрока.CREATE_DATE;
	КонецЕсли;

КонецЦикла;

Готово! Вся информация у нас есть. Осталось ее сохранить куда-нибудь на диск или выдать список баз - кандидатов на удаление.

Как сохранить таблицу значений в oscript на диск? Очень просто - я сформирую текстовый файл csv с разделителями, который без труда открывается обычным табличным редактором. Код простой, спрячу его под спойлер.

Процедура ЗаписатьТаблицуВФайл(ТаблицаЗначений, ИдентификаторСервера)

	ИмяФайла = СтрШаблон("out_%1_%2.csv", ИдентификаторСервера, Формат(ТекущаяДата(), "ДФ=yyyy-MM-dd"));
	ПолноеИмяФайла = ОбъединитьПути(ТекущийСценарий().Каталог, ИмяФайла);
	
	ТекстТаблицы = "";
	МассивКолонок = Новый Массив;

	Для Каждого Колонка Из ТаблицаЗначений.Колонки Цикл
		ТекстТаблицы = СтрШаблон("%1%2%3", ТекстТаблицы, Колонка.Имя, ";");
		МассивКолонок.Добавить(Колонка.Имя);
	КонецЦикла;

	ТекстТаблицы = СтрШаблон("%1%2", ТекстТаблицы, Символы.ПС);

	Для Каждого СтрокаТаблицы Из ТаблицаЗначений Цикл

		Для Каждого Колонка Из МассивКолонок Цикл
			ТекстТаблицы = СтрШаблон("%1%2%3", ТекстТаблицы, СтрокаТаблицы[Колонка], ";");	
		КонецЦикла;

		ТекстТаблицы = СтрШаблон("%1%2", ТекстТаблицы, Символы.ПС);

	КонецЦикла;

	ЗаписьТекста = Новый ЗаписьТекста(ПолноеИмяФайла);
    ЗаписьТекста.Записать(ТекстТаблицы);
    ЗаписьТекста.Закрыть();

КонецПроцедуры

Кандидат на удаление вычисляется очень просто - берем максимальную дату из доступных нам колонок, считаем ее датой актуальности. Если дата актуальности старше 3 месяцев - это кандидат на удаление.

Да уж, есть базы, которыми не пользуются и они занимают порядочно места.

Текст скрипта опубликован на гитхабе - DB_Magic

Запускать его так: