gifts2017

Скрипт MS SQL для вывода информации о дате последнего изменения БД и размере БД. Для всех БД на сервере.

Опубликовал Валерий Волошин (VVi3ard) в раздел Администрирование - Сервисные утилиты

Что делает:
Скрипт выводит в виде таблицы информацию о дате последнего изменения базы ( берется из _UsersWorkHistory) и её размер.
Кому нужно:
Может помочь администраторам у которых на сервере множество различных БД 1С.
Одним нажатием кнопки можно понять какие БД стоит рассматривать как "неиспользуемые".

Работает только с базами 8.2 управляемое приложение т.к. для определения даты последнего изменения используется системная таблица:  _UsersWorkHistory

Что делает:
Скрипт выводит в виде таблицы информацию о дате последнего изменения базы ( берется из _UsersWorkHistory) и её размер.
Кому нужно:
Может помочь администраторам у которых на сервере множество различных БД 1С.
Одним нажатием кнопки можно понять какие БД стоит рассматривать как "неиспользуемые".

Так же может использоваться в качестве примера пакетной работы с таблицами, и базами. 

Как работает:

Работает только с базами 8.2 управляемое приложение т.к. для определения даты последнего изменения используется системная таблица: _UsersWorkHistory

При желании можно заменит запрос для определения даты последнего изменения: "Insert into #T1 SELECT '''+ @dbname + ''' as f1, Max(_Date) as f2 FROM ['+ @dbname+'].[dbo].[_UsersWorkHistory]" на ваш. 

Базы не 1С или 1С ниже 8.2 не обрабатываются. 

 

Скрипт: 


print 'Получение информации о размерерах БД'
if object_id('tempdb..#T2') is not null drop table #T2 --Проверим наличие временных таблиц и если существуют удалим.
--Создадим временную таблицу для хранения информации о размерах БД.
create table #T2 (name sysname, db_size nvarchar(13), owner sysname, dbid smallint, created nvarchar(11), status nvarchar(600), compatibility_level tinyint)

insert into #T2 EXEC sp_helpdb --Вызов системной функции и помещение результатов в временную таблицу T2

print 'Получение информации о использовании БД'
if object_id('tempdb..#T1') is not null drop table #T1 --Проверим наличие временных таблиц и если существуют удалим.

--Создадим временную таблицу T1 для хранения информации о последнем измении БД:
--Поля:
-- F1 - Имя базы
-- F2 - Дата последнего изменения.

CREATE TABLE #T1 (f1 sysname, f2 datetime);

-- Выбираем список не системных (database_id>4), активных(state=0) БД
-- Опции LOCAL STATIC READ_ONLY используются для ускорения работы(LOCAL,READ_ONLY) и для того что бы полученная таблица не изменялась в процессе работы(STATIC).
declare dbcursor CURSOR LOCAL STATIC READ_ONLY for SELECT name FROM sys.Databases where database_id>4 and state=0

declare @Count int --Счетчик баз по которым прошел курсор (Используется для отладки что бы видеть что курсор обошел все базы).

declare @dbname sysname --Содержит имя текущей базы данных, используется при работе с курсором.

declare @sqlQuery nvarchar(4000) --Используется для формирования текста запроса (хранит текст запроса).
declare @ParmDefinition nvarchar(500);
declare @CountRow int

OPEN dbcursor
FETCH NEXT FROM dbcursor into @dbname --Устанавливаем @dbname для первой строки набора.

set @count=0 --Устанавливаем начальное значение счетчика (исп. для отладки)

WHILE @@FETCH_STATUS = 0 --Начинаем цикл. @@FETCH_STATUS глобальная переменная для работы с текущим "CURSOR"

BEGIN
--Вывод отладочной информации в консоль.
set @count=@count+1
print @count
print @dbname

--Проверяем есть ли в текущей БД таблица: _UsersWorkHistory (в 8.2 в этой таблице хранится история работы пользователей) .
set @sqlQuery ='select @CountRowOUT = count(*) from ['+@dbname+'].information_schema.tables where TABLE_NAME=''_UsersWorkHistory'''
set @ParmDefinition = '@CountRowOUT int OUTPUT';
exec sp_executesql @sqlQuery, @ParmDefinition, @CountRowOUT=@CountRow OUTPUT

if @CountRow > 0 --Если результат запроса не пустой значит такая таблица есть.
Begin
set @sqlQuery ='Insert into #T1 SELECT '''+ @dbname + ''' as f1, Max(_Date) as f2 FROM ['+ @dbname+'].[dbo].[_UsersWorkHistory]'
exec sp_executesql @sqlQuery
END
ELSE print 'Необработано (8.1 или база шаблона): '+@dbname

FETCH NEXT FROM dbcursor into @dbname
END

CLOSE dbcursor
Deallocate dbcursor

SELECT #T1.*, #T2.*
FROM #T1 LEFT OUTER JOIN
#T2 ON #T1.F1 = #T2.name
order by f2,db_size

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Сергей Старых (tormozit) 10.04.13 16:37
Еще бы неплохо приделать это к списку баз 1С. Получить все базы из кластера 1С и для каждой вывести такую инфу. У Гилева в облачных сервисах есть похожая фича. Но без облаков она была бы полезнее.
2. Сергей Куликов (ksvd) 10.04.13 16:41
А почему в дате последнего изменения год такой получается?
4013-04-10 16:05:30.000
3. Валерий Волошин (VVi3ard) 10.04.13 16:47
(1) tormozit, это уже нужно обработку писать, вводить логин пароль администратора (я правильно понимаю что под "Список баз 1С" ты имеешь в виду список баз в консоли сервера?)
Я у Гилева не видел того что ты описал, может пропустил, или добавили позже... Надо будет снова подключится посмотреть.
В моем случае просто специфика была такая что баз могло не быть в этом списке.
4. Валерий Волошин (VVi3ard) 10.04.13 16:48
5. Сергей Куликов (ksvd) 10.04.13 16:54
Спасибо за разьяснения и за скрипт.
6. DERL (DERL) 11.04.13 11:14
2008 выдал такую вещь:

ЗАГОЛОВОК: Microsoft SQL Server Management Studio
------------------------------

Не удается выполнить сценарий.

------------------------------
ДОПОЛНИТЕЛЬНЫЕ СВЕДЕНИЯ:

Не удается запустить отладчик Transact-SQL. Отладчик Transact-SQL не поддерживает SQL Server 2005 и более ранние версии SQL Server. (SQLEditors)

------------------------------
КНОПКИ:

ОК
------------------------------
...Показать Скрыть
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа