Действия при переполнении базы данных — различия между версиями

Версия 11:51, 17 июля 2015

Наверх

Введение

В данной статье описываются действия при переполнении базы данных.

SQL SERVER 2005 Express имеет ограничение на размер базы данных 4 Гб, SQL SERVER 2008 Express - 8 Гб, SQL SERVER 2012 Express - 10 Гб. Превышение размера баз данных, как правило происходит из за роста следующих таблиц (обратите внимание на базы данных):

• Статистика соединений АТС [oktell].[dbo].[A_Stat_Connections_1x1]
• Статистика соединений Call-центра [oktell_cc_temp].[dbo].[A_Cube_CC_EffortConnections]

В случае переполнения базы данных у вас могут проявляться следующие симптомы: не отображается статистика АТС, система не запускается, зависание линий и др.

Вы можете использовать встроенные методы для очистки баз данных. Зайдите в Администрирование - Общие настройки - Управление базами данных - Произвести общую профилактику БД и Удалить данные и перестроить индексы. Обратите внимание на количество дней, которые необходимо оставить в вашей статистике.

ВНИМАНИЕ: Если сделать "Профилактика БД и удаление данных 2 дня", то база может "упасть" и восстанавливать ее можно только из бэкапа. Происходит это из за того, что запрос может выполняться долгое время и если его прервать, то восстановить БД уже будет сложно. Дело в том, что при выполнении данной процедуры создается некоторый кэш внутри бд. Если база переполнена — он не создастся.

Для использования инструкции вам потребуется установленная SQL Server Management Studio.

Создание бэкапа

Обязательно сделайте бэкапы баз данных. Зайдите в Администрирование - Общие настройки - Управление базами данных - Произвести резервное копирование БД.

Анализ размера таблиц БД

Определение таблиц, которые занимают больше всего места в вашей базе данных. Воспользуйтесь хранимой процедурой A_DB_HelpTables. Выполните в SSMS следующий запрос:

exec A_DB_HelpTables

Удаление данных

Используя инструкцию Delete

Для очищения таблиц воспользуйтесь следующим запросом:

DELETE FROM [oktell].[dbo].[A_Stat_Connections_1x1]
WHERE 
TimeStart< DATEADD ( dd, 7 , (SELECT MIN(timestart) FROM a_stat_connections_1x1 ) ) 
AND 
TimeStart< DATEADD ( DD, -30, GETDATE())

DBCC SHRINKDATABASE  (oktell,  TRUNCATEONLY)

Описание запроса: Запрос удаляет записи из таблицы [oktell].[dbo].[A_Stat_Connections_1x1] по неделям, начиная с самой последней (первое условие в запросе). При этом запрос, в любом случае, оставит записи за последний месяц (второе условие). После выполнения запроса производится очистка таблицы от временных записей (логи, транзакции) командой SHRINK.

• Чтобы не потерять данные в результате запроса, например за последние три месяца, измените во втором условии значение -30 на -90.

• Если база данных переполнена настолько, что не дает выполнить данный запрос, попробуйте удалять записи не по неделям (по 7 дней), а по 3 дня. Для этого измените значение в первом условии 7 на 3. Этим же способом, можно увеличить количество удаляемых записей с недели на месяц, изменив значение на 30.

Нажмите F5 для выполнения запроса. В случае удачного выполнения из таблицы удалятся записи за неделю. Нажмите F5 еще несколько раз, пока не увидите в сообщениях, что обработано 0 строк.

Для проверки правильности выполнения предлагается использоваться следующий запрос:

select timestart from A_Stat_Connections_1x1

На рисунке показан пример выполнения данного запроса. Запрос выполнялся 06.12.13 и как видно в результате выполнения очистки таблицы, остались все записи не позднее, чем 30 дней от текущего дня.

Используя инструкцию Truncate table

TRUNCATE TABLE (Transact-SQL) удаляет все строки в таблице, не записывая в журнал удаление отдельных строк. Инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, однако TRUNCATE TABLE выполняется быстрее и требует меньших ресурсов системы и журналов транзакций.

Инструкция DELETE производит удаление по одной строке и заносит в журнал транзакций запись для каждой удаляемой строки. Инструкция TRUNCATE TABLE удаляет данные, освобождая страницы данных, используемые для хранения данных таблиц, и в журнал транзакций записывает только данные об освобождении страниц.

ВАЖНО! Если таблица содержит столбец идентификаторов, счетчик этого столбца сбрасывается до начального значения, определенного для этого столбца. Если начальное значение не задано, используется значение по умолчанию, равное 1. Чтобы сохранить столбец идентификаторов, используйте инструкцию DELETE.

Более подробную информацию можно найти по ссылке TRUNCATE TABLE (Transact-SQL)

Если выполняются следующие условия:

• Существует не переполненная База Данных (для MS SQL Server 2008 R2 Express максимальный размер реляционной базы данных 10 Гб, более подробно по ссылке Features Supported by the Editions of SQL Server 2008 R2)

Для проверки выполним код

use oktell
exec A_DB_HelpTables

В результате выполнения видно, что размер БД Oktell составляет 119.50 MB (столбец db_size), БД не переполнена.

• Очищен журнал транзакций (очищение журнала транзакции происходит с помощью инструкции DBCC SHRINKDATABASE (ИМЯ_БД, TRUNCATEONLY)).

, то можно выполнить следующие инструкции:

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

SELECT *
into [oktell].[dbo].[A_Stat_Connections_1x1_for_copy]
FROM [oktell].[dbo].[A_Stat_Connections_1x1]
where TimeStart >='2015-01-01 00:00:00'

, где

• [oktell].[dbo].[A_Stat_Connections_1x1] - исходная таблица, которую необходимо очистить
• [oktell].[dbo].[A_Stat_Connections_1x1_for_copy] - таблица копия, созданная в не переполненной БД Oktell

2)Очистим исходную таблицу от данных используя инструкцию TRUNCATE TABLE

TRUNCATE TABLE [oktell].[dbo].[A_Stat_Connections_1x1]

3)Копируем данные из вспомогательной таблицы обратно в исходную таблицу

insert into [oktell].[dbo].[A_Stat_Connections_1x1]
select * from [oktell].[dbo].[A_Stat_Connections_1x1_for_copy]

4)Очищение таблицы завершено, после этого можно удалить вспомогательную таблицу

DROP TABLE [oktell].[dbo].[A_Stat_Connections_1x1_for_copy]