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

Материал из Oktell
Перейти к: навигация, поиск
(Используя инструкцию Truncate table)
 
(не показаны 24 промежуточные версии этого же участника)
Строка 1: Строка 1:
 +
[[Практики|Наверх]]
 +
 +
__TOC__
 +
 +
==Введение==
 +
 
В данной статье описываются действия при переполнении базы данных.
 
В данной статье описываются действия при переполнении базы данных.
  
Строка 14: Строка 20:
 
|}
 
|}
 
</center>
 
</center>
 +
 +
[[Файл:ПереполнениеБД-005.png | center | 700px]]
 +
  
 
Вы можете использовать встроенные методы для очистки баз данных. Зайдите в '''Администрирование''' - '''Общие настройки''' - '''Управление базами данных''' - '''Произвести общую профилактику БД''' и '''Удалить данные и перестроить индексы'''. Обратите внимание на количество дней, которые необходимо оставить в вашей статистике.  
 
Вы можете использовать встроенные методы для очистки баз данных. Зайдите в '''Администрирование''' - '''Общие настройки''' - '''Управление базами данных''' - '''Произвести общую профилактику БД''' и '''Удалить данные и перестроить индексы'''. Обратите внимание на количество дней, которые необходимо оставить в вашей статистике.  
Строка 19: Строка 28:
 
<span style="color:red;"> ВНИМАНИЕ: Если сделать "'''Профилактика БД и удаление данных 2 дня'''", то база может "упасть" и восстанавливать ее можно только из бэкапа. Происходит это из за того, что запрос может выполняться долгое время и если его прервать, то восстановить БД уже будет сложно. Дело в том, что при выполнении данной процедуры создается некоторый кэш внутри бд. Если база переполнена — он не создастся.  
 
<span style="color:red;"> ВНИМАНИЕ: Если сделать "'''Профилактика БД и удаление данных 2 дня'''", то база может "упасть" и восстанавливать ее можно только из бэкапа. Происходит это из за того, что запрос может выполняться долгое время и если его прервать, то восстановить БД уже будет сложно. Дело в том, что при выполнении данной процедуры создается некоторый кэш внутри бд. Если база переполнена — он не создастся.  
  
'''Шаг 1.''' Обязательно сделайте бэкапы баз данных. Зайдите в '''Администрирование''' - '''Общие настройки''' - '''Управление базами данных''' - '''Произвести резервное копирование БД.'''  
+
Для использования инструкции вам потребуется установленная '''SQL Server Management Studio.'''
  
'''Шаг 2.''' Определение таблиц, которые занимают больше всего места в вашей базе данных. Воспользуйтесь хранимой процедурой '''A_DB_HelpTables.''' Выполните в SSMS следующий запрос:
+
==Создание бэкапа==
 +
 
 +
Обязательно сделайте бэкапы баз данных. Зайдите в '''Администрирование''' - '''Общие настройки''' - '''Управление базами данных''' - '''Произвести резервное копирование БД.'''  
 +
 
 +
==Анализ размера таблиц БД==
 +
Определение таблиц, которые занимают больше всего места в вашей базе данных. Воспользуйтесь хранимой процедурой '''A_DB_HelpTables.''' Выполните в SSMS следующий запрос:
  
 
  exec A_DB_HelpTables
 
  exec A_DB_HelpTables
  
[[Файл:ПереполнениеБД-001.png | center| 600px]]
 
  
'''Шаг 3.''' Очищение таблиц. Воспользуйтесь следующим запросом:
+
[[Файл:ПереполнениеБД-001.png | center| 700px]]
 +
 
 +
 
 +
==Удаление данных==
 +
 
 +
===Используя инструкцию Delete===
 +
 
 +
Для очищения таблиц воспользуйтесь следующим запросом:
  
 
  DELETE FROM [oktell].[dbo].[A_Stat_Connections_1x1]
 
  DELETE FROM [oktell].[dbo].[A_Stat_Connections_1x1]
Строка 44: Строка 64:
  
 
Нажмите '''F5''' для выполнения запроса. В случае удачного выполнения из таблицы удалятся записи за неделю. Нажмите '''F5''' еще несколько раз, пока не увидите в сообщениях, что '''обработано 0 строк.'''  
 
Нажмите '''F5''' для выполнения запроса. В случае удачного выполнения из таблицы удалятся записи за неделю. Нажмите '''F5''' еще несколько раз, пока не увидите в сообщениях, что '''обработано 0 строк.'''  
 +
  
 
<center>[[Файл:ПереполнениеБД-002.png | 500px]][[Файл:ПереполнениеБД-003.png| 500px]]</center>
 
<center>[[Файл:ПереполнениеБД-002.png | 500px]][[Файл:ПереполнениеБД-003.png| 500px]]</center>
 +
  
 
Для проверки правильности выполнения предлагается использоваться следующий запрос:
 
Для проверки правильности выполнения предлагается использоваться следующий запрос:
Строка 51: Строка 73:
 
  select timestart from A_Stat_Connections_1x1
 
  select timestart from A_Stat_Connections_1x1
  
