Еще немного полезных SQL-скриптов (Скрипты для SQL-Server

Представляю вашему вниманию заключительный вебинар из цикла вебинаров по скриптам для MS SQL-сервера.

Видео в формате вебинара:

Ну и, непосредственно, сами разобранные в вебинаре скрипты под спойлерами ниже. Все переменные собраны в соответствующем блоке в начале скриптов. Для отправки электронной почты используется предварительно настроенный профиль электронной почты компоненты DataBase Mail. О настройке компоненты можно прочитать, например, здесь. Все скрипты много раз опробованы в бою и протестированы на версиях MS SQL 2008, 2012, 2024.

Скрипт, очищающий таблицы указанной базы данных:

---------------------------------------------
-- НАСТРАИВАЕМЫЕ ПАРАМЕТРЫ
-- Текущая база данных
USE WorkBase
-- Имена таблиц для очистки (необходимо добавить имена таблиц в таблицу @Table)
DECLARE @Table table(TablName varchar(100));
INSERT INTO @Table VALUES ('_Table1')
INSERT INTO @Table VALUES ('_Table2')
-- Имя почтового профиля, для отправки электонной почты
DECLARE @profilename as nvarchar(100) = 'ОсновнойПрофиль'
-- Получатели сообщений электронной почты, разделенные знаком ";"
DECLARE @recipients as nvarchar(500) = 'admin@mydomen.com'

-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ
DECLARE @SQLString NVARCHAR(4000)
DECLARE @TableName varchar(100)
DECLARE @subject as NVARCHAR(1000) = '' -- тема сообщения
DECLARE @finalmassage as NVARCHAR(4000) = '' -- текст сообщения

-------------------------------------------
-- ТЕЛО СКРИПТА

-- Цикл по всем указанным таблицам
DECLARE TableCursor CURSOR FOR
(
SELECT * FROM @Table
)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

-- Удаляем таблицу, если такая есть
SET @SQLString = 'IF NOT OBJECT_ID(N''[' + @TableName + ']'',''U'') IS NULL TRUNCATE TABLE [dbo].[' + @TableName + '];'

PRINT @SQLString
BEGIN TRY
EXEC sp_executesql @SQLString
SET @finalmassage = @finalmassage + 'Успешная очистка таблицы ' + @TableName + ' в базе данных ' + DB_NAME() + CHAR(13) + CHAR(13)
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @subject = 'БЫЛИ ОШИБКИ при очистке таблиц в базе данных ' + DB_NAME()
SET @finalmassage = @finalmassage + 'ОШИБКА очистки таблицы ' + @TableName + ' в базе данных ' + DB_NAME() + CHAR(13) + CHAR(13)
+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
+ 'Текст T-SQL: ' + CHAR(13) + @SQLString + CHAR(13) + CHAR(13)
END CATCH;

-- Следующий элемент цикла
FETCH NEXT FROM TableCursor INTO @TableName

END
CLOSE TableCursor;
DEALLOCATE TableCursor;

-- Формируем сообщение об успешном или не успешном выполнении операций
IF @subject = ''
BEGIN
-- Успешное выполнение всех операций
SET @subject = 'Успешная очистка таблиц в базе данных ' + DB_NAME()
END

-- Если задан профиль электронной почты, отправим сообщение
IF @profilename <> ''
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients = @recipients,
@body = @finalmassage,
@subject = @subject;

-- Выводим сообщение о результате
SELECT
@subject as subject,
@finalmassage as finalmassage

GO

Скрипт, очищающий таблицы нескольких баз данных (по определенному условию):

---------------------------------------------
-- НАСТРАИВАЕМЫЕ ПАРАМЕТРЫ
-- Условие для выборки, '%' - все базы данных
DECLARE @namelike varchar(100) = 'WorkBase%'
-- Имена таблиц для очистки (необходимо добавить имена таблиц в таблицу @Table)
DECLARE @Table table(TablName varchar(100));
INSERT INTO @Table VALUES ('_Table1')
INSERT INTO @Table VALUES ('_Table2')
-- Имя почтового профиля, для отправки электонной почты
DECLARE @profilename as nvarchar(100) = 'ОсновнойПрофиль'
-- Получатели сообщений электронной почты, разделенные знаком ";"
DECLARE @recipients as nvarchar(500) = 'admin@mydomen.com'

-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ
DECLARE @SQLString NVARCHAR(4000)
DECLARE @DBName varchar(100)
DECLARE @TableName varchar(100)
DECLARE @subject as NVARCHAR(1000) = '' -- тема сообщения
DECLARE @finalmassage as NVARCHAR(4000) = '' -- текст сообщения

-------------------------------------------
-- ТЕЛО СКРИПТА
USE master

-- Цикл по всем базам данных
DECLARE DBcursor CURSOR FOR
(
SELECT d.name as DatabaseName
FROM sys.databases d
WHERE
d.name <> 'tempdb'
AND d.name <> 'master'
AND d.name <> 'model'
AND d.name <> 'msdb'
AND d.state_desc = 'ONLINE' -- база должна быть в сети
AND d.name like @namelike -- база должна содержать указанное слово
)

-- Цикл по всем указанным таблицам
DECLARE TableCursor CURSOR FOR
(
SELECT * FROM @Table
)

-- Цикл по всем базам, попавшим в выборку
OPEN DBcursor
FETCH NEXT FROM DBcursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT N'----------------------------------------------------------'
PRINT N'USE [' + @DBName + N']'

-- Цикл по всем указанным таблицам
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN

-- Удаляем таблицу, если такая есть
SET @SQLString = 'USE [' + @DBName + '] IF NOT OBJECT_ID(N''[' + @TableName + ']'',''U'') IS NULL TRUNCATE TABLE [dbo].[' + @TableName + '];'

-- Выполняем инструкцию
PRINT @SQLString
BEGIN TRY
EXEC sp_executesql @SQLString
SET @finalmassage = @finalmassage + 'Успешная очистка таблицы ' + @TableName + ' в базе данных ' + @DBName + CHAR(13) + CHAR(13)
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @subject = 'БЫЛИ ОШИБКИ при очистке таблиц '
SET @finalmassage = @finalmassage + 'ОШИБКА очистки таблицы ' + @TableName + ' в базе данных ' + @DBName + CHAR(13) + CHAR(13)
+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
+ 'Текст T-SQL: ' + CHAR(13) + @SQLString + CHAR(13) + CHAR(13)
END CATCH;

-- Следующий элемент цикла по таблицам
FETCH NEXT FROM TableCursor INTO @TableName

END
CLOSE TableCursor;

-- Следующий элемент цикла по базам данных
FETCH NEXT FROM DBcursor INTO @DBName

END
DEALLOCATE TableCursor;
CLOSE DBcursor;
DEALLOCATE DBcursor;

-- Формируем сообщение об успешном или не успешном выполнении операций
IF @subject = ''
BEGIN
-- Успешное выполнение всех операций
SET @subject = 'Успешная очистка таблиц '
END

-- Если задан профиль электронной почты, отправим сообщение
IF @profilename <> ''
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients = @recipients,
@body = @finalmassage,
@subject = @subject;

-- Выводим сообщение о результате
SELECT
@subject as subject,
@finalmassage as finalmassage

GO

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

-------------------------------------------
-- НАСТРАИВАЕМЫЕ ПАРАМЕТРЫ
-- Имя базы данных
DECLARE @DBName varchar(100) = 'TestBase'
-- Имя почтового профиля, для отправки электонной почты
DECLARE @profilename as nvarchar(100) = 'ОсновнойПрофиль'
-- Получатели сообщений электронной почты, разделенные знаком ";"
DECLARE @recipients as nvarchar(500) = 'admin@mydomen.com'

-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ
DECLARE @SQLString NVARCHAR(4000)
DECLARE @subject as NVARCHAR(1000) = ''
DECLARE @finalmassage as NVARCHAR(4000) = ''

-------------------------------------------
-- ТЕЛО СКРИПТА

-- Переводим базы в простую модель восстановления
SET @SQLString = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY SIMPLE WITH NO_WAIT;'
PRINT @SQLString
BEGIN TRY
EXEC sp_executesql @SQLString
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @subject = 'ОШИБКА перевода базы данных ' + @DBName + ' в простую модель восстановоления '
SET @finalmassage = 'ОШИБКА перевода базы данных ' + @DBName + ' в простую модель восстановоления ' + CHAR(13) + CHAR(13)
+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
+ 'Текст T-SQL:' + CHAR(13) + @SQLString
END CATCH;

-- Запускаем сжатие базы данных
IF @subject = ''
BEGIN
SET @SQLString = 'DBCC SHRINKDATABASE(N''' + @DBName + ''');'
PRINT @SQLString
BEGIN TRY
EXEC sp_executesql @SQLString
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @subject = 'ОШИБКА сжатия базы данных ' + @DBName
SET @finalmassage = 'ОШИБКА сжатия базы данных ' + @DBName + CHAR(13) + CHAR(13)
+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
+ 'Текст T-SQL:' + CHAR(13) + @SQLString
END CATCH;
END

-- Формируем сообщение об успешном выполнении операций
IF @subject = ''
BEGIN
-- Успешное выполнение всех операций
SET @subject = 'Успешный перевод в простую модель восстановления и сжатие базы данных ' + @DBName
SET @finalmassage = 'Успешный перевод в простую модель восстановления и сжатие базы данных ' + @DBName
END

-- Если задан профиль электронной почты, отправим сообщение
IF @profilename <> ''
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients = @recipients,
@body = @finalmassage,
@subject = @subject;

-- Выводим сообщение о результате
SELECT
@subject as subject,
@finalmassage as finalmassage

GO

Перевод в простую модель восстановления и сжатие нескольких баз данных (по определенному условию):

---------------------------------------------
-- НАСТРАИВАЕМЫЕ ПАРАМЕТРЫ
-- Условие для выборки, '%' - все базы данных
DECLARE @namelike varchar(100) = '%fto%'
-- Имя почтового профиля, для отправки электонной почты
DECLARE @profilename as nvarchar(100) = 'ОсновнойПрофиль'
-- Получатели сообщений электронной почты, разделенные знаком ";"
DECLARE @recipients as nvarchar(500) = 'admin@mydomen.com'

-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ
DECLARE @SQLString NVARCHAR(4000)
DECLARE @DBName varchar(100)
DECLARE @subject as NVARCHAR(1000) = ''
DECLARE @finalmassage as NVARCHAR(4000) = ''

-------------------------------------------
-- ТЕЛО СКРИПТА

-- Отбоерем базы для выполнения операций
DECLARE DBcursor CURSOR FOR
(
SELECT d.name as DatabaseName
FROM sys.databases d
WHERE d.name <> 'tempdb'
AND d.name <> 'master'
AND d.name <> 'model'
AND d.name <> 'msdb'
AND d.state_desc = 'ONLINE' -- база должна быть в сети
AND d.name like @namelike -- база должна содержать указанное слово
)

-- Цикл по всем базам, попавшим в выборку
OPEN DBcursor
FETCH NEXT FROM DBcursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN

-- Переводим базы в простую модель восстановления
SET @SQLString = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY SIMPLE WITH NO_WAIT;'
PRINT @SQLString
BEGIN TRY
EXEC sp_executesql @SQLString
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @finalmassage = @finalmassage + 'ОШИБКА перевода базы данных ' + @DBName + ' в простую модель восстановоления ' + CHAR(13) + CHAR(13)
+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
+ 'Текст T-SQL:' + CHAR(13) + @SQLString + CHAR(13) + CHAR(13)
END CATCH;

-- Запускаем сжатие базы данных
SET @SQLString = 'DBCC SHRINKDATABASE(N''' + @DBName + ''');'
PRINT @SQLString
BEGIN TRY
EXEC sp_executesql @SQLString
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @finalmassage = @finalmassage + 'ОШИБКА сжатия базы данных ' + @DBName + ' в простую модель восстановоления ' + CHAR(13) + CHAR(13)
+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
+ 'Текст T-SQL:' + CHAR(13) + @SQLString + CHAR(13) + CHAR(13)
END CATCH;

-- Следующий элемент цикла
FETCH NEXT FROM DBcursor
INTO @DBName

END
CLOSE DBcursor;
DEALLOCATE DBcursor;

-- Формируем сообщение об успешном или не успешном выполнении операций
IF @finalmassage = ''
BEGIN
-- Успешное выполнение всех операций
SET @subject = 'Успешное выполнение операций с базами данных '
SET @finalmassage = 'Успешный перевод в простую модель восстановления и сжатие баз данных '
END
ELSE
-- Были ошибки
SET @subject = 'БЫЛИ ОШИБКИ при выполненит операций с базами данных '

-- Если задан профиль электронной почты, отправим сообщение
IF @profilename <> ''
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients = @recipients,
@body = @finalmassage,
@subject = @subject;

-- Выводим сообщение о результате
SELECT
@subject as subject,
@finalmassage as finalmassage

GO

Перевод в простую модель восстановления и сжатие только файлов журнала транзакций нескольких баз данных (по определенному условию):

---------------------------------------------
-- НАСТРАИВАЕМЫЕ ПАРАМЕТРЫ
-- Условие для выборки, '%' - все базы данных
DECLARE @namelike varchar(100) = '%fto%'
-- Имя почтового профиля, для отправки электонной почты
DECLARE @profilename as nvarchar(100) = 'ОсновнойПрофиль'
-- Получатели сообщений электронной почты, разделенные знаком ";"
DECLARE @recipients as nvarchar(500) = 'admin@mydomen.com'

-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ
DECLARE @SQLString NVARCHAR(4000)
DECLARE @DBName varchar(100)
DECLARE @DBLogName varchar(100)
DECLARE @subject as NVARCHAR(1000) = ''
DECLARE @finalmassage as NVARCHAR(4000) = ''

-------------------------------------------
-- ТЕЛО СКРИПТА

-- Отберем базы для выполнения операций
DECLARE DBcursor CURSOR FOR
(
SELECT
t_Name.name as DatabaseName,
t_LogName.name as DatabaseLogName
FROM sys.databases as t_Name
Inner join sys.master_files as t_LogName
on t_Name.database_id = t_LogName.database_id
WHERE t_Name.database_id > 4
AND t_Name.state_desc = 'ONLINE' -- база должна быть в сети
AND t_Name.name like @namelike -- база должна содержать указанное слово
AND t_LogName.type = 1
)

-- Цикл по всем базам, попавшим в выборку
OPEN DBcursor
FETCH NEXT FROM DBcursor INTO @DBName, @DBLogName
WHILE @@FETCH_STATUS = 0
BEGIN

-- Переводим базы в простую модель восстановления
SET @SQLString = 'ALTER DATABASE ' + @DBName + ' SET RECOVERY SIMPLE WITH NO_WAIT;'
PRINT @SQLString
BEGIN TRY
EXEC sp_executesql @SQLString
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @finalmassage = @finalmassage + 'ОШИБКА перевода базы данных ' + @DBName + ' в простую модель восстановоления ' + CHAR(13) + CHAR(13)
+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
+ 'Текст T-SQL:' + CHAR(13) + @SQLString + CHAR(13) + CHAR(13)
END CATCH;

-- Запускаем сжатие лога базы данных
SET @SQLString = 'USE [' + @DBName + '];  DBCC SHRINKFILE(' + @DBLogName + ', 0, truncateonly);'
PRINT @SQLString
BEGIN TRY
EXEC sp_executesql @SQLString
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @finalmassage = @finalmassage + 'ОШИБКА сжатия базы данных ' + @DBName + ' в простую модель восстановоления ' + CHAR(13) + CHAR(13)
+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
+ 'Текст T-SQL:' + CHAR(13) + @SQLString + CHAR(13) + CHAR(13)
END CATCH;

-- Следующий элемент цикла
FETCH NEXT FROM DBcursor
INTO @DBName, @DBLogName

END
CLOSE DBcursor;
DEALLOCATE DBcursor;

-- Формируем сообщение об успешном или не успешном выполнении операций
IF @finalmassage = ''
BEGIN
-- Успешное выполнение всех операций
SET @subject = 'Успешное выполнение операций с базами данных '
SET @finalmassage = 'Успешный перевод в простую модель восстановления и сжатие файла журнала транзакций'
END
ELSE
-- Были ошибки
SET @subject = 'БЫЛИ ОШИБКИ при выполненит операций с базами данных '

-- Если задан профиль электронной почты, отправим сообщение
IF @profilename <> ''
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients = @recipients,
@body = @finalmassage,
@subject = @subject;

-- Выводим сообщение о результате
SELECT
@subject as subject,
@finalmassage as finalmassage

GO

Скрипт, показывающий все соединения с сервером SQL:

-------------------------------------------
-- ТЕЛО СКРИПТА
SELECT
program_name,
net_transport
FROM sys.dm_exec_sessions AS sessions
left join sys.dm_exec_connections AS connections
ON sessions.session_id = connections.session_id
WHERE
not sessions.program_name is null
ORDER BY
program_name

GO

Скрипт, показывающий версию сервера SQL:

-------------------------------------------
-- ТЕЛО СКРИПТА
SELECT
SERVERPROPERTY('MachineName') AS ComputerName,
SERVERPROPERTY('ServerName') AS InstanceName,
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel;

GO

Эти и другие скрипты доступны также в репозитории: https://github.com/Tavalik/SQL_TScripts

Все вебинары по скриптам для SQL:

Автоматизируем перезаливку баз (Часть 1): //infostart.ru/public/799857/
Регламентные операции с индексами в MS SQL Server (Часть 2): //infostart.ru/public/803209/
Еще немного полезных SQL-скриптов (Часть 3): //infostart.ru/public/807843/

5 Comments

Akbis 02.04.2018 at 21:31

Может вместо delete стоит делать truncate?

А так спасибо за скрипты.

Reply ↓
Tavalik 03.04.2018 at 06:28

(2)

В вебинаре delete, но в самих скриптах я уже давно на truncate поменял.

Спасибо за комментарий.

Reply ↓
serferian 04.04.2018 at 08:25

Пользуйтесь, не надо придумывать велосипед

1. поддержка баз (переиндексирование, перестройка, бакап рестор и прочее) https://ola.hallengren.com/

все с примерами и легко вешается в задачи sql.

2. монитор активности: whoisactive

Reply ↓
Milanick 04.04.2018 at 09:14

Коллеги, можете подскажите советом , куда капнуть?

Задача «Проверка целостности базы данных» (W2008R2) Проверить целостность базы данных Соединение с локальным сервером Базы данных TEMP_FG Включить индексы Начало задачи: 2018-03-25T19:00:01.

Конец задачи: 2018-03-25T19:07:52.

Ошибка:(-1073548784) Сбой выполнения запроса «DBCC CHECKDB(N’TEMP_FG’) WITH NO_INFOMSGS » со следующей ошибкой: «Экстент (1:8488) в базе данных с идентификатором 7 размещен несколькими объектами размещения.

Экстент (1:8528) в базе данных с идентификатором 7 размещен несколькими объектами размещения.

Экстент (1:8488) размещен в «Unknown» и по крайней мере еще одном объекте.

Экстент (1:8528) размещен в «Unknown» и по крайней мере еще одном объекте.

На страницу карты распределения индекса (IAM) (1:64493) указывает предыдущий указатель IAM-страницы (1:6378) в объекте с идентификатором 0, идентификатор индекса -1, идентификатор секции 0, идентификатор единицы размещения 72060067471556608 (тип Unknown), но это не было обнаружено в ходе просмотра.

Экстент (1:8488) размещен в «dbo.Config, PK__ConfigNG__589E6EED4499BB57» и по крайней мере еще одном объекте.

Экстент (1:8528) размещен в «dbo.Config, PK__ConfigNG__589E6EED4499BB57» и по крайней мере еще одном объекте.

CHECKDB обнаружил 5 ошибок размещения и 0 ошибок согласованности, не связанных ни с одним объектом.

CHECKDB обнаружил 2 ошибок размещения и 0 ошибок согласованности в таблице «Config» (идентификатор объекта 1118925541).

CHECKDB обнаружил 7 ошибок размещения и 0 ошибок согласованности в базе данных

repair_allow_data_loss — это минимальный уровень исправления для ошибок, найденных DBCC CHECKDB (TEMP_FG).». Возможные причины сбоя: проблемы с этим запросом, свойство «ResultSet» установлено неправильно, параметры установлены неправильно или соединение было установлено неправильно.

Reply ↓
ecstazi 01.03.2019 at 13:25

Всё отлично,можно ли добавить в скрипт в конец перевод в полную модель восстановления

Reply ↓

Еще немного полезных SQL-скриптов (Скрипты для SQL-Server — Часть 3)

5 Comments

Leave a Comment Отменить ответ

Еще немного полезных SQL-скриптов (Скрипты для SQL-Server — Часть 3)

Related Posts

5 Comments

Leave a Comment Отменить ответ