На рисунке показан пример выполнения данного запроса. Запрос выполнялся 06.12.13 и как видно в результате выполнения очистки таблицы, остались все записи не позднее, чем 30 дней от текущего дня.
+
На рисунке показан пример выполнения данного запроса. Запрос выполнялся '''06.12.13''' и как видно в результате выполнения очистки таблицы, остались все записи не позднее, чем 30 дней от текущего дня.
 +
 
 +
 
 +
[[Файл:ПереполнениеБД-004.png | center| 700px]]
 +
 
 +
 
 +
 
 +
===Используя инструкцию Truncate table===
 +
 
 +
TRUNCATE TABLE (Transact-SQL) удаляет все строки в таблице, не записывая в журнал удаление отдельных строк. Инструкция TRUNCATE TABLE похожа на инструкцию DELETE без предложения WHERE, однако TRUNCATE TABLE выполняется быстрее и требует меньших ресурсов системы и журналов транзакций.
 +
 
 +
Инструкция DELETE производит удаление по одной строке и заносит в журнал транзакций запись для каждой удаляемой строки. Инструкция TRUNCATE TABLE удаляет данные, освобождая страницы данных, используемые для хранения данных таблиц, и в журнал транзакций записывает только данные об освобождении страниц.
 +
 
 +
 
 +
<span style="color:red">ВАЖНО! Если таблица содержит столбец идентификаторов, счетчик этого столбца сбрасывается до начального значения, определенного для этого столбца. Если начальное значение не задано, используется значение по умолчанию, равное 1. Чтобы сохранить столбец идентификаторов, используйте инструкцию DELETE.</span>
 +
 
 +
 
 +
Более подробную информацию можно найти по ссылке [https://msdn.microsoft.com/ru-ru/library/ms177570(v=sql.105).aspx TRUNCATE TABLE (Transact-SQL)]
 +
 
 +
Если выполняются следующие условия:
 +
*Существует не переполненная База Данных (для MS SQL Server 2008 R2 Express максимальный размер реляционной базы данных 10 Гб, более подробно по ссылке [https://msdn.microsoft.com/ru-ru/library/cc645993(v=sql.105).aspx Features Supported by the Editions of SQL Server 2008 R2])
 +
 
 +
Для проверки выполним код
 +
 
 +
<pre>
 +
exec sp_helpdb
 +
</pre>
 +
 
 +
[[Файл:db_trun_del_2.jpg|center|800px]]
 +
 
 +
В результате выполнения видно, что размер БД Oktell составляет 119.50 MB (столбец db_size), БД не переполнена.
 +
 
 +
*Очищен журнал транзакций (очищение журнала транзакции происходит с помощью инструкции DBCC SHRINKDATABASE  (ИМЯ_БД,  TRUNCATEONLY)).
 +
, то можно выполнить следующие инструкции:
 +
 
 +
 
 +
1)Создадим копию очищаемой таблицы в не переполненной базе данных, при этом записывая в нее данные которые нам требуется сохранить. Для этого выполним следующий код:
 +
 
 +
[[Файл:db_trun_del_1.jpg|center|800px]]
 +
 
 +
<pre>
 +
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'
 +
</pre>
 +
 
 +
, где
 +
*[oktell].[dbo].[A_Stat_Connections_1x1] - исходная таблица, которую необходимо очистить
 +
*[oktell].[dbo].[A_Stat_Connections_1x1_for_copy] - таблица копия, созданная в не переполненной БД Oktell
 +
 
 +
 
 +
2)Очистим исходную таблицу от данных используя инструкцию TRUNCATE TABLE
 +
 
 +
<pre>
 +
TRUNCATE TABLE [oktell].[dbo].[A_Stat_Connections_1x1]
 +
</pre>
 +
 
 +
3)Копируем данные из вспомогательной таблицы обратно в исходную таблицу
 +
 
 +
<pre>
 +
insert into [oktell].[dbo].[A_Stat_Connections_1x1]
 +
select * from [oktell].[dbo].[A_Stat_Connections_1x1_for_copy]
 +
</pre>
 +
 
 +
4)Очищение таблицы завершено, после этого можно удалить вспомогательную таблицу
  
[[Файл:ПереполнениеБД-004.png | center| 600px]]
+
<pre>
 +
DROP TABLE [oktell].[dbo].[A_Stat_Connections_1x1_for_copy]
 +
</pre>

Текущая версия на 11:58, 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]

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

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


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

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

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

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

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

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

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

exec A_DB_HelpTables


ПереполнениеБД-001.png


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

Используя инструкцию 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 строк.


ПереполнениеБД-002.pngПереполнениеБД-003.png


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

select timestart from A_Stat_Connections_1x1

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


ПереполнениеБД-004.png


Используя инструкцию 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)

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

exec sp_helpdb
Db trun del 2.jpg

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

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

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


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

Db trun del 1.jpg
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]